Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Question on Static SQL

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

New User


Joined: 10 Mar 2008
Posts: 34
Location: Pune

PostPosted: Thu Jul 02, 2015 10:57 pm    Post subject: Question on Static SQL
Reply with quote

Hello

Lets say I have a table that has account number and account name.
I get a list of account numbers separated by comma from front end and I need to return the account name through a cobol DB2 Stored Proc.

This can be accomplished through a dynamic sql that can be prepared within the Stored Procedure.

However , I am wondering if something like below would work -

SELECT ACCOUNT_NAME FROM ACCOUNT_TABLE
WHERE ACCOUNT_NUMBER IN (:INPUT-ACCOUNT-LIST) ?

Here INPUT-ACCOUNT-LIST will be a comma separated list of account numbers (VARCHAR).

Please let me know your thoughts.

Regards
Puspojit
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Jul 03, 2015 2:38 am    Post subject: Reply to: Question on Static SQL
Reply with quote

Your INPUT-ACCOUNT-LIST variable will be treated as a single string. I have seen codes where they use it like
Code:
 IN (:WS-VAR-1, :WS-VAR-2, :WS-VAR-3)

As it tells me that each variable will be treated as a single string and so is your variable INPUT-ACCOUNT-LIST. If your variable has values A,B,C ; they will be treated like 'A,B,C' as a single string.

It's not going to work this way. What you can do is, that you can make a temporary table and use Select clause as a subquery in the 'IN'

So, your code will be:
Code:
SELECT ACCOUNT_NAME FROM ACCOUNT_TABLE
WHERE ACCOUNT_NUMBER IN (Select WS-VAR from temp table)

.
Back to top
View user's profile Send private message
Puspojit

New User


Joined: 10 Mar 2008
Posts: 34
Location: Pune

PostPosted: Fri Jul 03, 2015 11:16 am    Post subject: Reply to: Question on Static SQL
Reply with quote

Okay, let me check that out. GTT and dynamic sql are the options that may be tried ...However, I am still unclear on why DB2 when it receives a list of numbers separated by comma in the host variable would treat it as a single value and not as list of numbers separated by comma...Any thoughts ?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Fri Jul 03, 2015 2:16 pm    Post subject:
Reply with quote

Because a host variable is one unit of information - it is not an array.
Back to top
View user's profile Send private message
Puspojit

New User


Joined: 10 Mar 2008
Posts: 34
Location: Pune

PostPosted: Fri Jul 03, 2015 4:46 pm    Post subject: Reply to: Question on Static SQL
Reply with quote

Thanks !
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jul 07, 2015 3:44 pm    Post subject:
Reply with quote

sometimes it is, if you define it as such :
post 57176
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 Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm
No new posts dataset copy question - REPRO or some... atulxp TSO/ISPF 2 Wed Aug 03, 2016 10:56 pm
No new posts SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm
No new posts DFSort Question krrp DFSORT/ICETOOL 9 Mon Mar 07, 2016 7:11 pm
No new posts Question: REORG on LOB tablespaces rakesh1155 DB2 1 Fri Aug 14, 2015 2:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us