| Author |
 Topic  |
|
|
dwdwone
Starting Member
USA
37 Posts |
 Posted - 02/18/2013 : 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'
END
FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
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
|
Edited by - dwdwone on 02/18/2013 13:48:19
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/18/2013 : 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 things
if 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.
Jim
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
dwdwone
Starting Member
USA
37 Posts |
Posted - 02/18/2013 : 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'
END
I get no error. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/18/2013 : 14:53:25
|
What is the error you are getting?
Jim
Everyday I learn something that somebody else already knew |
|
|
|
dwdwone
Starting Member
USA
37 Posts |
Posted - 02/18/2013 : 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. |
Edited by - dwdwone on 02/18/2013 15:15:11 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/18/2013 : 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'
END
FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
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'
END
Jim
Everyday I learn something that somebody else already knew |
|
|
|
dwdwone
Starting Member
USA
37 Posts |
Posted - 02/18/2013 : 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 this
CASE
WHEN PA.CreatedWhen IS NULL THEN 'Missing'
ELSE 'ok'
END
to this
CASE
WHEN O.OrderID IS NULL THEN 'Missing'
ELSE 'ok'
END
So 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
Starting Member
USA
37 Posts |
Posted - 02/18/2013 : 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
Starting Member
USA
37 Posts |
Posted - 02/19/2013 : 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'
END
FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
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
Flowing Fount of Yak Knowledge
India
1717 Posts |
Posted - 02/19/2013 : 23:29:47
|
You can't include subqueries in GROUP BY list
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48098 Posts |
Posted - 02/20/2013 : 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'
END
FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
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 MVP
http://visakhm.blogspot.com/
|
|
|
|
dwdwone
Starting Member
USA
37 Posts |
Posted - 02/20/2013 : 13:14:34
|
| I thought you couldn't put a subselect in a GROUP BY? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48098 Posts |
Posted - 02/20/2013 : 13:20:06
|
can you show your subselect ?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dwdwone
Starting Member
USA
37 Posts |
Posted - 02/20/2013 : 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'
END
FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
PA.Createdwhen BETWEEN @Calendar1 AND @Calendar2 + 1
AND (PA.CreatedWhen IS NULL OR PA.ScannedWhen IS NULL)
OR
O.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 |
Edited by - dwdwone on 02/20/2013 14:26:27 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/20/2013 : 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 null
THEN 'Missing'
ELSE 'ok'
END
FROM tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
LEFT JOIN Signatures st on PA.OrderID = st.PKID
WHERE
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 null
THEN 'Missing'
ELSE 'ok'
END
Everyday I learn something that somebody else already knew |
|
|
|
dwdwone
Starting Member
USA
37 Posts |
Posted - 02/20/2013 : 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
Starting Member
USA
37 Posts |
Posted - 02/20/2013 : 15:03:15
|
| I spoke to soon. It is working perfectly. Thank you! |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/20/2013 : 15:25:02
|
You're welcome!
Jim
Everyday I learn something that somebody else already knew |
|
|
| |
Topic |
|
Simple CASE WHEN not working
