sql

Having difficulty combining JET SQL queries

Warning: Here be beginner SQL! Be gentle... I have two queries that independently give me what I want from the relevant tables in a reasonably timely fashion, but when I try to combine the two in a (fugly) union, things quickly fall to bits and the query either gives me duplicate records, takes an inordinately long time to run, or refu...

MySQL SELECT statement using Regex to recognise existing data

My web application parses data from an uploaded file and inserts it into a database table. Due to the nature of the input data (bank transaction data), duplicate data can exist from one upload to another. At the moment I'm using hideously inefficient code to check for the existence of duplicates by loading all rows within the date rang...

Oracle SQL: LEAST() returns multiple rows...?

A co-worker just came to me with a puzzling SQL query: (essentially) SELECT LEAST(id) FROM tableA A, tableB B WHERE a.name = b.name(+) The result set returned lists three numbers however: LEAST(id) -------------- 621 644 689 (all being IDs that meet the query as if it lacked the LEAST function all together) Why? =) ...

Watching variables in SSIS during debug

I have a project in SSIS and I've added an Execute SQL Task which sends its result out to a variable. I wanted to confirm the value because I was worried that it would try to write it out as a resultset object rather than an actual integer (in this case I'm returning a COUNT). My first thought was just to run it in debug mode and add th...

Multiple WHEN inside no-expression CASE in SQL?

DECLARE @TestVal int SET @TestVal = 5 SELECT CASE WHEN @TestVal <=3 THEN 'Top 3' ELSE 'Other' END I saw this sample code online but I couldn't find an example where there was no expression and it had more than one WHEN, so I am wondering if this type of thing is OK: DECLARE @TestVal int SET @TestVal = 5 SEL...

Can Pro*Fortran connect to Oracle 10g on windows?

Hi, has anyone successfully compiled a pro*fortran program on windows that connects to Oracle 10g? I can only seem to find information that suggests that pre-compilation of embedded sql is possible against Oracle 8i and earlier. If you have done this please can you post any links you have for compilers, oracle downloads/articles etc a...

SQL Server Procedure returns multiple tables - Insert results into tables

I have a procedure that returns multiple tables; eg: PROCEDURE Something AS BEGIN SELECT 1,2,3 SELECT 4,5 SELECT 9,10,11 END I would like to take each table from the result and insert it into a series of tables/temp tables - one for each record set. Is this possible? ...

SQL Equivalent of COUNTIF()

I'm building a query with a GROUP BY clause that needs the ability to count records based only on a certain condition (field value is equal to 1). SELECT UID, COUNT(UID) AS TotalRecords, SUM(ContractDollars) AS ContractDollars, --- Get the average of all records that satisfy the condition. (COUNTIF(MyColumn, 1) / COUNT(UID)...

What is the best way to generate an ID for a SQL Insert?

What is the best, DBMS-independent way of generating an ID number that will be used immediately in an INSERT statement, keeping the IDs roughly in sequence? ...

Performance implications of sql 'OR' conditions when one alternative is trivial ?

I'm creating a stored procedure for searching some data in my database according to some criteria input by the user. My sql code looks like this: Create Procedure mySearchProc ( @IDCriteria bigint=null, ... @MaxDateCriteria datetime=null ) as select Col1,...,Coln from MyTable where (@IDCriteria is null or ID=@IDCriteria) ... and (@Max...

After insert, update trigger not running [solved]

I have two triggers After Insert or Update and Instead of Insert. It appears that the after trigger is not running or sending the correct data. I have verified the correct operation of Z_UpdateStageTable stored procedure and the Instead of Insert trigger. Removing the Instead of Insert trigger doesn't have any affect. The After Insert, ...

Improving performance of cluster index GUID primary key

Hi, I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient. ...

Replace spaces in URL with underline

How can I replace spaces in URL with a underline(_)? $query = mysql_query("SELECT * FROM users WHERE username = '$_GET[user]'"); But if a user has a space in her/his username I wanna replace the space with an underline. So the URL for profile.php?user=John Johnson would be profile.php?user=John_Johnson. How can I do this? Thanks! ...

SQL select where repeating, but distinct on another column.

I have the following table: uid | key | email ----------------- 1 | 1 | [email protected] 2 | 2 | [email protected] 3 | 3 | [email protected] 4 | 4 | [email protected] 5 | 4 | [email protected] 6 | 4 | [email protected] 7 | 6 | [email protected] 8 | 7 | [email protected] I'd like to grab all of the rows with distinct key but repeatin...

My SQLParameter isn't passing NULL correctly.

I have this C# webform that has has a date picker box. If the date is set to nothing (the default) I want it to pass NULL to the database. This happens inside my parametrized query. SqlParameter CMActionDate = new SqlParameter(); CMActionDate.ParameterName = "@ActionDate"; if (ActionDate.Equals("")) { CMActionDate.Value = Syste...

Is there a way to explicitly specify a u/p using windows authentication for ms sql?

Is there a way to explicitly specify a user/domain/password when using windows authentication for MS SQL? I mean in the connection string. Edit: I would like to connect to another SQL server with a specific username and password on a different computer so impersonating I don't think is possible. ...

SQL Select syntax workaround

Let's say I have two existing tables, "dogs" and "cats": dog_name | owner ---------+------ Sparky | Bob Rover | Bob Snoopy | Chuck Odie | Jon cat_name | owner ---------+------ Garfield | Jon Muffy | Sam Stupid | Bob How do I write a query with this output? owner | num_dogs | num_cats ------+----------+...

How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?

I Know I can select a column from a subquery using this syntax: SELECT A.SalesOrderID, A.OrderDate, ( SELECT TOP 1 B.Foo FROM B WHERE A.SalesOrderID = B.SalesOrderID ) AS FooFromB FROM A WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4' But what is the correct syntax to use multiple columns from a subqu...

Connecting to an mssql server from a remote machine gives Login failed for user ''

I get this error when I try to connect to a remote SQL server using this connection string. Error: ODBC error: 28000118452[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection. Connection string: "DRIVER={SQL Server};SERVER=testserver,...

How can I have multiple common table expressions in a single SELECT statement?

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions. Declaring a single cte works fine. WITH cte1 AS ( SELECT * from cdr.Location ) select * from cte1 Is it possible to declare and use more than one cte in the same SELECT? ie this sql gives an error WITH cte1 a...