SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple CASE WHEN not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 02/18/2013 :  13:46:27  Show Profile  Reply with Quote
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
2869 Posts

Posted - 02/18/2013 :  14:20:58  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/18/2013 :  14:42:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/18/2013 :  14:53:25  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/18/2013 :  15:06:34  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/18/2013 :  15:17:17  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/18/2013 :  15:21:32  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/18/2013 :  15:32:35  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/19/2013 :  16:21:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 02/19/2013 :  23:29:47  Show Profile  Reply with Quote
You can't include subqueries in GROUP BY list

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  01:22:49  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/20/2013 :  13:14:34  Show Profile  Reply with Quote
I thought you couldn't put a subselect in a GROUP BY?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  13:20:06  Show Profile  Reply with Quote
can you show your subselect ?

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

Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 02/20/2013 :  13:42:28  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/20/2013 :  13:47:13  Show Profile  Reply with Quote
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

USA
68 Posts

Posted - 02/20/2013 :  13:57:34  Show Profile  Reply with Quote
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

USA
68 Posts

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/20/2013 :  15:25:02  Show Profile  Reply with Quote
You're welcome!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000