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
 

 

How to give a COBOL array as an input to a select statement?

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

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Fri Apr 02, 2010 10:25 pm    Post subject: How to give a COBOL array as an input to a select statement?
Reply with quote

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
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri Apr 02, 2010 10:53 pm    Post subject:
Reply with quote

That is a question asked several times in the last week, and even after going through several of the DB2 manuals, I have yet to figure it out.
Please try these links to other threads and see if you can make any sense of it.
Mass Insert in Db2?
Still Facing problem with varchar col in multi-row insert
Facing problem with varchar col in multi-row insert
Multi row insert
By the way, I think I got that you will have to tell DB2 how many rows you want selected.....
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Fri Apr 02, 2010 11:05 pm    Post subject: Reply to: How to give a COBOL array as an input to a select
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Apr 02, 2010 11:08 pm    Post subject:
Reply with quote

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
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri Apr 02, 2010 11:19 pm    Post subject: Re: Reply to: How to give a COBOL array as an input to a sel
Reply with quote

vimalravi83 wrote:
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.
After another look an the manual, maybe it will not work for a SELECT....
A host-variable-array can only be referenced in a FETCH statement for a multiple row fetch, in an INSERT statement with a multiple row insert, or in a multiple row MERGE statement.
Back to top
View user's profile Send private message
Bill O'Boyle

CICS Moderator


Joined: 14 Jan 2008
Posts: 2502
Location: Atlanta, Georgia, USA

PostPosted: Fri Apr 02, 2010 11:47 pm    Post subject: Reply to: How to give a COBOL array as an input to a select
Reply with quote

CICS Guy,

Wasn't your previous location at your desk icon_question.gif

Did you rearrange the furniture? icon_biggrin.gif

Coffee table seems to be a bit uncomfortable.... icon_wink.gif

Bill
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sat Apr 03, 2010 12:08 am    Post subject: Re: Reply to: How to give a COBOL array as an input to a sel
Reply with quote

Bill O'Boyle wrote:
CICS Guy,
Wasn't your previous location at your desk icon_question.gif
Yes.
Quote:
Did you rearrange the furniture? icon_biggrin.gif
No.
Quote:
Coffee table seems to be a bit uncomfortable.... icon_wink.gif
No, not with me lounging on my couch watching an episode of Supernatural between entries to the forum....grin.....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Apr 03, 2010 1:15 am    Post subject:
Reply with quote

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
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Sat Apr 03, 2010 10:20 am    Post subject: Reply to: How to give a COBOL array as an input to a select
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Apr 03, 2010 6:38 pm    Post subject:
Reply with quote

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:
  1. cursor
  2. 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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Apr 03, 2010 6:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Sat Apr 03, 2010 6:50 pm    Post subject:
Reply with quote

Dick,

if he will tell that in your words, he will never finish this class.

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

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Sat Apr 03, 2010 8:55 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Apr 03, 2010 9:38 pm    Post subject:
Reply with quote

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 icon_smile.gif
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm


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