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 DateTimeDECLARE @Calendar2 AS DateTimeSET @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.SpecialInstFROM tblOrder AS OINNER JOIN tblOrderStatus AS OS ON OS.OrderID = O.OrderIDLEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderIDWHERE 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 meDO you mean it errors out? if yes, can you post exact error message? or is it that query window freezes?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 DateTimeDECLARE @Calendar2 AS DateTimeSET @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.SpecialInstFROM tblOrder AS OINNER JOIN tblOrderStatus AS OS ON OS.OrderID = O.OrderIDLEFT JOIN tblOrderDrivers AS OD ON OD.OrderID = O.OrderIDWHERE 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 MVPhttp://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 ArgumentsEmpty = Local VariablesmsTableName = strQuery = DECLARE @Calendar1 AS DateTimeDECLARE @Calendar2 AS DateTimeSET @Calendar1 = '10/9/2011' SET @Calendar2 = '10/12/2011' Module VariablesQueryField(0) = QueryField(1) = QueryField(2) = QueryField(3) = QueryFieldType(0) = 1QueryFieldType(1) = 1QueryFieldType(2) = 1QueryFieldType(3) = 1QueryCondition(0) = QueryCondition(1) = QueryCondition(2) = QueryCondition(3) = QueryValue(0) = QueryValue(1) = QueryValue(2) = QueryValue(3) = RetVal = 730612IsCustomerLookup = 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 MVPhttp://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. |
|
|
|