Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL IN predicate using COBOL table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 194
Location: Toronto, ON, Canada

PostPosted: Fri Jun 29, 2018 10:30 pm    Post subject: SQL IN predicate using COBOL table
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1942
Location: NY,USA

PostPosted: Sat Jun 30, 2018 4:10 am    Post subject:
Reply with quote

Why do you think for dynamic sql there would be any performance issues?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2035
Location: UK

PostPosted: Sat Jun 30, 2018 3:43 pm    Post subject: Reply to: SQL IN predicate using COBOL table
Reply with quote

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
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1113
Location: Oostende, Belgium

PostPosted: Sat Jun 30, 2018 3:54 pm    Post subject: Re: SQL IN predicate using COBOL table
Reply with quote

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
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 667
Location: Whitby, ON, Canada

PostPosted: Tue Jul 03, 2018 7:42 pm    Post subject:
Reply with quote

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
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jul 03, 2018 8:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2035
Location: UK

PostPosted: Tue Jul 03, 2018 9:23 pm    Post subject: Reply to: SQL IN predicate using COBOL table
Reply with quote

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
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jul 03, 2018 9:29 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10488
Location: italy

PostPosted: Tue Jul 03, 2018 9:44 pm    Post subject: Reply to: SQL IN predicate using COBOL table
Reply with quote

the fact that something compiles does NOT mean that the program will produce the expected results icon_cool.gif

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
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Jul 03, 2018 9:51 pm    Post subject: Re: Reply to: SQL IN predicate using COBOL table
Reply with quote

enrico-sorichetti wrote:
the fact that something compiles does NOT mean that the program will produce the expected results icon_cool.gif

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
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 194
Location: Toronto, ON, Canada

PostPosted: Wed Jul 04, 2018 6:19 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2035
Location: UK

PostPosted: Wed Jul 04, 2018 8:09 pm    Post subject: Reply to: SQL IN predicate using COBOL table
Reply with quote

Maybe I will throw that book away! It only goes up to V5.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 667
Location: Whitby, ON, Canada

PostPosted: Wed Jul 04, 2018 9:49 pm    Post subject: Re: Reply to: SQL IN predicate using COBOL table
Reply with quote

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
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Jul 04, 2018 9:58 pm    Post subject:
Reply with quote

@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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1942
Location: NY,USA

PostPosted: Thu Jul 05, 2018 10:27 am    Post subject:
Reply with quote

Jerryte, This is very basic stuff that you could have tried , what was the hardest part that made you to post here? If it works it works else it won’t work and why afraid of using dynamic queries ?
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_inpredicate.html
Such questions usually belongs to beginners forum here
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 194
Location: Toronto, ON, Canada

PostPosted: Thu Jul 05, 2018 8:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1942
Location: NY,USA

PostPosted: Thu Jul 05, 2018 11:53 pm    Post subject:
Reply with quote

okay, I think much more effective way to handle in such situations is to make a use of GTT.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Converting SAS code to Cobol or Easyt... Ashish.Raghav All Other Mainframe Topics 6 Fri Jun 29, 2018 6:01 pm
No new posts Can we use a file both as input and o... Chiku kumar COBOL Programming 11 Thu Jun 28, 2018 3:30 pm
No new posts Varying List Select SQL Query In DB2 ... NikhilGuptaGaya DB2 5 Wed Jun 27, 2018 11:01 pm
This topic is locked: you cannot edit posts or make replies. Need help - DB2 Dynamic SQL Program e... NikhilGuptaGaya DB2 6 Sat Jun 23, 2018 1:58 pm
This topic is locked: you cannot edit posts or make replies. Issue in suppressing leading zeros &a... Vijay_Sirisha COBOL Programming 21 Thu Jun 07, 2018 8:01 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us