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: Robert on 28 Jan 2008 23:20 On Mon, 28 Jan 2008 16:34:21 -0800 (PST), canela <vferr094(a)alumni.uottawa.ca> wrote: >Hello, > >I need some help with tables. I will use an example to help explain >what I am trying to do. > >I have two tables: Table 1 and Table 2 > >Table 1 has a lot of rows returned from the database, let's say I have > > ws-results1-table1 > ws-results1-name > ws-results1-duty > ws-results1-date-finished > . > . > . > >Now, let's say Jane is a person that has many rows in table1 because >she has performed many duties over times which started at different >times, and finished at different times. > >I want to grab each unique name from table1, so say I want Jane. >With this key, Jane, I want to go off to the database query another >table, let's say to get her departnment >so let's say in table2 i have > > ws-results2-table2 > ws-results2-name > ws-department > . > . > . > my question is, while doing this in some form of loop, what is the >best way in cobol to perform this so that let's say, Jane's name comes >up in table1 500 times, but not only as previous. So let's say Jane >came up in index 1 of table1, again in row 300 and row 455, while i >loop through the first table, what is the best way to know it is >unique to store it in table2 ? > >I was thinking of moving the first value right away, since it's the >first occurence but I am not sure of what the correct statement to use >to sort of say, store in table 2 as long as it does not exist ? >Comparing the previous valued stored won't be enough .. can anyone >help with this? 1. Do it in SQL select name, department bulk collect into :ws-results2-table2 from table2 where name in (select distinct name from table1) 2. Look up the name in table2 with a Cobol SEARCH verb. Add it if not found. set x2 to 1 search ws-results2-table2 at end add 1 to ws-limit2 move ws-results1-name (x1) to ws-results2-name (ws-limit2) perform get-department when ws-results1-name (x1) = ws-results2-name (x2) continue end-search 3. Sort table1 on name, create a department when name (x1) not equal to name (x1 - 1)
From: Anonymous on 29 Jan 2008 03:11 In article <pv5tp355jqp7j268uec8rj03i5n74h0vhf(a)4ax.com>, Robert <no(a)e.mail> wrote: >On Mon, 28 Jan 2008 16:34:21 -0800 (PST), canela ><vferr094(a)alumni.uottawa.ca> wrote: > >>Hello, >> >>I need some help with tables. I will use an example to help explain >>what I am trying to do. [snip] >1. Do it in SQL > > select name, department > bulk collect into :ws-results2-table2 > from table2 > where name in (select distinct name from table1) 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. DD
From: Robert on 29 Jan 2008 08:53 On Tue, 29 Jan 2008 08:11:32 +0000 (UTC), docdwarf(a)panix.com () wrote: >In article <pv5tp355jqp7j268uec8rj03i5n74h0vhf(a)4ax.com>, >Robert <no(a)e.mail> wrote: >>On Mon, 28 Jan 2008 16:34:21 -0800 (PST), canela >><vferr094(a)alumni.uottawa.ca> wrote: >> >>>Hello, >>> >>>I need some help with tables. I will use an example to help explain >>>what I am trying to do. > >[snip] > >>1. Do it in SQL >> >> select name, department >> bulk collect into :ws-results2-table2 >> from table2 >> where name in (select distinct name from table1) > >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. The syntax is specific to Oracle. DB2 calls it ARRAY_AGG.
From: Anonymous on 29 Jan 2008 09:51 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: > >>In article <pv5tp355jqp7j268uec8rj03i5n74h0vhf(a)4ax.com>, >>Robert <no(a)e.mail> wrote: >>>On Mon, 28 Jan 2008 16:34:21 -0800 (PST), canela >>><vferr094(a)alumni.uottawa.ca> wrote: >>> >>>>Hello, >>>> >>>>I need some help with tables. I will use an example to help explain >>>>what I am trying to do. >> >>[snip] >> >>>1. Do it in SQL >>> >>> select name, department >>> bulk collect into :ws-results2-table2 >>> from table2 >>> where name in (select distinct name from table1) >> >>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') Finding cites that 'bulk collect' is available in PL/SQL is a trivial web-search exercise; if you have any evidence that 'bulk collect' is a part of the ANSI standard I'd be interested in your presenting it. > >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.) DD
From: Robert on 29 Jan 2008 13:44
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: >> >>>In article <pv5tp355jqp7j268uec8rj03i5n74h0vhf(a)4ax.com>, >>>Robert <no(a)e.mail> wrote: >>>>On Mon, 28 Jan 2008 16:34:21 -0800 (PST), canela >>>><vferr094(a)alumni.uottawa.ca> wrote: >>>> >>>>>Hello, >>>>> >>>>>I need some help with tables. I will use an example to help explain >>>>>what I am trying to do. >>> >>>[snip] >>> >>>>1. Do it in SQL >>>> >>>> select name, department >>>> bulk collect into :ws-results2-table2 >>>> from table2 >>>> where name in (select distinct name from table1) >>> >>>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') > >Finding cites that 'bulk collect' is available in PL/SQL is a trivial >web-search exercise; if you have any evidence that 'bulk collect' is a >part of the ANSI standard I'd be interested in your presenting it. 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. 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. >>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. |