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
 Case not making the select

Author  Topic 

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-08-02 : 10:49:44
The following code is a front end parameter - Quantity but when the user enters '0' it is not returning any rows (there are). Is there something wrong here or to add something?

(Select {?Quantity}
CASE "ALL":
1=1
CASE "Negative":
({ACBALMPK.IRQOH#}<0) OR ({ACBALMPK.IEQOH#}<0)
CASE "Zero":
({ACBALMPK.IRQOH#}=0) OR ({ACBALMPK.IEQOH#}=0)
CASE "Greater than Zero":
({ACBALMPK.IRQOH#}>0;) OR ({ACBALMPK.IEQOH#}>0;))

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-02 : 11:21:05
quote:
Originally posted by AdamWest

The following code is a front end parameter - Quantity but when the user enters '0' it is not returning any rows (there are). Is there something wrong here or to add something?

(Select {?Quantity}
CASE "ALL":
1=1
CASE "Negative":
({ACBALMPK.IRQOH#}<0) OR ({ACBALMPK.IEQOH#}<0)
CASE "Zero":
({ACBALMPK.IRQOH#}=0) OR ({ACBALMPK.IEQOH#}=0)
CASE "Greater than Zero":
({ACBALMPK.IRQOH#}>0;) OR ({ACBALMPK.IEQOH#}>0;))

Does the front end take this code and translate it to T-SQL? If/when it does, what does the resulting T-SQL code look like?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-02 : 11:51:21
[code]SELECT CASE
WHEN ACBALMPK.IRQOH# < 0 OR ACBALMPK.IEQOH# < 0 THEN 'Negative'
WHEN ACBALMPK.IRQOH# = 0 OR ACBALMPK.IEQOH# = 0 THEN 'Zero'
WHEN ACBALMPK.IRQOH# > 0 OR ACBALMPK.IEQOH# > 0 THEN 'Greater than Zero'
ELSE 'ALL'
END AS Quantity[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-08-02 : 12:45:53
For the case in problem, it does not show a sql code because it says that no data is returned. THe nearest thing that works is when Qty > 0 here is the sql for that. Sorry it's rather ugly looking.


SELECT "ACBALMPK"."IELOC1", "ACBALMPK"."IELOC2", "ACBALMPK"."IELOC3", "ACBALMPK"."IRLOC1", "ACBALMPK"."IRLOC2", "ACBALMPK"."IRLOC3", "ICLOC1"."LMLOC1", "ICLOC1"."LMLOC2", "ICLOC1"."LMLOC3", "ICLOC1_1"."LMLOC1", "ICLOC1_1"."LMLOC2", "ICLOC1_1"."LMLOC3", "ICLOC1"."LMLTPC", "ICLOC1_1"."LMLTPC", "ACBALMPK"."IEPRT#", "ACBALMPK"."IRPRT#", "ICPRT1"."IA101", "ICPRT1_1"."IA101", "ICECT6"."CTPT2#", "ICECT6_1"."CTPT2#", "ACBALMPK"."IERIDC", "ACBALMPK"."IEQOH#", "ACBALMPK"."IRQOH#", "ACBALMPK"."IEWHS#", "ACBALMPK"."IRWHS#", "ICLOC1"."LMLCT1", "ICLOC1"."LMLCT2", "ICLOC1"."LMACTF", "ICLOC1_1"."LMLCT1", "ICLOC1_1"."LMLCT2", "ICLOC1_1"."LMACTF", "ICPRT1"."IARCC6", "ICPRT1_1"."IARCC6", "ICPRT1"."IAACTF", "ICPRT1_1"."IAACTF", "ICPRT1"."IAORDF", "ICPRT1_1"."IAORDF", "ICPRT1"."IAPCHF", "ICPRT1_1"."IAPCHF", "ICPRT1"."IAINRF", "ICPRT1_1"."IAINRF", "ICPRT1"."IAMFGF", "ICPRT1_1"."IAMFGF", "ICLOC1"."LMWHS#", "ICPRT1"."IABMU", "ICLOC1"."LMCOM#", "ICLOC1_1"."LMWHS#", "ICPRT1_1"."IABMU", "ICLOC1_1"."LMCOM#"
FROM {oj ((((("A4047N4M"."ASTCCDTA"."ACBALMPK" "ACBALMPK" INNER JOIN "A4047N4M"."ASTDTA"."ICLOC1" "ICLOC1" ON (("ACBALMPK"."IELOC1"="ICLOC1"."LMLOC1") AND ("ACBALMPK"."IELOC2"="ICLOC1"."LMLOC2")) AND ("ACBALMPK"."IELOC3"="ICLOC1"."LMLOC3")) INNER JOIN "A4047N4M"."ASTDTA"."ICLOC1" "ICLOC1_1" ON (("ACBALMPK"."IRLOC1"="ICLOC1_1"."LMLOC1") AND ("ACBALMPK"."IRLOC2"="ICLOC1_1"."LMLOC2")) AND ("ACBALMPK"."IRLOC3"="ICLOC1_1"."LMLOC3")) LEFT OUTER JOIN "A4047N4M"."ASTDTA"."ICPRT1" "ICPRT1" ON "ACBALMPK"."IRPRT#"="ICPRT1"."IAPRT#") LEFT OUTER JOIN "A4047N4M"."ASTDTA"."ICPRT1" "ICPRT1_1" ON "ACBALMPK"."IEPRT#"="ICPRT1_1"."IAPRT#") LEFT OUTER JOIN "A4047N4M"."ASTDTA"."ICECT6" "ICECT6_1" ON "ICPRT1_1"."IAPRT#"="ICECT6_1"."CTPRT#") LEFT OUTER JOIN "A4047N4M"."ASTDTA"."ICECT6" "ICECT6" ON "ICPRT1"."IAPRT#"="ICECT6"."CTPRT#"}
WHERE "ICLOC1"."LMCOM#"='001' AND ("ICLOC1"."LMWHS#"='CRW' OR "ICLOC1_1"."LMWHS#"='CRW') AND ("ACBALMPK"."IRQOH#">0 OR "ACBALMPK"."IEQOH#">0) AND ("ACBALMPK"."IRLOC3"<>'' AND "ACBALMPK"."IRLOC2"<>'' AND "ACBALMPK"."IRLOC1"<>'' OR "ACBALMPK"."IELOC3"<>'' AND "ACBALMPK"."IELOC2"<>'' AND "ACBALMPK"."IELOC1"<>'') AND "ICLOC1_1"."LMCOM#"='001'





quote:
Originally posted by James K

quote:
Originally posted by AdamWest

The following code is a front end parameter - Quantity but when the user enters '0' it is not returning any rows (there are). Is there something wrong here or to add something?

(Select {?Quantity}
CASE "ALL":
1=1
CASE "Negative":
({ACBALMPK.IRQOH#}<0) OR ({ACBALMPK.IEQOH#}<0)
CASE "Zero":
({ACBALMPK.IRQOH#}=0) OR ({ACBALMPK.IEQOH#}=0)
CASE "Greater than Zero":
({ACBALMPK.IRQOH#}>0;) OR ({ACBALMPK.IEQOH#}>0;))

Does the front end take this code and translate it to T-SQL? If/when it does, what does the resulting T-SQL code look like?

Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-08-02 : 13:04:03
THE CR Syntax is not allowing this way, but I will check into it.


quote:
Originally posted by SwePeso

SELECT	CASE
WHEN ACBALMPK.IRQOH# < 0 OR ACBALMPK.IEQOH# < 0 THEN 'Negative'
WHEN ACBALMPK.IRQOH# = 0 OR ACBALMPK.IEQOH# = 0 THEN 'Zero'
WHEN ACBALMPK.IRQOH# > 0 OR ACBALMPK.IEQOH# > 0 THEN 'Greater than Zero'
ELSE 'ALL'
END AS Quantity



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page
   

- Advertisement -