View previous topic :: View next topic
|
Author |
Message |
Puspojit
New User
Joined: 10 Mar 2008 Posts: 34 Location: Pune
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Puspojit
New User
Joined: 10 Mar 2008 Posts: 34 Location: Pune
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Because a host variable is one unit of information - it is not an array. |
|
Back to top |
|
|
Puspojit
New User
Joined: 10 Mar 2008 Posts: 34 Location: Pune
|
|
|
|
Thanks ! |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
sometimes it is, if you define it as such :
post 57176 |
|
Back to top |
|
|
|