View previous topic :: View next topic
|
Author |
Message |
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
Hello all,
I need to declare a variable CDCONF char(3) which occurs 3 times, in DCLGEN and also create an SQL code for reading the value of this variable from the DB2 table. The table is a new one and will be created during prod move by client. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
I would very much like to see the SQL for defining this column. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
in DCLGEN ????
create an SQL code for reading the value of this variable ????
pls explain ... |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
To explain more.
My client is making a new table. He has asked me to create a pgm where this table is to be read using SELECT stmt.
In the data defn of the different columns of the new table, I have the variable or column name CDCONF which occurs 3 times.
Wat shud I do? Im kinda really stuck on this.. |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
"WITH ROWSET POSITIONING" feature of DB2 V8.1 and above can help you.
Please see the below link and let us know if you wanted anything different than this. |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
I just wanted to know if your database is DB2 for sure? The way you explained the field reminds me of ADABAS. |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
Yes. Its DB2 for sure. |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
I'm not sure that its the ROWSET stmt... coz in the pgm Im reading all columns of the table into a copybook.. |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
Back to sqare one. Answer the Craq's question. |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
I dont have the SQL..if that's wat u meant by Craq'q question.
I need to create it! |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
can You please show the relevant create table statements
( at least for the three column involved )
I guess it has been defined as column char(9)
in this case the host variable were the column will be fetched
might be redefined as an array
( a supposition, I do not speak cobol, can be certainly be done in PL/1 )
the select could use the substring operand
the COBOL the reference modifier |
|
Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007 Posts: 691 Location: Earth
|
|
|
|
As enrico suggested we need TABLE creation SQL (At least).
As we understand you need to create SQL query to fetch data from that table. |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
The table is not yet created.. It'll be done by the client later. So, I dont have the create table stmt.
But even if it is the case as u said (char 9), how will the sql stmt b like?
EXEC SQL SELECT
CDCONF(1:3),
CDCONF(3:6),
CDCONF(6:9)
INTO
,
:y,
:z
FROM ..etc
Is this a correct query? |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
oops.. sorry for the emoticons.. tht was a typo |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
I think Ive found a probable somn on internet- host variable array.
Do u think it is the soln? Does anyone have any info regarding using host variable arrays? |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
Code: |
EXEC SQL SELECT
CDCONF(1:3),
CDCONF(3:6),
CDCONF(6:9).... |
right from a syntax pint of view but...
why waste resources? having db2 make three moves,
when a
Code: |
... SELECT CDCONF INTO .... |
and redefining Your variable as three variables or as an array
or using reference modification
is better |
|
Back to top |
|
|
Manuneedhi K
Active User
Joined: 07 May 2008 Posts: 115 Location: Chennai
|
|
|
|
EXEC SQL SELECT
CDCONF(1:3),
CDCONF(3:6),
CDCONF(6:9)
INTO
,
:y,
:z
Are you referrring to splitting the column CDCONF (using substring function) into three different host variables? The above sql looks like it. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
I've joined the party somewhat late, but i suspect it will be futile to try to write the SELECT code before the table definition is known. . .
Code: |
SELECT WhatIWant FROM WhereverItIs WHERE SomeConditon |
will not resolve to something useful. . .
Maybe i misunderstand, but if i do understand, coding needs to wait until the table definition is resolved. |
|
Back to top |
|
|
Cecil
New User
Joined: 23 Oct 2007 Posts: 9 Location: chennai
|
|
|
|
enrico-sorichetti,
Could u explain a bit more ur soln?I mean how shud the query code look like? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
CECIL,
you should defne your table, and each of these 3 variables should be a separate column.
rowsets is used for multiple rows, not multiple columns.
if your user is a complete idiot and tries to jam 3 numeric fields into one column, you should tell him that is not the way to go. he should have a column per field. because of he does not, there is no way to update the '2nd' field in the column without selecting, parsing, modifiying, re-bundling and then updating the column. (or the 1st or 2nd).
everytime somebody uses a char field to hold numerics or combines fields into an char or refuses to use date/time db2 datatypes, just makes the system harder to deal with. db2 is not vsam. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello Cecil,
Quote: |
I mean how shud the query code look like? |
Please consider - how can anyone tell you the code until you can tell us the table?
As DBZ mentions, the approach being considered (recommended?) by whoever at your organization is a bad choice. One of the main rules of db2 / sql / relatonal / etc is that there will be no repeating groups. Give each "number" a column and proveed from there. |
|
Back to top |
|
|
|