I have two fields (location, product) that a client can enter on the screen to return the result set. The result set can be ordered by either location/product or product/location pending which value(s) the client wants returned.
My issue is the result set is not being ordered correctly if I try to use host variables in the "order by" clause.
This is an SQLRPGLE, v5r4 iSeries program with embedded sql.
-- determine if client selected a location and/or a product
select;
when selproduct <> ' ' and selloc = ' ';
Col_SortA = 2;
Col_SortB = 1;
COL_SORTfld1 = 'PMPRD#'; // tried this for testing as well
COL_SORTfld2 = 'PMLOC'; // tried this for testing as well
other;
Col_SortA = 1;
Col_SortB = 2;
COL_SORTfld2 = 'PMPRD#'; // tried this for testing as well
COL_SORTfld1 = 'PMLOC'; // tried this for testing as well
ENDSL;
exec sql declare CurMS0118 SCROLL CURSOR for
select ifnull(pmloc,' ') pmloc,
ifnull(pmprd#,' ') pmprd#,
ifnull(ipdsc,' ') ipdsc,
ifnull(INGroup,' ') INGroup,
ifnull(Impcat,' ') Impcat,
ifnull(imszd,' ') imszd,
ifnull(inszuom,' ') inszuom,
ifnull(inetw,0) inetw
from prodmast
left outer join inmstp01
on pmprd#=iop
where pmloc <> '00' and (
pmloc >= case when :selloc <> ' '
then :selloc
else pmloc end and
pmprd# >= case when :selproduct <> ' '
then :selProduct
else pmprd# end )
order by :Col_SortA, :Col_SortB ;
"Order by pmloc, pmprd#" works fine, but the host variables doesn't. Please - any suggestions? Thanks.
My issue is the result set is not being ordered correctly if I try to use host variables in the "order by" clause.
This is an SQLRPGLE, v5r4 iSeries program with embedded sql.
-- determine if client selected a location and/or a product
select;
when selproduct <> ' ' and selloc = ' ';
Col_SortA = 2;
Col_SortB = 1;
COL_SORTfld1 = 'PMPRD#'; // tried this for testing as well
COL_SORTfld2 = 'PMLOC'; // tried this for testing as well
other;
Col_SortA = 1;
Col_SortB = 2;
COL_SORTfld2 = 'PMPRD#'; // tried this for testing as well
COL_SORTfld1 = 'PMLOC'; // tried this for testing as well
ENDSL;
exec sql declare CurMS0118 SCROLL CURSOR for
select ifnull(pmloc,' ') pmloc,
ifnull(pmprd#,' ') pmprd#,
ifnull(ipdsc,' ') ipdsc,
ifnull(INGroup,' ') INGroup,
ifnull(Impcat,' ') Impcat,
ifnull(imszd,' ') imszd,
ifnull(inszuom,' ') inszuom,
ifnull(inetw,0) inetw
from prodmast
left outer join inmstp01
on pmprd#=iop
where pmloc <> '00' and (
pmloc >= case when :selloc <> ' '
then :selloc
else pmloc end and
pmprd# >= case when :selproduct <> ' '
then :selProduct
else pmprd# end )
order by :Col_SortA, :Col_SortB ;
"Order by pmloc, pmprd#" works fine, but the host variables doesn't. Please - any suggestions? Thanks.
Comment