Need Help With CASE Query - SQL Server Forums

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

Need Help With CASE Query

Author

Topic

dwdwonw
Starting Member

21 Posts

Posted - 2011-10-10 : 19:47:04

I've modified a canned query and cannot manage to get rid of an input value. Here it is:

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.OriginName, O.DestName, O.CSR, O.ReadyTimeFrom, O.DueTimeTo, OS.AtDestination,

DATEDIFF(n,O.DueTimeTo,OS.Delivered) AS 'Min Late At Dest',

'MinLate Deld' = CASE
WHEN OS.AtDestination IS NOT NULL
THEN DateDiff(minute,O.DueTimeTo,OS.AtDestination)
ELSE DateDiff(minute,O.DueTimeTo,OS.Delivered)
END,
OD.DriverID, O.SpecialInst

FROM tblOrder AS O
INNER JOIN tblOrderStatus AS OS ON OS.OrderID = O.OrderID
LEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderID

WHERE CASE
WHEN OS.AtDestination IS NOT NULL
THEN DateDiff(minute,O.DueTimeTo,OS.AtDestination)
ELSE DateDiff(minute,O.DueTimeTo,OS.Delivered)
END >= '<<Grace Minutes>>'
AND O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2

When I remove the Grace Minutes input from the original query ('<<Grace Minutes>>'), the query crashes on me. What am I doing wrong?

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-10 : 21:39:58

Could it be that you're still referencing grace minutes int the query "END >= '<<Grace Minutes>>' "

Likes to run, hates the runs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:06:18

the query crashes on me
DO you mean it errors out? if yes, can you post exact error message? or is it that query window freezes?

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-11 : 01:09:07

Please post your exact error with error code and does your query window get hangs.....

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-11 : 04:13:54

CASE
WHEN OS.AtDestination IS NOT NULL
THEN DateDiff(minute,O.DueTimeTo,OS.AtDestination)
ELSE DateDiff(minute,O.DueTimeTo,OS.Delivered)
END
this portion in the where clause is giving you a duration in minutes which you are comparing with grace minutes >= '<<Grace Minutes>>' which is OK. if you remove <<Grace Minutes>> SQL server will have nothing on the right side of the comparision oprator (>=) and hence it'll crash.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 04:18:43

Are you saying this fails?

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.OriginName, O.DestName, O.CSR, O.ReadyTimeFrom, O.DueTimeTo, OS.AtDestination,

DATEDIFF(n,O.DueTimeTo,OS.Delivered) AS 'Min Late At Dest',

'MinLate Deld' = CASE
WHEN OS.AtDestination IS NOT NULL
THEN DateDiff(minute,O.DueTimeTo,OS.AtDestination)
ELSE DateDiff(minute,O.DueTimeTo,OS.Delivered)
END,
OD.DriverID, O.SpecialInst

FROM tblOrder AS O
INNER JOIN tblOrderStatus AS OS ON OS.OrderID = O.OrderID
LEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderID

WHERE O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2

What is the error?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dwdwonw
Starting Member

21 Posts

Posted - 2011-10-11 : 09:53:20

I don't have acess to the underlying prompt and have to write my queries through a user interface which I believe is part of Active Reports. All I can say for sure is that when I remove the

>= '<<Grace Minutes>>'

the system won't allow the query to process.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 12:23:29

quote:
Originally posted by dwdwonw

I don't have acess to the underlying prompt and have to write my queries through a user interface which I believe is part of Active Reports. All I can say for sure is that when I remove the

>= '<<Grace Minutes>>'

the system won't allow the query to process.

are you trying to pass the value as a constant? or is it a parameter?

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

dwdwonw
Starting Member

21 Posts

Posted - 2011-10-11 : 14:46:52

It appears to be a variable. The system tells me 'Unexpected Error" and gives me the following detail:

Proc Arguments
Empty =
Local Variables
msTableName =
strQuery = DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
SET @Calendar1 = '10/9/2011'
SET @Calendar2 = '10/12/2011'

Module Variables
QueryField(0) =
QueryField(1) =
QueryField(2) =
QueryField(3) =
QueryFieldType(0) = 1
QueryFieldType(1) = 1
QueryFieldType(2) = 1
QueryFieldType(3) = 1
QueryCondition(0) =
QueryCondition(1) =
QueryCondition(2) =
QueryCondition(3) =
QueryValue(0) =
QueryValue(1) =
QueryValue(2) =
QueryValue(3) =
RetVal = 730612
IsCustomerLookup = False

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 01:34:07

which is this application in which you're calling the query?

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

dwdwonw
Starting Member

21 Posts

Posted - 2011-10-14 : 13:48:00

It's a proprietary mission critical app called cxt. The interface is Active Reports.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources