I have a query that looks like this: SELECT id, GROUP_CONCAT(type SEPARATOR ',') AS type FROM rock_types WHERE type IN("DWS","Top rope") GROUP BY id this returns all the ids that have a type of DWS or top rope or both with the types concatenated. However, there are more types than just those 2. Is it possible to still select ...
How can you solve the Resource ID #8 -error message in the following code? The error apparently means that I have a bug in my SQL statement. However, I cannot see it. $result = pg_prepare($dbconn, "query1", "SELECT user_id FROM users WHERE email = $1;"); $result = pg_execute($dbconn, "query1", array("[email protected]")); // to r...
i have a view that is using linked server to retrieve data from a remote server in SQL Server. On each time viewing the view, the results returned are vary. For example, 1st time execution may return 100 rows of records but on 2nd time of execution, rows returned are 120 rows. Any ideas what is the cause? ...
Hi guys I am looking at doing some automated unit testing and I was wondering if you know of any way that one can "Parse"/"Compile" a stored proc(s) from code (i.e. from within .net). The case I am trying to catch is if someone makes a change to a table (i.e. removes a column) and that breaks a stored proc somewhere that they don't kn...
Below are two schema which I believe achieve the same result, i.e. m:n relationship with the option for a default/preferred value. Is there any reason to use one over the other? Schema #1 CREATE TABLE people ( id serial, primary key (id) ); CREATE TABLE names ( id serial, first_name text not null, last_name text not null, ...
Possible Duplicates: Why would a sql query have where 1 = 1 Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? I've seen that a lot in different query examples and it goes to probably all SQL engines. If there is a query that has no conditions defined people (and specially ORM frameworks) often add always-true ...
Hi All, I have the following search sql query: ALTER PROCEDURE [EmployeeSearch] @PayrollNumber varchar(50) = null, @isInternal bit = null, @Firstname varchar(50) = null, @Surname varchar(50) = null AS SET NOCOUNT ON; Select EmployeeSearchView.* From EmployeeSearchView Where EmployeeSearchView.isInternal = @isInternal AND (Employ...
Hi All, I'm trying to understand a historical stored procedure I need to fix. I found this DRVTBL key word, and I couldn’t find out what it means??? (This is a sql2000 database) SELECT ... FROM ( ...) ) DRVTBL ...
I have quite a few stored procedures following the pattern of selecting the row for which a date column is the latest up to a certain date, inclusive. I see two forms in use: select top 1 x, y, z from sometable where a=b and date <= @date order by date desc or select x, y, z from sometable where a=b and date=(select max(date) from so...
I've written a query in MS Access. This is a simplified version: SELECT IIf([category] LIKE "*abc*","DEF",category) AS category , Month , Sum(qty) AS [qty] FROM [tableX] GROUP BY category, Month The purpose of the query is to sum quantities of a product in different categories for different months. I want to aggregate categories lik...
Django has the great new annotate() function for querysets. However I can't get it to work properly for multiple annotations in a single queryset. For example, tour_list = Tour.objects.all().annotate( Count('tourcomment') ).annotate( Count('history') ) A tour can contain multiple tourcomment and history entries. I'm trying to get ho...
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other ...
Update can't work. sqlstr ="UPDATE emp SET bDate='"+Convert.ToDateTime(txtbDate.Text)+"'"; can't update emp table. I tried also using Parse method. It throws error message : The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. ...
Hi I want to load some data with a SP. I've put a SP in a Linq to SQL Class and I don't know how to use it for loading it's data in a datagrid. In LinqToSqlDomainService I can't figure out how to call a SP. What steps should I use. Any samples of this ? All samples use a table. Thank's ...
CF8 and MySQL5, for loop within INSERT INTO. I am getting form data and trying to optimize an insert statement to loop within the cfquery. The best case scenario would be that the loop is just around the VALUES in order to have a single INSERT, but I had issues in trying to ID the second iteration and put a comma at the beginning of th...
Joel is always talking about how great SQL Compare from Redgate software is. SQL Compare only works with Microsoft SQL Server databases, but is there a good piece of software that works with MySQL and the other common databases that are part of the LAMP stack (PostgreSQL, Oracle, etc)? ...
SQL Server Question: I have a table with appx. 1000 already existing rows, and 5 columns How can I update the value at row Y in column ? I don't have any keys or conditions for the where. ...
Hello. Is there a way to retrieve the filename for filegroup filestream? I try to create a new database in tsql, i need to create the filestream path for the database. but i dont know the path of the folder to put the filestream in. So i try to get the filename path of another database on the same server to configure the new database....
I'm trying to pull results from a database (sql server 2005) which takes 4 tables: Subscriber S, Member M, ClaimLines L, ClaimHistoryLines H Query is as follows: select S.SBSB_ID, M.MEME_NAME, (CASE L.CLCL_ID WHEN '' THEN H.CLCL_ID ELSE L.CLCL_ID END) AS CLAIM_ID FROM CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_H...
Hello, I have a table representing standards of alloys. The standard is partly based on the chemical composition of the alloys. The composition is presented in percentages. The percentage is determined by a chemical composition test. Sample data. But sometimes, the lab cannot measure below a certain percentage. So they indicate that the...