Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple CASE WHEN not working

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'
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

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 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
Go to Top of Page

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'
END

I get no error.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-18 : 14:53:25
What is the error you are getting?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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.
Go to Top of Page

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'
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
Go to Top of Page

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 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?
Go to Top of Page

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?
Go to Top of Page

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'
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
Go to Top of Page

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
Go to Top of Page

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'
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/

Go to Top of Page

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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 13:20:06
can you show your subselect ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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'
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
Go to Top of Page

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 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
Go to Top of Page

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.
Go to Top of Page

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-02-20 : 15:03:15
I spoke to soon. It is working perfectly. Thank you!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-20 : 15:25:02
You're welcome!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -