Re: help with tables

On 1/30/2008 at 8:25 PM, in message
<med2q3hfb6vi8gbtvf436o07sr4k1jg0n5@xxxxxxx>, Robert<no@xxxxxx> wrote:
On Wed, 30 Jan 2008 21:55:18 +0000, Frederico Fonseca
<real-email-in-msg-spam@xxxxxxxxx>
wrote:

The following is allowed on V9, but on Z/OS.

WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry.
10 t2-name pic x(30) occurs 100.
10 t2-dept pic x(04) occurs 100.

Was that necessary? DB2, like Oracle, expands a Cobol group name into a
list of host
variables by using the next lower level, which should be elementary. In
other words:

05 table2-entry occurs 100.
10 t2-name pic x(30).
10 t2-dept pic x(04).

into :table2-entry
is expanded to
into :t2-name, :t2-dept

I don't know whether DB2 is smart enough to treat :t2-name as an array.
Oracle is.
If not, would :table2-entry.t2-name work?

EXEC SQL
END DECLARE SECTION
END-EXEC
EXEC SQL
DECLARE CURSOR-X CURSOR FOR
select name, dept
from test.table2
where name in (select distinct name from test.table1)
END-EXEC.

A cursor need not be declared in the data division, only before the
first reference to it.
I prefer to put the declaration just above the fetch, with the open and
close further
down.

I prefer cursors to be in the data division, because they are declarations
and not 'action' statements. By putting them in the procedure division one
who doesn't know any better (say, me a year ago) could think that the
declaration is actually doing something.

Now I could conceivable see it immediately *above* the paragraph.

Funny thing is that in VSE you *have* to put it in the procedure division.
So I've taken to putting all of my DECLARE CURSOR statements immediately
following the PROCEDURE DIVISION line. Bugs me, but hey...

Not that I don't see your point, that it could be nice to have the cursor
declaration near the FETCH. I'm just concerned about the confusion it may
cause to less knowledgeable programmers.

......
PROCEDURE....
exec sql
FETCH NEXT CURSOR-X
into :T2-NAME, :T2-DEPT
for 100 rows
end-exec

It seems that Windows/Unix versions do not allow multiple fetch/insert
rows. Pitty.

Hard to believe. I think the non-singleton select caused the error.

You're welcome to try it yourself, or at least provide an example that you
think would work and I could try it.

Frank

.

Relevant Pages

  • Re: Opinions on approach, please...
    ... It means the first column in the result set. ... EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. ...
    (comp.lang.cobol)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)
  • Re: Replace Cursor Procedure with Update Query?
    ... DECLARE @tmpResults TABLE, Create_Date Int, Seq int) ... Can I replace the below cursor based procedure with an update> query? ... Every morning, after the update,> I run the procedure below to update the Sequence field. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Procedure Optimisation
    ... Any reason why you did not mention what DBMS product? ... I'm using a cursor to perform updates based on an ID. ... DECLARE @Site_ID NVARCHAR ... due to your EAV design of the source ...
    (comp.databases)