|
Traditionally this is done using only 1 sql statement. Again using between. D @SQLByCat C 'Select * from dmitmmst + D Where (WHSLC between + D ? and ?) and + D (CATCD1 between ? and ?) + D Order By SAQYR desc' If you want only 1 value for CATCD1 then you use that for both parameters. If you want all, then use *loval and *hival. Now, I am assuming you are using quotes around the substitution parameter for CATCD1 because it is a character. Like: CREATE TABLE QTEMP/DMITMMST (WHSLC NUMERIC ( 2, 0) NOT NULL WITH DEFAULT, CATCD1 CHAR ( 1) NOT NULL WITH DEFAULT, SAQYR NUMERIC (4 ,0) NOT NULL WITH DEFAULT) INSERT INTO QTEMP/DMITMMST VALUES(1, '1', 1994) INSERT INTO QTEMP/DMITMMST VALUES(2, '2', 2001) The quotes are not necessary. This is actual working code: H ActGrp(*CALLER) H DftActGrp(*NO) D OpenCursor PR n D FetchCursor PR n D CloseCursor PR n D @SQLByCat S 32000a inz( D 'Select * from dmitmmst + D Where + D (WHSLC between ? and ?) and + D (CATCD1 between ? and ?) + D Order By SAQYR desc') D MyLib s 10a D MyFile s 10a D dmitmmst e ds extname(dmitmmst) D whslca s like(whslc) inz(1) D whslcb s like(whslc) inz(2) D catcd1a s like(catcd1) inz(*loval) D catcd1b s like(catcd1) inz(*hival) /free *inlr=*on; if not OpenCursor(); // perform error routine to alert the troops // ... Else; Dow FetchCursor(); // putting the fetchcursor on the do loop allows the user of // iter, and thus iter will not perform an infinite loop // normal processing here... EndDo; CloseCursor(); EndIf; return; /end-free P OpenCursor B D OpenCursor PI like(ReturnVar) D ReturnVar s n 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/END-EXEC /free dsply sqlstt; /end-free C/EXEC SQL C+ Prepare s1 from :@SQLByCat C/END-EXEC /free dsply sqlstt; /end-free C/EXEC SQL C+ Declare C1 cursor for S1 C/END-EXEC /free dsply sqlstt; /end-free C/EXEC SQL C+ Open C1 using :whslca, :whslcb, :catcd1a, :catcd1b C/END-EXEC /free dsply sqlstt; /end-free /free Select; When SqlStt='00000'; return *on; Other; return *off; EndSl; /end-free P OpenCursor E /eject P FetchCursor B D FetchCursor PI like(ReturnVar) D ReturnVar s n C/EXEC SQL C+ Fetch C1 into :dmitmmst C/END-EXEC /free Select; When sqlstt='00000'; // row was received, normal ReturnVar=*on; When sqlstt='02000'; // same as %eof, sooner or later this is normal ReturnVar=*off; Other; // alert the troops! ReturnVar=*off; EndSl; return ReturnVar; /end-free P FetchCursor E /eject P CloseCursor B D CloseCursor PI like(ReturnVar) D ReturnVar s n C/EXEC SQL C+ Close C1 C/END-EXEC /free Select; When sqlstt='00000'; // cursor was closed, normal ReturnVar=*on; Other; // alert the troops! ReturnVar=*off; EndSl; return ReturnVar; /end-free P CloseCursor E If I change this D catcd1a s like(catcd1) inz(*loval) D catcd1b s like(catcd1) inz(*hival) to this: D catcd1a s like(catcd1) inz('1') D catcd1b s like(catcd1) inz('1') It works just fine. Rob Berendt
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.