|
View previous topic :: View next topic
|
| Author |
Message |
krithikaj
New User
Joined: 10 Aug 2008
Posts: 45
Location: India
|
|
|
|
Hi,
I have a SQL as below.
SELECT
AWC.WORKFLOW_ACTN_ID
, WAL.LABEL_TXT
, WAL.LANGUAGE_CODE
, WCA.ACTN_GROUP_ID
FROM APPR_WRKFLW_CHAIN AWC
INNER JOIN WRKFLW_CHAIN_ACTN WCA ON
WCA.DOC_TYPE_CD = AWC.DOC_TYPE_CD
AND WCA.COUNTRY_CODE = AWC.COUNTRY_CODE
AND WCA.BUS_FORMAT_TYPE_CD = AWC.BUS_FORMAT_TYPE_CD
AND WCA.INPUT_TYPE_CODE = AWC.INPUT_TYPE_CODE
AND WCA.AD_GROUP_ID = AWC.AD_GROUP_ID
LEFT OUTER JOIN WRKFLW_ACTN_LABEL WAL ON
WAL.DOC_TYPE_CD = WCA.DOC_TYPE_CD
AND WAL.COUNTRY_CODE = WCA.COUNTRY_CODE
AND WAL.BUS_FORMAT_TYPE_CD = WCA.BUS_FORMAT_TYPE_CD
AND WAL.INPUT_TYPE_CODE = WCA.INPUT_TYPE_CODE
AND WAL.AD_GROUP_ID = WCA.AD_GROUP_ID
AND WAL.ACTN_SEQ_NBR = WCA.ACTN_SEQ_NBR
WHERE AWC.DOC_TYPE_CD = 5011
AND AWC.COUNTRY_CODE = 'US'
AND AWC.BUS_FORMAT_TYPE_CD = 496
AND AWC.INPUT_TYPE_CODE = 'D'
AND AWC.AD_GROUP_ID = 'FSSNAUS-APPOExpenseTeam'
AND WCA.WORKFLOW_ACTN_ID NOT IN
(CASE :WS-VAR
WHEN '0' THEN 4
WHEN '1' THEN
(3,4,13)
END)
This SQL gives -811 as CASE expression cannot have multiple values in THEN clause.
What is required:
I would like to select the data where WCA.WORKFLOW_ACTN_ID not in 3,4,13 when my WS-VAR is 0 and
WCA.WORKFLOW_ACTN_ID not in 3,4,13 when my WS-VAR is 1
Can someone help whether what would be the possible solution.
Thanks
K J |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
why a case on :ws-var
create a table ws-tab with 3 elemenents
| Code: |
IF ws-var = 0
move 4 to ws-tab-el (1)
move 4 to ws-tab-el (2)
move 4 to ws-tab-el (3)
ELSE
move 3 to ws-tab-el (1)
move 4 to ws-tab-el (2)
move 13to ws-tab-el (3)
end-if
EXEC SQL
...
and WCA.WORKFLOW_ACTN_ID NOT IN ws-tab
END-EXEC |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| krithikaj wrote: |
| This SQL gives -811 as CASE expression cannot have multiple values in THEN clause |
I do not think that is a correct (or rational) assumption.
a -811 has nothing to do with CASE expression.
would be interesting to know what is the data definition of WS-VAR
the CASE expression did not build a proper IN-list,
which would not cause a -811.
you got a -811 because your sql returned more than one row. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| dbzTHEdinosauer wrote: |
| krithikaj wrote: |
| This SQL gives -811 as CASE expression cannot have multiple values in THEN clause |
I do not think that is a correct (or rational) assumption.
a -811 has nothing to do with CASE expression.
would be interesting to know what is the data definition of WS-VAR
the CASE expression did not build a proper IN-list,
which would not cause a -811.
you got a -811 because your sql returned more than one row. |
I don't think a CASE can return a IN-list, and you do get -811 if a case returns more than 1 value (result of a subselect).
I doubt if the SQL posted does precompile. probably gives an -104 on the , in the in-list.
you could solve it in SQL using
...
and case
when :ws-var = 0 and WCA.WORKFLOW_ACTN_ID <> 4 then 'OK'
when :ws-var = 1 and WCA.WORKFLOW_ACTN_ID not in (3,4,13) then 'OK'
else 'NOK' END = 'OK' |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
I agrre Guy_C, if the CASE was generating a subselect, but
it was dealing with a Host-Variable. |
|
| Back to top |
|
|
krithikaj
New User
Joined: 10 Aug 2008
Posts: 45
Location: India
|
|
|
|
Thanks for the replies. I am trying to use the CASE statement given by GUYC. I tried to run the SQL on QMF with one of the columns of the table and i didn't find any error.
I would notify the result if I get actual results correctly.
Regards
K J |
|
| Back to top |
|
|
|
|
