View previous topic :: View next topic
|
Author |
Message |
jerryte
Active User
Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
|
|
|
|
Is it possible to have an sql IN predicate that uses a COBOL table or list of values? I am trying to avoid using dynamics sql for performance reasons
Code: |
WHERE column IN (:WS-MY-COBOL-TABLE)
|
WS-MY-COBOL-TABLE would be either an OCCURS of values or field with a list of values. I have been reading the SQL Reference manual but no luck. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Why do you think for dynamic sql there would be any performance issues? |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
As long as your host variable is a list of comma separated, quoted values then there shouldn't be a problem because that is what SQL expects. If it isn't then why ask? |
|
Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
jerryte wrote: |
Is it possible to have an sql IN predicate that uses a COBOL table or list of values? I am trying to avoid using dynamics sql for performance reasons
Code: |
WHERE column IN (:WS-MY-COBOL-TABLE)
|
WS-MY-COBOL-TABLE would be either an OCCURS of values or field with a list of values. I have been reading the SQL Reference manual but no luck. |
Just how hard would it have been to try it, compiling a tiny COBOL program? |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
The most obvious approach is a bit cumbersome if the IN list is long, but it works:
Quote: |
Where column IN (:WS-HV1, :WS-HV2, ... :WS-HVn) |
|
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
It looks like you want something like this:
Code: |
01 WS-VALID-VALUE-INLIST.
05 WS-VS-01 PIC S9(4) COMP value +01.
05 WS-VS-02 PIC S9(4) COMP value +02.
05 WS-VS-03 PIC S9(4) COMP value +03.
05 WS-VS-04 PIC S9(4) COMP value +04.
05 WS-VS-05 PIC S9(4) COMP value +05.
05 WS-VS-06 PIC S9(4) COMP value +06.
05 WS-VS-07 PIC S9(4) COMP value +07.
...
EXEC SQL
...
WHERE VALID_VALUE IN (:WS-VALID-VALUE-INLIST)
END-EXEC |
This should work. Please try and let us know. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
I do not think that that will work. DB2 will only see one, long, binary string whereas it expects a list of comma separated values. |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
There is no harm in trying!!!
Just like Prino said:
Quote: |
Just how hard would it have been to try it, compiling a tiny COBOL program? |
|
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
the fact that something compiles does NOT mean that the program will produce the expected results
imo when posting snippets it is the responsibility of the poster to check that the snippet provides the proper results
posting wrong coding snippets is bad forum behavior |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
enrico-sorichetti wrote: |
the fact that something compiles does NOT mean that the program will produce the expected results
imo when posting snippets it is the responsibility of the poster to check that the snippet provides the proper results
posting wrong coding snippets is bad forum behavior |
I understand that enrico. I have posted only because I have faith in my suggestion. I have tried this in past and it worked for me. The only reason I'm not sure is I don't know if it depends upon the DB2 version and currently I don't have access to mainframe to re-test. That's why I asked to try this approach.
And at the same time I would like to know: posting your opinion without having any concrete proof: Is that a bad forum behavior too, in your humble opinion? |
|
Back to top |
|
|
jerryte
Active User
Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
|
|
|
|
mistah kurtz wrote: |
It looks like you want something like this:
Code: |
01 WS-VALID-VALUE-INLIST.
05 WS-VS-01 PIC S9(4) COMP value +01.
05 WS-VS-02 PIC S9(4) COMP value +02.
05 WS-VS-03 PIC S9(4) COMP value +03.
05 WS-VS-04 PIC S9(4) COMP value +04.
05 WS-VS-05 PIC S9(4) COMP value +05.
05 WS-VS-06 PIC S9(4) COMP value +06.
05 WS-VS-07 PIC S9(4) COMP value +07.
...
EXEC SQL
...
WHERE VALID_VALUE IN (:WS-VALID-VALUE-INLIST)
END-EXEC |
This should work. Please try and let us know. |
I tried this and it worked. Thanka |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Maybe I will throw that book away! It only goes up to V5. |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
Nic Clouston wrote: |
Maybe I will throw that book away! It only goes up to V5. |
Surprised me too. I know there was a time when wasn't supported, but cannot say when it changed. |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
@jerryte: Glad to know that it worked. You are welcome.
@don.leahy: I tried this somewhere around 2013. Can't say for sure when it changed. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
Back to top |
|
|
jerryte
Active User
Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
|
|
|
|
I had tried but I used an TABLE instead of a list. DB2 didn't like it. I read the reference manuals but it doesn't say anything about this. I do research a topic before I post but I missed this one.
I don't know about the performance of dynamic sql in a high usage environment. I have not had a chance to research this. I will add this to my long list of things to try before I retire. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
okay, I think much more effective way to handle in such situations is to make a use of GTT. |
|
Back to top |
|
|
|