|
View previous topic :: View next topic
|
| Author |
Message |
vimalravi83
New User
Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL
|
|
|
|
Hello,
I have one requirement something like below in COBOL;
| Code: |
01 WS-ROLL.
05 WS-ROLL-ID-ARR OCCURS 500 times PIC X(10).
.
.
.
SELECT COUNT(*) from STUDENT
INTO :WS-COUNT
WHERE ROLL_NUM IN (:WS-ROLL-ID-ARR) |
where the array values I will be populating using a PERFORM loop before the SELECT statement. ROLL_NUM in table is of CHAR(10). I tried above, but didn't work, saying that host variable is unusable. Anyone knows how to do this? |
|
| Back to top |
|
 |
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
vimalravi83
New User
Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL
|
|
|
|
Yes. I agree that. Multi row INSERT is possible.
But here the array I am giving as input to IN clause of SELECT statement, not a array of values I want to SELECT from a table or to INSERT. It is a condition. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
One way to get what you want is to load this array into a temp table and then join the temp table to the real table.
| Quote: |
| It is a condition. |
I don't know what this means. . . |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
Bill O'Boyle
CICS Moderator
Joined: 14 Jan 2008
Posts: 2501
Location: Atlanta, Georgia, USA
|
|
|
|
CICS Guy,
Wasn't your previous location at your desk 
Did you rearrange the furniture? 
Coffee table seems to be a bit uncomfortable.... 
Bill |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| Bill O'Boyle wrote: |
CICS Guy,
Wasn't your previous location at your desk |
Yes.
| Quote: |
| Did you rearrange the furniture? |
No.
| Quote: |
| Coffee table seems to be a bit uncomfortable.... |
No, not with me lounging on my couch watching an episode of Supernatural between entries to the forum....grin..... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
the array is not an object of the select.
it is part of the WHERE CLAUSE
the term is IN-LIST
and you can have working-storage IN-LISTs
your syntax was incorrect, as you need to reference the group variable
as in
WHERE ROLL_NUM IN (:WS-ROLL)
other things come to mind, do you have 500 values with which to populate the Array?
insure that the values are sorted.
I would populate the unused items with high-values, hoping of course, high-values is not a valid value for the column. |
|
| Back to top |
|
|
vimalravi83
New User
Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL
|
|
|
|
Hi DBzthedinosauer,
I tried with WS-ROLL also, but the same error;
UNUSABLE HOST variable.
I am not worried about the values in the array. It can be anything. The problem is my code itself is not compiling if I give an array like that. I strongly believe that we can't give an array as a condition in SELECT statement. I think as Dick said, it will be better I get the help of temp table and get it done by joining. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
probably you need to define your working-storage as follows:
| Code: |
01 W-S-IN-LIST.
05 ITEM-001 PIC X(10).
...
05 ITEM-500 PIC X(10).
01 W-S-ARRAY
REDEFINES
W-S-IN-LIST.
05 WS-ROLL-ID-ARR OCCURS 500 times PIC X(10).
|
and use W-S-IN-LIST in
WHERE ROLL_NUM IN (:W-S-IN-LIST)
why, probably because the DB2 Working-Storage IN LIST SQL build routine does not calculate based on occurs,
but I do know that the W-S-IN-LIST definition will past muster by DB2,
unless you are running DB2 version 6 or below.
| Quote: |
| I am not worried about the values in the array. |
you are starting to display your ignorance of computers,
and lack of respect for suggestions.
You should always consider the integrity of your data values,
otherwise you will receive 'unexpected results'.
and again, i suspect this to be a class room exercise - see another class exercise by TS
and as such,
I would suggest you keep your posts limited to - questions
- observations of results
and not guesses.
| Quote: |
| I strongly believe that we can't give an array |
I already said you can not reference a host-variable with item REFERENCE,
because DB2 has no way to resolve the indicies.
you always have to reference the group stucture.
Mult-row processing can handle the occurs definition,
but as I said before, I don't think the IN LIST will accept occurs.
But by subdefining your IN LIST group item as independent elementary items,
DB2 can calculate the size of the group item
| Quote: |
| as a condition in SELECT statement |
you don't have a condition in a SELECT statement,
you have a condition in a WHERE clause.
if I was your DBA, I would tell you to select the ROLL_NUM by either:- cursor
- multi-row SELECT into an ARRAY (if your version of DB2 supports this)
and do a binary search thru your SORTED COBOL internal table.
Why, because even though DB2 could accomplish this task,
(a large IN LIST should be processed via QMF)
the amount of resouces required by DB2
to scan the IN LIST for each value of ROLL_NUM contained in your table
(how many rows, by the way)
for a COUNT is a waste of DB2 resources and should be done by the program. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| and you can tell your class instructor that an exercise that involves an large IN LIST is really stupid and useless in the real world. |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
Dick,
if he will tell that in your words, he will never finish this class.
Cheers. |
|
| Back to top |
|
|
vimalravi83
New User
Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL
|
|
|
|
Hi dbzTHEdinosauer,
First of all, if my words were wrong, I am sorry for that. With all respect I am taking your valuable suggestions. These are not class room exercises. To make it simple, I am putting all my requirements in terms of STUDENT or EMPLOYEE examples .
| Quote: |
| you are starting to display your ignorance of computers, |
I have PERFORM loops to initialize the array first and start moving proper values into that.
I will try REDEFINES as you said.
Thanks.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| To make it simple, I am putting all my requirements in terms of STUDENT or EMPLOYEE examples . |
Yup, It is often helpful to use examples that most people can relate to easily.
Good luck  |
|
| Back to top |
|
|
|
|
