Can someone share with this SQL on the IBM i returns rows
Select Itmid, Upccd, Itmdesc, Altdesc, Mytmino, Mytsku, Onhand, Price From MIKELIB.MYPRD Where Itmdesc LIKE TRIM('%RBY%')
and my RPGSQL fails
DS Info
Dcl-DS PRDDTA DIM(100) LEN(148) Qualified;
Itmid Char(18);
Upccd Char(12);
Itmdesc Char(40);
Altdesc Char(40);
MytminoChar(10);
MytskuChar(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);
Mytmino Char(10);
Mytsku Char(10);
Onhand Zoned(9:2);
Price Zoned(9:2);
End-Ds VParts;
Dcl-s NbrOfRows uns(5:0) inz(%elem(PRDDTA)) ;
Dcl-s OFFSET uns(10:0) inz(%elem(PRDDTA)) ;
Addtional program code below this point nothing pertaining to SQL
if SavRFilter = 'B'; // Begins with
eval NbrOfRows = 50;
eval Matched = 0;
eval RtnRows = 0;
eval X = 1;
eval Ctr = 1;
Eval SrchDesc = '%' + %TRIM(SrchDesc) + '%'; < this field in debug contains %RBY%
//Set SQL Script
EXEC Sql
Declare ProdsReq cursor for
Select Itmid, Upccd, Itmdesc, Altdesc, Mytmino,
Motsku, Onhand, Price
From MIKELIB/MYPRD Where Itmdesc
Like Trim(:SrchDesc)
OFFSET :Offset ROWS;
EXEC SQL OPEN ProdsReq;
EXEC SQL
FETCH FROM ProdsReq for :NbrOfRows rows INTO :PRDDTA;
// verify beigins
if Ctr <= SavRMaxrtn;
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).Mytmino= PRDDTA(x).Mytmino;
Eval Vparts(X).Mytsku= PRDDTA(x).Mytsku;
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;
EXEC SQL Close ProdsReq;
Select Itmid, Upccd, Itmdesc, Altdesc, Mytmino, Mytsku, Onhand, Price From MIKELIB.MYPRD Where Itmdesc LIKE TRIM('%RBY%')
and my RPGSQL fails
DS Info
Dcl-DS PRDDTA DIM(100) LEN(148) Qualified;
Itmid Char(18);
Upccd Char(12);
Itmdesc Char(40);
Altdesc Char(40);
MytminoChar(10);
MytskuChar(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);
Mytmino Char(10);
Mytsku Char(10);
Onhand Zoned(9:2);
Price Zoned(9:2);
End-Ds VParts;
Dcl-s NbrOfRows uns(5:0) inz(%elem(PRDDTA)) ;
Dcl-s OFFSET uns(10:0) inz(%elem(PRDDTA)) ;
Addtional program code below this point nothing pertaining to SQL
if SavRFilter = 'B'; // Begins with
eval NbrOfRows = 50;
eval Matched = 0;
eval RtnRows = 0;
eval X = 1;
eval Ctr = 1;
Eval SrchDesc = '%' + %TRIM(SrchDesc) + '%'; < this field in debug contains %RBY%
//Set SQL Script
EXEC Sql
Declare ProdsReq cursor for
Select Itmid, Upccd, Itmdesc, Altdesc, Mytmino,
Motsku, Onhand, Price
From MIKELIB/MYPRD Where Itmdesc
Like Trim(:SrchDesc)
OFFSET :Offset ROWS;
EXEC SQL OPEN ProdsReq;
EXEC SQL
FETCH FROM ProdsReq for :NbrOfRows rows INTO :PRDDTA;
// verify beigins
if Ctr <= SavRMaxrtn;
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).Mytmino= PRDDTA(x).Mytmino;
Eval Vparts(X).Mytsku= PRDDTA(x).Mytsku;
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;
EXEC SQL Close ProdsReq;
Comment