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.
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? |
|
|
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 |
|
|
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?
|
|
|
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
|
|
|
|
|
|
|
|