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: 378
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: 1825
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: 1281
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 QUESTION: RETURN CHANNEL pahiker CA Products 21 Thu Apr 20, 2017 12:31 am
No new posts The Last Two Question For You To Ask ... CaptBill Mainframe Interview Questions 0 Fri Apr 14, 2017 5:42 am
No new posts Asynchronous CICS Processing Question moezbud CICS 1 Mon Mar 06, 2017 6:13 pm
No new posts CICS Question RE: Browse, Readnext an... moezbud CICS 7 Sun Mar 05, 2017 5:45 am
No new posts Fault Analyzer - listings question. egrove IBM Tools 4 Thu Aug 11, 2016 5:31 pm


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