IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Question on Static SQL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 446
Location: USA

PostPosted: Fri Jul 03, 2015 2:38 am
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
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: 2455
Location: Hampshire, UK

PostPosted: Fri Jul 03, 2015 2:16 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Question for file manager IBM Tools 7
No new posts question for Pedro TSO/ISPF 2
No new posts question on Outrec and sort #Digvijay DFSORT/ICETOOL 20
No new posts panel creation question TSO/ISPF 12
No new posts Sort w/OUTREC Question DFSORT/ICETOOL 2
Search our Forums:

Back to Top