Product Srch Locs table using RPGSQL loading Array
Posted: Wed Oct 27, 2021 11:25 pm
Scott, I'v searched and exhausted resources I'm urgently in need of help to resolve getting data from a table load data into a data structure using Search Criteria filters String there are 4 2 data structure (PDDTA and Vparts) arrays never get loaded with data from table. If I run a regular SQL Select I get records. also I run the program in DEBUG with service point entry in RDI with breaks in code to check values
Scott then in the end I'm working with the arrays stored values from the select and Vparts vaule are always blank because PRDDTA is blank
Code: Select all
Dcl-F EDIMIKPRD Disk Usage(*Input:*Output) Keyed Qualified UsrOpn;
Data Structure:
Dcl-DS PRDDTA DIM(100) LEN(150) Qualified;
Itmid Char(18);
Upccd Char(12);
Itmdesc Char(40);
Altdesc Char(40);
Mikmino Char(10);
MIKSKU Char(10);
Onhand Zoned(9:2);
Price Zoned(9:2);
END-DS;
Dcl-ds VParts Dim(100) LEN(148) qualified;
Itmid Char(18);
Upccd Char(12);
Itmdesc Char(40);
Altdesc Char(40);
Mikmino Char(10);
MikSKU Char(10);
Onhand Zoned(9:2);
Price Zoned(9:2);
End-Ds VParts;
1. if SavRFilter = 'B';
// Begins with
Eval SrchDesc = '%' + TRIM(SrchDesc) + '%';
EXEC Sql
Declare ProdsReq cursor for
Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
Miksku, Onhand, Price
From MIKPOC/EDIMikPRD Where
CONTAINS(Itmdesc, Trim(:SrchDesc)) = 1;
Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
EXEC SQL OPEN ProdsReq;
EXEC SQL
FETCH FROM ProdsReq for :RowCnt rows INTO :PRDDTA;
if (Ctr <= SavRMaxrtn) and (PRDDTA(x).Itmid > *blanks);
Eval VParts(X).Itmid= PRDDTA(x).Itmid;
Eval VParts(X).Upccd = PRDDTA(x).Upccd;
Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
Eval Vparts(X).Price = PRDDTA(x).Price;
Eval RtnRows = RtnRows + 1;
Eval Matched = Matched + 1;
Eval Ctr = Ctr +1;
Eval X = X + 1;
Endif;
Endif;
2. If SavRFilter = 'C'; //Contains
Eval SrchDesc = '_' + %TRIM(SrchDesc) + '_';
EXEC Sql
Declare ProdsReq2 cursor for
Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
Miksku, Onhand, Price
From MIKPOC/EDIMikPRD Where Itmdesc
Like Trim(:SrchDesc);
Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
EXEC SQL OPEN ProdsReq2;
EXEC SQL
FETCH FROM ProdsReq2 for :RowCnt rows INTO :PRDDTA;
if Ctr <= SavRMaxrtn and (PRDDTA(x).Itmid > *blanks);
Eval VParts(X).Itmid= PRDDTA(x).Itmid;
Eval VParts(X).Upccd = PRDDTA(x).Upccd;
Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
Eval Vparts(X).Price = PRDDTA(x).Price;
Eval RtnRows = RtnRows + 1;
Eval Matched = Matched + 1;
eval Ctr = Ctr +1;
Eval X = X + 1;
Endif;
Endif;
If SavRFilter = 'C'; //Contains
EXEC SQL Close ProdsReq2;
Endif;
If SavRFilter = 'E'; // Exact
eval NbrOfRows = 50;
eval Matched = 0;
eval RtnRows = 0;
eval X = 1;
EXEC Sql
Declare ProdsReq3 cursor for
Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
Miksku, Onhand, Price
From MIKPOC/EDIMikPRD Where Itmdesc
Like :SrchDesc;
Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
EXEC SQL OPEN ProdsReq3;
EXEC SQL
FETCH FROM ProdsReq3 for :RowCnt rows INTO :PRDDTA;
// verify beigins with
if Ctr <= SavRMaxrtn and (PRDDTA(x).Itmid > *blanks);
Eval Matched = Matched + 1;
Eval VParts(X).Itmid= PRDDTA(x).Itmid;
Eval VParts(X).Upccd = PRDDTA(x).Upccd;
Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
Eval Vparts(X).Price = PRDDTA(x).Price;
Eval RtnRows = RtnRows + 1;
Eval Matched = Matched + 1;
Eval Ctr = Ctr +1;
Eval X = X + 1;
Endif;
Endif;
If SavRFilter = 'E'; // Exact
EXEC SQL Close ProdsReq3;
Endif;
If SavRFilter = 'K'; // Keyword
eval Matched = 0;
eval RtnRows = 0;
X = 1;
// Read EDIMikPRD VParts; //SrchDesc Like Trim(:SrchDesc);
EXEC Sql
Declare ProdsReq4 cursor for
Select Itmid, Upccd, Itmdesc, Altdesc, Mikmino,
Miksku, Onhand, Price
From MIKPOC/EDIMikPRD Where Itmdesc
Like Trim(:SavRString) or
AltDesc LIKE Trim(:SavRString);
Exec SQL GET DIAGNOSTICS :RowCnt = ROW_COUNT ;
EXEC SQL OPEN ProdsReq4;
EXEC SQL
FETCH FROM ProdsReq4 for :RowCnt rows INTO :PRDDTA;
if Ctr < SavRMaxrtn and (PRDDTA(x).Itmid > *blanks);
Eval Matched = Matched + 1;
Eval VParts(X).Itmid= PRDDTA(x).Itmid;
Eval VParts(X).Upccd = PRDDTA(x).Upccd;
Eval VParts(X).Itmdesc = PRDDTA(x).Itmdesc;
Eval VParts(X).Altdesc = PRDDTA(x).ALTDESC;
Eval VParts(X).Mikmino = PRDDTA(x).Mikmino;
Eval Vparts(X).Miksku = PRDDTA(x).Miksku;
Eval Vparts(X).Onhand = PRDDTA(x).Onhand;
Eval Vparts(X).Price = PRDDTA(x).Price;
Eval RtnRows = RtnRows + 1;
Eval Matched = Matched + 1;
Eval X = X + 1;
Eval Ctr = Ctr +1;
Eval PassFail = 'P';
Endif;
Endif;
If SavRFilter = 'K'; // Keyword
EXEC SQL Close ProdsReq4;
Endif;
Code: Select all
For i = 1 to %ELEM(VParts);
if (PRDDTA(I).MOTSKU <> *blank) or (VParts(i).ITMID <> *blank);
RXS_ComposeVariable( V.VENDOR_PART : %Trim(VParts(i).MikSKU) );
RXS_ComposeVariable( V.VENDOR_ITEM : %Trim(VParts(i).ITMID) );
RXS_ComposeVariable( V.VENDOR_UPC_NO : %Char(VParts(i).UPCCD) );
RXS_ComposeVariable( V.MINO : %Trim(VParts(i).MikMINO) );
RXS_ComposeVariable( V.ITEM_NO : %Trim(VParts(i).ITMID) );
If PassFail = 'P';
RXS_ComposeVariable( V.UNIT_PRICE : %Char(VParts(i).PRICE) );
RXS_ComposeVariable( V.QTY_AVL : %Char(VParts(i).ONHAND) );
endif;
RXS_ComposeVariable( V.VENDOR_DESC : %Trim(VParts(i).ITMDESC) );
RXS_ComposeVariable( V.POR : %Trim(POR) );
RXS_ComposeVariable( V.PHONE_FLAG : %Trim(PHONE_FLAG) );
RXS_ComposeVariable( V.MESSAGE : %Trim(MESSAGE) );
RXS_ComposeSection( S.PROD_SRCHR_ITMDTL );
endif;
EndFor;
RXS_ComposeSection( S.PROD_SRCHR_ITMDTL_FOOTER );
//Close the cursor
RXS_ComposeSection( S.PROD_SRCHR_ITMHDR_FOOTER );
RXS_ComposeVariable( V.NO_MATCHES : %Char(Matched) );
RXS_ComposeVariable( V.ROWS_RETURN : %Char(Ctr) );
RXS_ComposeSection( S.PROD_SRCHR_FOOTER );
XMLResponse = RXS_GetComposeBuffer();
// Write the response XML to the IFS
RXS_ResetDS( PutStmfDS : RXS_DS_TYPE_PUTSTMF );
PutStmfDS.Stmf = %Trim(pXMLResponseFile);
RXS_PutStmf( XMLResponse : PutStmfDS );