help with tables
in [Cobol]
|
Prev: oracle10g SQLBEX giving 114 with Oracle Dynamic SQL Method 4(ora9 works well)
Next: compile+link Fujitsu Linux
From: Anonymous on 29 Jan 2008 14:05 In article <c7sup39sjmh3d0n70m26o05ql6np5igq5r(a)4ax.com>, Robert <no(a)e.mail> wrote: >On Tue, 29 Jan 2008 14:51:50 +0000 (UTC), docdwarf(a)panix.com () wrote: > >>In article <m6bup35nfg7lbr4fmtpk0hoa08j055hu9h(a)4ax.com>, >>Robert <no(a)e.mail> wrote: >>>On Tue, 29 Jan 2008 08:11:32 +0000 (UTC), docdwarf(a)panix.com () wrote: [snip] >>>>Things may have changed since I last looked, Mr Wagner - I've heard tell >>>>that GOBACK is more than an IBM extension! - but I believe that 'bulk >>>>collect' is PL/SQL, not ANSI. >>> >>>That's a widely held mistaken belief, especially in Cobol circles. Bulk >>>collect can be >>>used with SQL embedded in Cobol, C or Java. It is not limited to PL/SQL. >> >>(note - '-marks used to indicate a direct text quotations) >> >>A programmer might read what was written carefully, Mr Wagner; I did not >>say that 'bulk collect' was limited to PL/SQL, simply that it 'is PL/SQL, >>not ANSI.' ('ANSI' is being used as a shorthand for 'ANSI SQL') [snip] >You are putting words in my keyboard. I did not say anything about the >ANSI standarxd, I >said bulk collect is available in Cobol, it is not a PL/SQL feature. According to http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html 'bulk collect' most certainly *is* available in PL/SQL: --begin quoted text: BULK COLLECT and FORALL are very helpful, not only in improving performance but also in simplifying the code you need to write for SQL operations in PL/SQL. --end quoted text .... but what would Oracle know about the features of PL/SQL, anyhow? > >The ANSI standard that WOULD apply is SQL/CLI (Call Language Interface), >which is part of >the 2003 SQL standard package. Whatever it says (or not) is irrelevant, >because none of >the databases follow it. There was no pot, it was broken when I borrowed it and I returned it in perfect condition. > >>>The syntax is specific to Oracle. DB2 calls it ARRAY_AGG. >> >>DB2 may call it 'GIOACHINO_ROSSINI', Mr Wagner... and that might not be >>ANSI standard, as well. >> >>(My apologies if I was being obscure; what I attempted to point out was >>that in response to a generic request (for homework-doing, or so some >>might think) your response relied on Oracle syntax.) > >My point is, many databases allow a program to read multiple rows with a >single SELECT. >They each use different syntax because it was not in the ANSI SQL >standard they support. Since you already know, Mr Wagner, that there are many different syntaces for the many different databases you might know, as well, to wait until you learn which database is being used before offering an answer which uses a feature found in PL/SQL. DD
From: Robert on 29 Jan 2008 23:37 On Tue, 29 Jan 2008 19:05:40 +0000 (UTC), docdwarf(a)panix.com () wrote: >In article <c7sup39sjmh3d0n70m26o05ql6np5igq5r(a)4ax.com>, >Robert <no(a)e.mail> wrote: >>On Tue, 29 Jan 2008 14:51:50 +0000 (UTC), docdwarf(a)panix.com () wrote: >> >>>In article <m6bup35nfg7lbr4fmtpk0hoa08j055hu9h(a)4ax.com>, >>>Robert <no(a)e.mail> wrote: >>>>On Tue, 29 Jan 2008 08:11:32 +0000 (UTC), docdwarf(a)panix.com () wrote: > >[snip] > >>>>>Things may have changed since I last looked, Mr Wagner - I've heard tell >>>>>that GOBACK is more than an IBM extension! - but I believe that 'bulk >>>>>collect' is PL/SQL, not ANSI. >>>> >>>>That's a widely held mistaken belief, especially in Cobol circles. Bulk >>>>collect can be >>>>used with SQL embedded in Cobol, C or Java. It is not limited to PL/SQL. >>> >>>(note - '-marks used to indicate a direct text quotations) >>> >>>A programmer might read what was written carefully, Mr Wagner; I did not >>>say that 'bulk collect' was limited to PL/SQL, simply that it 'is PL/SQL, >>>not ANSI.' ('ANSI' is being used as a shorthand for 'ANSI SQL') > >[snip] > >>You are putting words in my keyboard. I did not say anything about the >>ANSI standarxd, I >>said bulk collect is available in Cobol, it is not a PL/SQL feature. > >According to >http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html >'bulk collect' most certainly *is* available in PL/SQL: > >--begin quoted text: > >BULK COLLECT and FORALL are very helpful, not only in improving >performance but also in simplifying the code you need to write for SQL >operations in PL/SQL. > >--end quoted text > >... but what would Oracle know about the features of PL/SQL, anyhow? If you don't like BULK COLLECT syntax, leave it out. Selecting into an array does the same thing and isn't tied to Oracle. It works on DB2 the same way. For example: 01 table2-area. 05 table2-entry occurs 1 to 100 depending on sqlerrd (3). 10 t2-name pic x(30). 10 t2-dept pic x(04). compute sqlerrd(3) = length of table2-area / length of table2-entry select name, department into table2-entry from table2 where name in (select distinct name from table1) display 'There are ' sqlerrd(3) ' departments.' >>The ANSI standard that WOULD apply is SQL/CLI (Call Language Interface), >>which is part of >>the 2003 SQL standard package. Whatever it says (or not) is irrelevant, >>because none of >>the databases follow it. > >There was no pot, it was broken when I borrowed it and I returned it in >perfect condition. SQL is like Cobol in having a 'current' standard that has not been fully implemented. There are four major ANSI SQL standards: 1986, 1992, 1999 and 2003. Only 2003 deals with embedded SQL and host variables, the issue with SELECT .. INTO. Since all four precompilers were written before 2003, three of them by database companies, they made up their own rules, consistant with the capabilities (and libraries) of their own databases. MF's OpenESQL is the only one that supports multiple databases. This page compares five major databases to the 2003 standard. There is widespread imcompatibility, even with basic things like DATE and TIMESTAMP. I was surprised to see PostgreSQL comes closest. http://troels.arvin.dk/db/rdbms/ http://www.analysisandsolutions.com/code/dates.htm >>>>The syntax is specific to Oracle. DB2 calls it ARRAY_AGG. >>> >>>DB2 may call it 'GIOACHINO_ROSSINI', Mr Wagner... and that might not be >>>ANSI standard, as well. >>> >>>(My apologies if I was being obscure; what I attempted to point out was >>>that in response to a generic request (for homework-doing, or so some >>>might think) your response relied on Oracle syntax.) >> >>My point is, many databases allow a program to read multiple rows with a >>single SELECT. >>They each use different syntax because it was not in the ANSI SQL >>standard they support. > >Since you already know, Mr Wagner, that there are many different syntaces >for the many different databases you might know, as well, to wait until >you learn which database is being used before offering an answer which >uses a feature found in PL/SQL. I was prompting the student to think of alternative approaches. It took some restraint to omit a hash join and a tree structure. I forgot to show him the MAINFRAME WAY: 3100-SEARCH-TABLE2. D DISPLAY 'IN 3100-SEARCH-TABLE2'. MOVE WS-CONST-N TO WS-IND1. PERFORM 5600-LOOK-IN-TABLE2 THRU 5699-LOOK-IN-TABLE2-EXIT VARYNG WS-COMP-SUB2 FROM WS-CONST-1 BY WS-CONST-1 UNTIL WS-IND1 = WS-CONST-Y OR WS-COMP-SUB2 > WS-COMP-TABLE2-SIZE END-PERFORM. IF WS-IND1 = WS-CONST-N ADD WS- CONST-1 TO WS-COMP-TABLE2-SIZE ON SIZE ERROR DISPLAY '3100-SEARCH-TABLE2 SOMETHING BAD HAPPENED' PERFORM 9999-ABEND THRU 9999-ABEND-EXIT END-ADD MOVE WS-TABLE1-NAME (WS-COMP-SUB1) TO WS-TABLE2-NAME (WS-COMP-TABLE2-SIZE) PERFORM 3200-FIND-DEPARTMENT THRU 3299-FIND-DEPARTMENT-EXIT ELSE CONTINUE END-IF. D DISPLAY 'EXITING 3100-SEARCH-TABLE2'. 3100-SEARCH-TABLE2-EXIT. EXIT.
From: Anonymous on 31 Jan 2008 05:39 In article <15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, Robert <no(a)e.mail> wrote: >On Tue, 29 Jan 2008 19:05:40 +0000 (UTC), docdwarf(a)panix.com () wrote: > >>In article <c7sup39sjmh3d0n70m26o05ql6np5igq5r(a)4ax.com>, >>Robert <no(a)e.mail> wrote: >>>On Tue, 29 Jan 2008 14:51:50 +0000 (UTC), docdwarf(a)panix.com () wrote: >>> >>>>In article <m6bup35nfg7lbr4fmtpk0hoa08j055hu9h(a)4ax.com>, >>>>Robert <no(a)e.mail> wrote: >>>>>On Tue, 29 Jan 2008 08:11:32 +0000 (UTC), docdwarf(a)panix.com () wrote: >> >>[snip] >> >>>>>>Things may have changed since I last looked, Mr Wagner - I've heard tell >>>>>>that GOBACK is more than an IBM extension! - but I believe that 'bulk >>>>>>collect' is PL/SQL, not ANSI. >>>>> >>>>>That's a widely held mistaken belief, especially in Cobol circles. Bulk >>>>>collect can be >>>>>used with SQL embedded in Cobol, C or Java. It is not limited to PL/SQL. >>>> >>>>(note - '-marks used to indicate a direct text quotations) >>>> >>>>A programmer might read what was written carefully, Mr Wagner; I did not >>>>say that 'bulk collect' was limited to PL/SQL, simply that it 'is PL/SQL, >>>>not ANSI.' ('ANSI' is being used as a shorthand for 'ANSI SQL') >> >>[snip] >> >>>You are putting words in my keyboard. I did not say anything about the >>>ANSI standarxd, I >>>said bulk collect is available in Cobol, it is not a PL/SQL feature. >> >>According to >>http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html >>'bulk collect' most certainly *is* available in PL/SQL: >> >>--begin quoted text: >> >>BULK COLLECT and FORALL are very helpful, not only in improving >>performance but also in simplifying the code you need to write for SQL >>operations in PL/SQL. >> >>--end quoted text >> >>... but what would Oracle know about the features of PL/SQL, anyhow? > >If you don't like BULK COLLECT syntax, leave it out. It may be less a matter of what *I* like, Mr Wagner, and more a matter of what my client has asked me to do in exchange for my wages. >Selecting into an >array does the >same thing and isn't tied to Oracle. This seems to be a kind of admission that your first example was PL/SQL, as was pointed out a while back and specifically denied by you in what you quoted ('... bulk collect ... it is not a PL/SQL feature.') [snip] >I forgot to show him the MAINFRAME WAY: > > 3100-SEARCH-TABLE2. >D DISPLAY 'IN 3100-SEARCH-TABLE2'. > MOVE WS-CONST-N TO WS-IND1. > PERFORM 5600-LOOK-IN-TABLE2 THRU 5699-LOOK-IN-TABLE2-EXIT > VARYNG WS-COMP-SUB2 FROM WS-CONST-1 > BY WS-CONST-1 > UNTIL WS-IND1 = WS-CONST-Y > OR WS-COMP-SUB2 > WS-COMP-TABLE2-SIZE > END-PERFORM. That's similar to a way I have seen things done on mainframes, yes... but a way that would not pass muster in more than a few shops where I've worked and would have gotten you laughed out of Prod Implementation reviews. (if 3100- begins in column 8 (as indicated by the debugging line following it) then the imperatives which follow precede column 12) DD
From: Frank Swarbrick on 31 Jan 2008 12:39 >>> On 1/29/2008 at 9:37 PM, in message <15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, Robert<no(a)e.mail> wrote: > If you don't like BULK COLLECT syntax, leave it out. Selecting into an > array does the > same thing and isn't tied to Oracle. It works on DB2 the same way. For > example: > > 01 table2-area. > 05 table2-entry occurs 1 to 100 depending on sqlerrd (3). > 10 t2-name pic x(30). > 10 t2-dept pic x(04). > > compute sqlerrd(3) = length of table2-area / length of table2-entry > select name, department into table2-entry from table2 > where name in (select distinct name from table1) > display 'There are ' sqlerrd(3) ' departments.' This does not appear to work, at least not using the DB2 pre-processor. Here's my test program: IDENTIFICATION DIVISION. PROGRAM-ID. tabtest. ENVIRONMENT DIVISION. 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). 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. PROCEDURE DIVISION. CALL 'connect' PERFORM get-into-table. PERFORM DB-DISCONNECT STOP RUN. get-into-table. compute sqlerrd(3) = length of table2-area / length of table2-entry exec sql select name, dept into :table2-entry from test.table2 where name in (select distinct name from test.table1) end-exec CALL 'checkerr' USING SQLCA ERRLOC display 'There are ' sqlerrd(3) ' departments.' exit. DB-DISCONNECT. MOVE 'DB-DISCONNECT' TO ERRLOC EXEC SQL CONNECT RESET END-EXEC CALL 'checkerr' USING SQLCA ERRLOC EXIT. END PROGRAM tabtest. And here's the output from the DB2 pre-processor: Database Connection Information Database server = DB2/NT 9.5.0 SQL authorization ID = FSWARBRI Local database alias = TEST1 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. 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 41 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 "1" warnings. Frank
From: Anonymous on 31 Jan 2008 13:42
In article <47A053E0.6F0F.0085.0(a)efirstbank.com>, Frank Swarbrick <Frank.Swarbrick(a)efirstbank.com> wrote: >>>> On 1/29/2008 at 9:37 PM, in message ><15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, Robert<no(a)e.mail> wrote: >> If you don't like BULK COLLECT syntax, leave it out. Selecting into an >> array does the >> same thing and isn't tied to Oracle. It works on DB2 the same way. For >> example: >> >> 01 table2-area. >> 05 table2-entry occurs 1 to 100 depending on sqlerrd (3). >> 10 t2-name pic x(30). >> 10 t2-dept pic x(04). >> >> compute sqlerrd(3) = length of table2-area / length of table2-entry >> select name, department into table2-entry from table2 >> where name in (select distinct name from table1) >> display 'There are ' sqlerrd(3) ' departments.' > >This does not appear to work, at least not using the DB2 pre-processor. Oh, these silly 'professionals' and their demands that things do stuff like... *work*. I seem to recall that in DB2 there's a specific SQLcode that comes back if more than one row in a table satisfies the criteria of a given SELECT. Some folks would get rather upset when they saw this... I've seen it turn a fellow from a genial, clean-talking guy into quite the curse-er. DD |