Unconfigured Ad Widget



No announcement yet.

Sorting using host variables

  • Filter
  • Time
  • Show
Clear All
new posts

  • Sorting using host variables

    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
    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
    Col_SortA = 1;
    Col_SortB = 2;
    COL_SORTfld2 = 'PMPRD#'; // tried this for testing as well
    COL_SORTfld1 = 'PMLOC'; // tried this for testing as well

    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.

  • #2
    You need to have two different sections (one order by pmloc, pmprd . . . and the other pmprd , pmloc )
    The compiler needs this "hardcoded" since this is not a data-selection, but is integral to building the SQL statement.

    Otherwise, You need to build the statement with a Dynamic Select statement.
    (This second alternative is more work).

    This post explains it better than I ever could.
    Last edited by jvoris; 09-18-2013, 08:26 AM.