|
All; Thanks for taking out time to help me correcting my code. I am really thankful for that. I have made the corrections that you guys have pointed out. Rob: I followed your instructions closely like always and I was confused about one very good point that you made.
Notice that you are selecting on an item number before you are prompted for it? That will work on the parameter that you passed
into
the program, but not on any values prompted.
midrange.com
I am using the exfmt PROMPT;
before the SQL statement.
is that wrong?
My code might still have a lot of mistakes in it but I will struggle and
learn...:-0)
*******************************************************************************************************
// Declare Files
FITEMINQSQLCF E WORKSTN IndDS(WkstnInd)
Fqsysprt o f 132 printer
D itemchoice PR EXTPGM('ITEMINQSQL')
D itemnum 5 0
D itemchoice PI
D itemnum 5 0
is this even necessary if I am just looking for itemnum to come from
the user by prompting?
D WkstnInd DS
D NotFound 40 40N
D Exit 03 03N
D Cancel 12 12N
D myDS E ds extname(item_pf)
/FREE
PgmNam = 'ITEMINQSQL';
Exfmt Prompt;
Dow NOT Exit;
/END-FREE
C/EXEC SQL
C+ Set Option
C+ Naming = *Sys,
C+ Commit = *None,
C+ UsrPrf = *User,
C+ DynUsrPrf = *User,
C+ Datfmt = *iso,
C+ CloSqlCsr = *EndMod,
C+ Commit = *NONE
C/END-EXEC
c/exec sql
c+ select ITMNBR, ITMDESCR into :myDS from
c+ testing/item_pf where itmnbr=:itemnum
c/end-exec
/FREE
Dow NOT Cancel; // Display details
except heading;
dow SQLSTT = '00000';
except detail;
enddo;
If Exit;
*InLR = *ON;
Return;
Endif;
Enddo;
// No Item record found or F12 pressed - display prompt
Cancel = *Off;
NotFound = *on;
Exfmt Prompt;
Enddo;
*InLR = *ON;
/END-FREE
Oqsysprt e heading 1
O + 1 'ITEM NUMBER'
O + 20 'ITEM DESCRIPTION'
Oqsysprt e detail 3
O ITMNBR + 1
O ITMDESCR + 20
***********************************************************************************************************
Thanks again for everybody's help,
cheers,
Jake.
On 9/22/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
A number of things: 1 - You do not need the external file name if you are accessing it via sql 2 - I'd use an external data structure for field definitions. Like D myds eds extname(item_pf) 3 - How many items with the same item number do you expect to find in the item file? Doesn't the file have a primary key constraint? If you are only expecting one, then get rid of the cursor and use a direct select into. If you insist on using the cursor, then you will need to close it and open it within your loop. Notice that the open on the cursor has a WHERE clause? Notice that you are selecting on an item number before you are prompted for it? That will work on the parameter that you passed into the program, but not on any values prompted. 4 - skip %found, use sqlstt for the select into, or the fetch. If you need to test for multiple item numbers you may want to try select count(*), itemnbr, max(itemdescr) from item_pf group by itemnbr having count(*)>1 Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Jake M" <jakeroc@xxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 09/21/2006 04:38 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> cc Subject Embedded SQL with DSPF problem. Hello All; Once again I am running into some newbie problems. This program is supposed to kick out a simple report based on the item number given by the user in the display file. The SQLRPGLE program compiles but it bombs out when I try and run it. I have a display file and a SQLRPGLE program which calls the display file. I think I am doing something wrong in the coding part itself. Should I be using an extra CL program with this? I do not know if this kind of report generation could be done with only one piece of sqlrpgle code and a dspf file. If anybody could point me in the right direction, I would really appreciate it. ITEMINQSQL.dspf ************************************************************************************************************************** A*%%TS DD 20060921 151811 jakem REL-V5.0.1 WDSc A*%%FD Example: Item Inquiry A*%%EC A DSPSIZ(24 80 *DS3) A REF(*LIBL/ITEM_PF) A CA03(03) A INDARA A R PROMPT A*%%TS DD 20060921 151811 jakem REL-V5.0.1 WDSc A PGMNAM 10A O 3 7 A 3 35'Item Inquiry' A COLOR(WHT) A 3 64DATE A EDTCDE(Y) A 8 20'Item Number . . . . . .:' A ITMNBR R D I 8 45 A 40 ERRMSG('Item not found on file - pl- A ease correct' 40) A 20 7'Press Enter to continue' A 21 7'F3=Exit' A COLOR(BLU) A*%%GP SCREEN1 01 ****************************************************************************************************************************** ITEMINQSQL.sqlrpgle ****************************************************************************************************************************** // Declare Files FITEM_PF IF E K DISK Rename(ITEM_PF:RITEMPF) FITEMINQSQLCF E WORKSTN IndDS(WkstnInd) Fqsysprt o f 132 printer D itemchoice PR EXTPGM('ITEMINQSQL') D itemnum 20 D itemchoice PI D itemnum 20 D WkstnInd DS D NotFound 40 40N D Exit 03 03N D Cancel 12 12N D myDS ds LIKEREC(RITEMPF) /FREE PgmNam = 'ITEMINQSQL'; Exfmt Prompt; Dow NOT Exit; /END-FREE C/EXEC SQL C+ Set Option C+ Naming = *Sys, C+ Commit = *None, C+ UsrPrf = *User, C+ DynUsrPrf = *User, C+ Datfmt = *iso, C+ CloSqlCsr = *EndMod, C+ Commit = *NONE C/END-EXEC c/exec sql c+ declare mainCursor Cursor c+ for c+ select * from testing/item_pf where itmnbr=:itemnum c/end-exec c/exec sql c+ open mainCursor c/end-exec c/exec sql c+ fetch next c+ from mainCursor c+ into :myDS c/end-exec /FREE If %found(Item_PF); // Item Number valid? NotFound = *off; Dow NOT Cancel; // Display details except heading; dow SQLSTT = '00000'; except detail; /END-FREE c/exec sql c+ fetch next c+ from mainCursor c+ into :myDS c/end-exec /FREE enddo; If Exit; *InLR = *ON; Return; Endif; Enddo; Endif; // No Item record found or F12 pressed - display prompt Cancel = *Off; NotFound = *on; Exfmt Prompt; Enddo; *InLR = *ON; /END-FREE Oqsysprt e heading 1 O + 1 'ITEM NUMBER' O + 20 'ITEM DESCRIPTION' Oqsysprt e detail 3 O ITMNBR + 1 O ITMDESCR + 20 **************************************************************************************************************************************** Thanks in advance, cheers, Jake. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-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.
