|
View previous topic :: View next topic
|
| Author |
Message |
darakhshan
New User
Joined: 03 Mar 2008
Posts: 32
Location: India
|
|
|
|
I am trying to use host variables conditionally in a where clause of my program.
the variables i have declared are:
01 WS-REGULAR-VAR PIC X(1).
01 WS-EXCEPTIONS.
05 CODE1 PIC X(1) VALUE 'A'.
05 CODE2 PIC X(1) VALUE 'B'.
05 CODE3 PIC X(1) VALUE 'C'.
now, only when the value in WS-REGULAR-VAR is 'X' i want to use the values in WS-EXCEPTIONS.
what I am trying is shown below. can i use this or should i be using IN(CODE1,CODE2,CODE3) instead??
exec sql
select
--
--
where
my_field in
case
:WS-REGULAR-VAR = 'X'
then (:WS-EXCEPTIONS)
else (:WS-REGULAR-VAR)
end
and --
and --
with ur
end-exec |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you post some sample data and the result you want when that sample data is processed by this query.
When posting the sample data, use the Code tag for readability. Use Preview to see the post as it will appear to the forum (rather than what is seen in the Reply Editor). |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Instead of WS-EXCEPTIONS give :CODE1,:CODE2,:CODE3 ...
Using CASE with an IN predicate should be with a fullselect. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
best way would be : to do this before your SQL
| Code: |
01 WS-REGULAR-VAR PIC X(1).
01 WS-EXCEPTIONS.
05 CODE1 PIC X(1) VALUE 'A'.
05 CODE2 PIC X(1) VALUE 'B'.
05 CODE3 PIC X(1) VALUE 'C'.
01 WS-HV.
05 CODE1 PIC X(1).
05 CODE2 PIC X(1).
05 CODE3 PIC X(1).
if WS-REGULAR-VAR = 'X'
then move ws-exceptions to ws-HV
else move WS-REGULAR-VAR to CODE1 of ws-HV
move WS-REGULAR-VAR to CODE2 of ws-HV
move WS-REGULAR-VAR to CODE3 of ws-HV
end-if
exec sql
...
where my_field in (:WS-HV)
end-exec |
|
|
| Back to top |
|
|
|
|
