Author |
Topic |
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-18 : 13:46:27
|
This should be pretty straightforward, so I'm thing I have a syntax error somewhere in my statement. Been trying different variations for almost two hours, no luck yet! Here's my code, simplified as much as I can. Any suggestions are greatly appreciated.DECLARE @Calendar1 AS DateTime DECLARE @Calendar2 AS DateTime SET @Calendar1 = '{{{ Please choose a start date. }}}' SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}' SELECT O.OrderId, O.CustID, O.OriginName, "Missing Signature" = CASE WHEN PA.CreatedWhen IS NULL THEN 'Missing' ELSE 'ok' ENDFROM tblOrder AS OINNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)GROUP BY O.OrderId, O.CustID, O.OriginName |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-18 : 14:20:58
|
Your CASE statement seems fine, but your WHERE clause is suspicious.If this is true PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 then PA.CreatedWhen IS NULL is never true, so your statement will only do thingsif PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 is true and PA.ScannedWhen IS NULL.That is, the first part of your case PA.CreatedWhen IS NULL will only fire off when PA.ScannedWhen IS NULL.JimJimEveryday I learn something that somebody else already knew |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-18 : 14:42:07
|
Good call Jim. Actually I shortened the WHERE clause in order to eliminate as much extraneous stuff as possible, i.e. to simplify the thing. For example, if I completely eliminate the WHERE clause I continue to get an error. However, when I eliminate:"Missing Signature" =CASE WHEN PA.CreatedWhen IS NULL THEN 'Missing'ELSE 'ok'ENDI get no error. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-18 : 14:53:25
|
What is the error you are getting?JimEveryday I learn something that somebody else already knew |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-18 : 15:06:34
|
I don't know since I'm forced to write the code through an interface provided by our software vendor. It appears to be called cmdFind_Click. Or maybe that's just a generic name, not sure! It does says Error nmuber 0. And am pretty sure it is T-SQL since it's using Microsoft's SQL. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-18 : 15:17:17
|
There's nothing worng with your case statement as presented, but without an error message or the full code, it'd be just guessing at what's wrong. Here's my first guess, that I should have caught earlier, you need to group by your case statement!SELECT O.OrderId, O.CustID,O.OriginName,"Missing Signature" =CASE WHEN PA.CreatedWhen IS NULL THEN 'Missing'ELSE 'ok'ENDFROM tblOrder AS OINNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)GROUP BY O.OrderId, O.CustID, O.OriginName,CASE WHEN PA.CreatedWhen IS NULL THEN 'Missing'ELSE 'ok'ENDJimEveryday I learn something that somebody else already knew |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-18 : 15:21:32
|
To further the mystery, when I change my criteria so that the CASE reads only from the primary table, it comes up with a result. That is, I chnaged thisCASE WHEN PA.CreatedWhen IS NULL THEN 'Missing'ELSE 'ok'ENDto thisCASE WHEN O.OrderID IS NULL THEN 'Missing'ELSE 'ok'ENDSo it appears it will only function when I use the first table in the query, and any further joins result in errors. Is this normal? |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-18 : 15:32:35
|
Your code runs perfectly. Thank you!Am wondering, why does it run without an error when not adding the CASE to the GROUP BY but changing the criteria inside the CASE to the primary table SELECTed? Is this some weird quirk in T-SQL? |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-19 : 16:21:09
|
Will this query run with a subselect? I can't seem to get it to work that way.SELECT O.OrderId, O.CustID,O.OriginName,"Missing Signature" =CASE WHEN PA.CreatedWhen IS NULL AND PA.OrderID NOT IN (SELECT ST.PKID FROM Signatures AS ST)THEN 'Missing'ELSE 'ok'ENDFROM tblOrder AS OINNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)GROUP BY O.OrderId, O.CustID, O.OriginName,CASE WHEN PA.CreatedWhen IS NULL AND PA.OrderID NOT IN IN (SELECT ST.PKID FROM Signatures AS ST) THEN 'Missing' ELSE 'ok'END |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-19 : 23:29:47
|
You can't include subqueries in GROUP BY list--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 01:22:49
|
quote: Originally posted by dwdwone Will this query run with a subselect? I can't seem to get it to work that way.SELECT O.OrderId, O.CustID,O.OriginName,"Missing Signature" =CASE WHEN PA.CreatedWhen IS NULL AND PA.OrderID NOT IN (SELECT ST.PKID FROM Signatures AS ST)THEN 'Missing'ELSE 'ok'ENDFROM tblOrder AS OINNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)GROUP BY O.OrderId, O.CustID, O.OriginName,CASE WHEN PA.CreatedWhen IS NULL AND PA.OrderID NOT IN IN (SELECT ST.PKID FROM Signatures AS ST) THEN 'Missing' ELSE 'ok'END
you can get it working so far as subselect fields are also included in GROUP BY or you apply aggregation over them------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-20 : 13:14:34
|
I thought you couldn't put a subselect in a GROUP BY? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 13:20:06
|
can you show your subselect ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-20 : 13:42:28
|
Certainly. I've left out of a few extranneous columns and tables to keep it simple. Am hoping that's the proper way to do it. Am still new at this!I have two sets of data. One is a set of orders which have not been scanned. The other set are orders which may or may not have been scanned but are missing a signature.SELECT O.OrderId, O.CustID,O.OriginName,COUNT(*) AS TotalProblemPackages,SUM(CASE WHEN pa.CreatedWhen IS NOT NULL AND pa.ScannedWhen IS NULL and PA.Type = 2 THEN 1 ELSE 0 END) AS NotScannedIn,"Missing Signature" =CASE WHEN PA.CreatedWhen IS NULL AND PA.OrderID NOT IN (SELECT ST.PKID FROM Signatures AS ST)THEN 'Missing'ELSE 'ok'ENDFROM tblOrder AS OINNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)ORO.Orderid NOT IN (SELECT ST.PKID FROM tblSignature_table AS ST WHERE ST.PKID = O.Orderid)GROUP BY O.OrderId, O.CustID, O.OriginName,CASE WHEN PA.CreatedWhen IS NULL AND PA.OrderID NOT IN (SELECT ST.PKID FROM Signatures AS ST)THEN 'Missing'ELSE 'ok'END |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-20 : 13:47:13
|
Would this work?SELECT O.OrderId, O.CustID,O.OriginName,"Missing Signature" =CASE WHEN PA.CreatedWhen IS NULL AND st.PKID IS nullTHEN 'Missing'ELSE 'ok'ENDFROM tblOrder AS OINNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderIDLEFT JOIN Signatures st on PA.OrderID = st.PKIDWHERE PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1 AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)GROUP BY O.OrderId, O.CustID, O.OriginName,CASE WHEN PA.CreatedWhen IS NULL AND st.PKID IS nullTHEN 'Missing'ELSE 'ok'ENDEveryday I learn something that somebody else already knew |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-20 : 13:57:34
|
That would work except I'm dealing with two sets of resulting data. In essence, I'm trying to put several possible errors into one query. To do this, I used an OR. However, I also need to sort out the results between the two sets and display them when appropriate. |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-02-20 : 15:03:15
|
I spoke to soon. It is working perfectly. Thank you! |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-20 : 15:25:02
|
You're welcome!JimEveryday I learn something that somebody else already knew |
|
|
|