|
View previous topic :: View next topic
|
| Author |
Message |
gch77
New User
Joined: 16 Mar 2009
Posts: 13
Location: Chennai
|
|
|
|
Hi All
Is it possible to have multiple where clause inside a query based on the WS variable inside a cobol db2 program.
Currently i use this way, but i wanted to know if it could be written better.
| Code: |
IF WS-A = 'Type 1'
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 IN('X','Y') AND
FIELD3 = :WS-FIELD3
END-EXEC
ELSE IF WS-A = 'Type 2'
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 = 'Z' AND
FIELD3 = :WS-FIELD3
END-EXEC
END-IF |
|
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Can you elaborate in which way you want it better?
Persoanlly I could not see any issue in a way logic is written
One I can think is instead of nested IF's use EVALUATE.
Regards,
Chandan |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Yes, I'm also not sure what you want to achieve. You want to make it more difficult to understand: so for what reason? Less to type?
I'd make those literals into 88's on WS-A (which I hope is not really called WS-A) and use EVALUATE TRUE. Everything with nice descriptive names. Simple SQL. Try it. Give it to a colleague and see which way they prefer. Give it to an analyst. |
|
| Back to top |
|
|
gch77
New User
Joined: 16 Mar 2009
Posts: 13
Location: Chennai
|
|
|
|
Sorry for the confusion. My question is, the correct code is like this and it works absolutely fine. My i want to optimize it further.
| Code: |
IF WS-A = 'Type 1'
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 IN('X','Y') AND
FIELD3 = :WS-FIELD3
END-EXEC
ELSE IF WS-A = 'Type 2'
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 = 'Z' AND
FIELD3 = :WS-FIELD3
END-EXEC
END-IF
END-IF |
I would like to avoid repeating the SQL queries twice. Because the queries are one and the same except for the below change.
If WS-A is 'Type 1' the value of FIELD2 to be looked for is in('X','Y').
If WS-A is 'Type 2' the value of FIELD2 to be looked for is 'Z'.
I tried using an 88 variable like
| Code: |
01 WS-FIELD2 PIC(X) VALUE SPACES.
88 WS-XY PIC(X) VALUE 'X','Y'.
88 WS-Z PIC(X) VALUE 'Z'. |
and the code as:
| Code: |
IF WS-A = 'Type 1'
SET WS-XY TO TRUE
ELSE IF WS-A = 'Type 2'
SET WS-Z TO TRUE
END-IF
END-IF |
and query as :
| Code: |
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 = :WS-FIELD2 AND
FIELD3 = :WS-FIELD3
END-EXEC |
But it doesn't work correctly. Always Field2 is looked only for 'X'.
Hope the explanation is clear, Kindly advise. |
|
| Back to top |
|
|
gch77
New User
Joined: 16 Mar 2009
Posts: 13
Location: Chennai
|
|
|
|
Any help on the above pls.  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Why do you want to "optimize it further"? Optimize it in terms of what?
You should read up on the SET and ponder upon how you thought that a one byte field could simultaneously contain two value (X and Y). After the SET to TRUE it is only ever going to contain X. That's not optimisation, that's breaking a working program - why? |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
I think that the TS wants to have 1 SQL statement with the entire WHERE clause defined by a variable.
That can be done, but it requires that you get into the topic of dynamic SQL, which will require a lot more coding. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Yes, Don, I think you're right. But what does that optimize? |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Don't value "optimization" over "maintainability". Your successors will appreciate it. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
I am also not sure what is meant by optimize further..
At a given time only one Sql query going to be executed so it will not cause any performance issue with having extra piece of code
if TS want to achieve above in one query I can see one way but I would not prefer to achieve it in that way. This is untested
| Code: |
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 IN(:WS-X,:WS-Y,:WS-Z) AND
FIELD3 = :WS-FIELD3
END-EXEC |
| Code: |
IF WS-A = 'Type 1'
Populate WS-X and WS-Y with X and Y repectively.
Populate WS-Z with X or high values provided high values not present in table
ELSE
IF WS-A = 'Type 2'
populate WS-X,WS-Y with high values or Z
populate WS-Z with value Z |
Regards,
Chandan |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Don suggested to use a Dynamic SQL and that is the perfect choice here for the requirement as well it is already simplified as you have shown us but still if you want to rewrite it in one then may be below one can suit you but you need to test as you wanted,
| Code: |
EXEC SQL
SELECT FIELD1
,FIELD2
INTO :WS-FIELD1
,:WS-FIELD2
FROM TABLENAME
WHERE FIELD2 IN('X','Y', 'Z') AND
FIELD3 = :WS-FIELD3
IF WS-FIELD2 = "Z"
Process "Type 1" records
Else
Process "Type 2" Records
END-IF |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
what chandan.inst said, but using a group-field for criteria.
| Code: |
01 ws-criteria.
03 ws-crit1 pic X.
03 ws-crit2 pic X.
IF WS-A = 'Type 1'
MOVE 'X' to ws-crit1
MOVE 'Y' to ws-crit2
else
MOVE 'Z' to ws-crit1
MOVE 'Z' to ws-crit2
end-if
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD2 IN :WS-CRIT AND
FIELD3 = :WS-FIELD3
END-EXEC |
|
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
The compiler accepted this statement but I'm not sure it really works (I didn't execute it).
Not sure either it can be called 'optimization'
| Code: |
EXEC SQL
SELECT FIELD1
INTO :WS-FIELD1
FROM TABLENAME
WHERE FIELD3 = :WS-FIELD3 AND
( (:WS-A = 'Type 1' AND FIELD2 IN ('X','Y'))
OR (:WS-A = 'Type 2' AND FIELD2 = 'Z' ) )
END-EXEC |
|
|
| Back to top |
|
|
|
|
