|
midrange.com
Patrick,
Here is an embedded SQL sample. Remember to set your program attribute to
SQLRPGLE or SQLRPG so that it compiles correctly. (The SQL is the same
regardless if you are using RPG or RPGLE)
Diane
*************** Beginning of data
*************************************
0001.00
*---------------------------------------------------------------------
0002.00 fFILE4 UF E K DISK
0003.00
*---------------------------------------------------------------------
0004.00 dsql_INPUT DS
0005.00 d sql_FIELD1 3 0
0006.00 d sql_FIELD2 2 0
0007.00 d sql_FIELD3 2
0008.00
*---------------------------------------------------------------------
0009.00 c *entry plist
0010.00 c parm parmChoice 1
0011.00 c parm parmNumber 2 0
0012.00
*---------------------------------------------------------------------
0013.00 c SELECT
0014.00 c WHEN parmChoice = 'A'
0015.00 c/exec SQL
0016.00 c+ declare C1 cursor for
0017.00 c+ select FILE1.FIELD1,
0018.00 c+ FILE2.FIELD2,
0019.00 c+ FILE3.FIELD3
0020.00 c+ from FILE1, FILE2, FILE3
0011.00 c parm parmNumber 2 0
0012.00
*---------------------------------------------------------------------
0013.00 c SELECT
0014.00 c WHEN parmChoice = 'A'
0015.00 c/exec SQL
0016.00 c+ declare C1 cursor for
0017.00 c+ select FILE1.FIELD1,
0018.00 c+ FILE2.FIELD2,
0019.00 c+ FILE3.FIELD3
0020.00 c+ from FILE1, FILE2, FILE3
0021.00 c+ where FILE1.FIELD1 = FILE2.FIELD1
0022.00 c+ and FILE1.FIELD2 = :parmNumber
0023.00 c+ and FILE2.FIELD3 = FILE3.FIELD3
0024.00 c+ order by FILE1.FIELD1, FILE2.FIELD2, FILE3.FIELD3
0025.00 c/end-exec
0026.00 c WHEN parmChoice = 'B'
0027.00 c/exec SQL
0028.00 c+ declare C1 cursor for
0029.00 c+ select FILE1.FIELD1,
0030.00 c+ FILE2.FIELD2,
0031.00 c+ FILE3.FIELD3
0022.00 c+ and FILE1.FIELD2 = :parmNumber
0023.00 c+ and FILE2.FIELD3 = FILE3.FIELD3
0024.00 c+ order by FILE1.FIELD1, FILE2.FIELD2, FILE3.FIELD3
0025.00 c/end-exec
0026.00 c WHEN parmChoice = 'B'
0027.00 c/exec SQL
0028.00 c+ declare C1 cursor for
0029.00 c+ select FILE1.FIELD1,
0030.00 c+ FILE2.FIELD2,
0031.00 c+ FILE3.FIELD3
0032.00 c+ from FILE1, FILE2, FILE3
0033.00 c+ where FILE1.FIELD1 = FILE2.FIELD1
0034.00 c+ and FILE1.FIELD2 = :parmNumber
0035.00 c+ and FILE2.FIELD3 = FILE3.FIELD3
0036.00 c+ order by FILE2.FIELD2, FILE1.FIELD1, FILE3.FIELD3
0037.00 c/end-exec
0038.00 c WHEN parmChoice = 'C'
0039.00 c/exec SQL
0040.00 c+ declare C1 cursor for
0041.00 c+ select FILE1.FIELD1,
0042.00 c+ FILE2.FIELD2,
0033.00 c+ where FILE1.FIELD1 = FILE2.FIELD1
0034.00 c+ and FILE1.FIELD2 = :parmNumber
0035.00 c+ and FILE2.FIELD3 = FILE3.FIELD3
0036.00 c+ order by FILE2.FIELD2, FILE1.FIELD1, FILE3.FIELD3
0037.00 c/end-exec
0038.00 c WHEN parmChoice = 'C'
0039.00 c/exec SQL
0040.00 c+ declare C1 cursor for
0041.00 c+ select FILE1.FIELD1,
0042.00 c+ FILE2.FIELD2,
0043.00 c+ FILE3.FIELD3
0044.00 c+ from FILE1, FILE2, FILE3
0045.00 c+ where FILE1.FIELD1 = FILE2.FIELD1
0046.00 c+ and FILE1.FIELD2 = :parmNumber
0047.00 c+ and FILE2.FIELD3 = FILE3.FIELD3
0048.00 c+ order by FILE3.FIELD3, FILE1.FIELD1, FILE2.FIELD2
0049.00 c/end-exec
0050.00
0051.00 c ENDSL
0052.00
*---------------------------------------------------------------------
0053.00 c/exec SQL
0044.00 c+ from FILE1, FILE2, FILE3
0045.00 c+ where FILE1.FIELD1 = FILE2.FIELD1
0046.00 c+ and FILE1.FIELD2 = :parmNumber
0047.00 c+ and FILE2.FIELD3 = FILE3.FIELD3
0048.00 c+ order by FILE3.FIELD3, FILE1.FIELD1, FILE2.FIELD2
0049.00 c/end-exec
0050.00
0051.00 c ENDSL
0052.00
*---------------------------------------------------------------------
0053.00 c/exec SQL
0054.00 c+ open C1
0055.00 c/end-exec
0056.00
0057.00 c/exec SQL
0058.00 c+ fetch C1 into :sql_INPUT
0059.00 c/end-exec
0060.00
0061.00 c SQLCOD DOWEQ 0
0062.00
0063.00 c sql_FIELD1 chain FILE4
0064.00 c update FILE4
0055.00 c/end-exec
0056.00
0057.00 c/exec SQL
0058.00 c+ fetch C1 into :sql_INPUT
0059.00 c/end-exec
0060.00
0061.00 c SQLCOD DOWEQ 0
0062.00
0063.00 c sql_FIELD1 chain FILE4
0064.00 c update FILE4
0065.00
0066.00 c/exec SQL
0067.00 c+ fetch C1 into :sql_INPUT
0068.00 c/end-exec
0069.00
0070.00 c ENDDO
0071.00
0072.00 c/exec SQL
0073.00 c+ close C1
0074.00 c/end-exec
0075.00
0066.00 c/exec SQL
0067.00 c+ fetch C1 into :sql_INPUT
0068.00 c/end-exec
0069.00
0070.00 c ENDDO
0071.00
0072.00 c/exec SQL
0073.00 c+ close C1
0074.00 c/end-exec
0075.00
0076.00 c eval *INLR = *ON
0077.00
*---------------------------------------------------------------------
****************** End of data
****************************************
-----Original Message-----
From: Contractor1@Parkdalemills.com
[mailto:Contractor1@Parkdalemills.com]
Sent: Wednesday, December 06, 2000 11:40 AM
To: RPG400-L@midrange.com
Subject: RE: Secondary Keys
Diane,
Sounds like this is what I'm looking for. Can you send me any code
examples? You can e-mail me directly if you prefer.
Patrick Conner
www.ConnecTown.com
(828) 244-0822
"Brown, Diane R."
<DBrown@DoanePetC To:
"'RPG400-L@midrange.com'"
are.com> <RPG400-L@midrange.com>
Sent by: cc:
owner-rpg400-l@mi Subject: RE: Secondary Keys
drange.com
12/06/00 10:53 AM
Please respond to
RPG400-L
Patrick,
Have you considered using embedded SQL? You could have a number of SQL
Declare statements in a Select, then based on the user choices, choose the
one that you need. I use the SQL file as input only, then chain to another
LF (that is over the same PF) for the actual updates. I have written many
embedded SQL programs if you need examples.
Diane
-----Original Message-----
From: Contractor1@Parkdalemills.com
[mailto:Contractor1@Parkdalemills.com]
Sent: Wednesday, December 06, 2000 9:26 AM
To: RPG400-L@midrange.com
Subject: Secondary Keys
I have a maintenance program that can be displayed with multiple sorts. It
is set up to read different a logical based on what the user keys as a sort
code. However, some information in the subfile is from secondary files and
they want the subfile sorted on those fields. I looked at doing a join
logical, but I can't tell where I'm allowed to use the fields from the
secondary file as key fields. Anyone have any suggestions?
Patrick Conner
www.ConnecTown.com
(828) 244-0822
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
