Re: help with tables
- From: "Frank Swarbrick" <Frank.Swarbrick@xxxxxxxxxxxxxx>
- Date: Thu, 31 Jan 2008 11:56:10 -0700
<jof2q31shvbu6d9ia1i7gelptabq9slahe@xxxxxxx>, Robert<no@xxxxxx> wrote:On 1/30/2008 at 8:25 PM, in message
On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick"
<Frank.Swarbrick@xxxxxxxxxxxxxx>
wrote:
--------------------------------------------------------------------
SQL0060W The "COBOL" precompiler is in progress.
19 SQL0008N The token "occurs" found in a host variable
declaration is not valid.
41 SQL4943W The number of host variables in the INTO clause
is not the same as the number of items in the SELECT
clause. SQLSTATE=01503
The manual says DB2 doesn't allow a simple SELECT to return multiple
rows. You must do the
SELECT in a cursor declaration and read the table with a single FETCH,
as illustrated by
Frederico.
Thanks for the experiment.
Still no love. DB2 does not like the OCCURS. Here's my latest test:
IDENTIFICATION DIVISION.
PROGRAM-ID. tabtest.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
CONSOLE IS CONSOLE
.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry occurs 1 to 100 depending on sqlerrd(3).
* 05 table2-entry.
10 t2-name pic x(30).
10 t2-dept pic x(04).
EXEC SQL
END DECLARE SECTION
END-EXEC
77 ERRLOC PIC X(80) VALUE SPACES.
exec sql
declare t2_curs cursor for
select name, dept
from test.table2
where name in (select distinct name from test.table1)
end-exec
PROCEDURE DIVISION.
CONTINUE.
MAIN.
CALL 'connect'
perform open-t2-curs
PERFORM get-into-table
perform close-t2-curs
PERFORM DB-DISCONNECT
STOP RUN.
get-into-table.
compute sqlerrd(3) = length of table2-area
/ length of table2-entry
exec sql
fetch t2_curs
into :table2-entry
end-exec
CALL 'checkerr' USING SQLCA ERRLOC
display 'There are ' sqlerrd(3) ' departments.'
exit.
open-t2-curs.
exec sql
open t2_curs
end-exec
exit.
close-t2-curs.
exec sql
close t2_curs
end-exec
exit.
DB-DISCONNECT.
MOVE 'DB-DISCONNECT' TO ERRLOC
EXEC SQL
CONNECT RESET
END-EXEC
CALL 'checkerr' USING SQLCA ERRLOC
EXIT.
END PROGRAM tabtest.
And the results:
DB20000I The SQL command completed successfully.
prep tabtest.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN
yes EXPLSNAP yes
LINE MESSAGES FOR tabtest.sqb
------
--------------------------------------------------------------------
SQL0060W The "COBOL" precompiler is in progress.
19 SQL0008N The token "occurs" found in a host variable
declaration is not valid.
51 SQL0306N The host variable "TABLE2-ENTRY" is undefined.
SQL0095N No bind file was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "3"
errors and "0" warnings.
I also tried this and got the same error
05 table2-entry occurs 100.
Now of course you can remove the occurs, but then you're stuck with fetching
one row at a time.
I see no way to do a multi row (at once) fetch using DB2/LUW.
More thoughts would be welcome.
Frank
.
- References:
- Re: help with tables
- From: Frank Swarbrick
- Re: help with tables
- From: Robert
- Re: help with tables
- Prev by Date: Re: help with tables
- Next by Date: Re: compile+link Fujitsu Linux
- Previous by thread: Re: help with tables
- Next by thread: Mainframe opportunities in Tampa!!!
- Index(es):
Relevant Pages
|
