|
midrange.com
Thanks, I am now trying to test this, but it is bombing with decimal data
error when I try to open the second cursor. At this point the first
cursor has opened Ok, and placing data in fields whose naming starts W0
(zero), within a data structure.
I had created where the first cursor places the data by copying the first
cursor, intending to change only the selection criteria (types of lines),
and sequence (description text), so the various fields ought to agree,
numeric, alpha, but I will be double checking that carefully, in case I
dropped some field, which would offset what's alpha or numeric.
I figured first cursor (B1) would not need all the fields, that second
cursor uses, but was postponing shrinking the collection until I developed
rest of modification, in case more fields ended up being needed than my
initial anticipation.
The two W0 fields, via first cursor, are defined numerical, but not yet
populated with anything.
The two fields they being matched with are also numbers.
I am wondering if I should delay opening second cursor until after first
read of data into first cursor.
There's a bunch of other cursors in the program.
i define them all.
I open them all
I use them in the program
I close them all
The cursor that won't open reads:
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR SELECT
C+
OORD,SQREQ,SOFAC,IPROD,IDESC,IFII,IITYP,ICLAS,OOPNO,OWRKC,OOPDS,OTOOL,OOPER,OSTAT,SORTEM,OCDTE
C+ FROM FSO,IIM,FRT,FOD WHERE SID='SO' AND IID='IM' AND
C+ RID='RT' AND ODID IN('OD','RD') AND SPROD=IPROD AND SPROD=RPROD
C+ AND SORD=OORD AND ROPNO=OOPNO AND OSTAT IN('1','3')
C+ AND OORD=:W0ORD AND OOPNO>=:W0OPNO
C+ GROUP BY
C+
OORD,SQREQ,SOFAC,IPROD,IDESC,IFII,IITYP,ICLAS,OOPNO,OWRKC,OOPDS,OTOOL,OOPER,OSTAT,SORTEM,OCDTE
C+ ORDER BY OORD,OOPNO,OSTAT
C/END-EXEC
*
I believe the problem is with data in the line
C+ AND OORD=:W0ORD AND OOPNO>=:W0OPNO
Hi,
I'd solve it with 2 cursors. With the first cursor I'd read all status 1
and
for each row selected by this cursor I'd read the the rest of the lines
for
this status 1 line. If you want to solve it with a single SQL-Statement
you
might have a look at UNION to merge the two "cursors".
>>I also do not remember if SQL can read two different logicals of the
same
physical, which would complicate field identification.
First SQL never reads a logical file, only the physical file. Access
Paths
(either in SQL indexes or DDS described keyed logical file) are only
used to
get access to the data in the physical files. If you specify a logical
file
in an SQL Statement, the SQL statement gets rewritten based on the
physical
files, considering the where and join clauses. In an second step the
optimal
access paths is determined by the optimizer from all existing access
path
defined over the physical file. The access path finally used may or may
not
be the access paths defined in the DDS described logical file.
Also specifying a logical file in a SQL-Statement cause the query
dispatcher
to reroute the execution of the SQL-Statement to the old (classic) query
engine, i.e. it cannot profit from the advantages of the new SQL Query
engine. This rerouting may cost up to 15% performance.
But it is possible to specify the same physical file several times in
the
same SQL-statement, i.e. it can be joined by itself. To distinguish
between
these physical files, you have to use a correlation variable, i.e. you
rename the file temporarily and qualify the field names with this
correlation variable.
Mit freundlichen Gru:ssen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
-----Urspru:ngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[ mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Al Mac
Gesendet: Monday, January 22, 2007 02:31
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL Sub Sort
I am using SQL embedded in RPG/400 to generate data sequenced by
stage of
manufacturing, and sequential number of instructions how to
manufacture
it. The first text line of each stage is an overall description, and
that
first line is also "status" type code "1". They want me to
resequence
this, so each group of instructions on a mfg stage is sorted by that
status "1" description, but continue rest of text lines that come
after
each "1" to remain in that sequence. There's nothing to tell
software
that this bunch of text lines "goes with" a first description, other
than
being sequential operation #s until the next status type"1" line I do
not
know if this is doable, or how to do it. I can do ILE, I am just not
good
at it.
Data formatted
* status "1" description is of what we are doing in each stage of
manufacturing, with an operation # like 100
* then there are a bunch of text lines of other than status "1",
with
operation #s like 101 102 103 etc. that print after the status
"1"
...
we only print until hit another status "1", which might be
operation
150
* right now I am printing this in operation sequence, then when it
hits
a new status "1", doing like a control break
* Then humans physically sort my output so it is organized by the
type
of work to be done, which takes them hours. They want the
computer
to
pre-sort for them.
Here's how the code currently reads
/EXEC SQL
DECLARE C1 CURSOR FOR SELECT
SORD,SQREQ,SOFAC,IPROD,IDESC,IFII,IITYP,ICLAS,OOPNO,OWRKC,OOPDS,OTOOL,OOPER,
OSTAT,SORTEM,OCDTE
FROM FSO,IIM,FRT,FOD WHERE SID='SO' AND IID='IM' AND
RID='RT' AND ODID IN('OD','RD') AND SPROD=IPROD AND SPROD=RPROD AND
SORD=OORD AND ROPNO=OOPNO AND OSTAT IN('1','3')
GROUP BY
SORD,SQREQ,SOFAC,IPROD,IDESC,IFII,IITYP,ICLAS,OOPNO,OWRKC,OOPDS,OTOOL,OOPER,
OSTAT,SORTEM,OCDTE
ORDER BY SORD,OOPNO,OSTAT
/END-EXEC
For those of you that recognize this as BPCS ... we are version 405
CD
It
has been a while since I modified this. I not see right now what role
master FRT file is playing, which is where FOD file got the data
needed
for current shop order.
Description FOD.OOPDS & FRT.ROPDS
Status FOD.OSTAT & FRT.RSTAT
Operation FOD.OOPNO & FRT.ROPNO
Shop Order only in Order files FSO.SORD & FOD.OORD
Parent Item in several files, includes FRT.RPROD & FSO.SPROD
I also do not remember if SQL can read two different logicals of the
same
physical, which would complicate field identification.
-
Al Macintyre
http://en.wikipedia.org/wiki/User:AlMac
http://www.ryze.com/go/Al9Mac
BPCS/400 Computer Janitor ... see
http://radio.weblogs.com/0107846/stories/2002/11/08/bpcsDocSources.html
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
