Order by derived fields

Caution:  If you are a "friend of Bill" do not read this sql statement - 
it may make you fall off the wagon.

Had a fellow that put an sql statement in Domino.  It got results but at 
times it ran darn near 2,000 seconds.  I reformatted it and tested it out 
in iNav and got much better performance.  The neat thing about Domino was 
that it allowed sorting on derived fields, like LSDTE0.  The best I could 
do in iNav was to order by the column number relative to it's position in 
the select statement.

Now, in the sample there are some hard coded values for customer number 
and item number.  This, of course, we be replaced by variables in the 
actual code.

Original SQL:
    Select CNME,CAD3, CMALPH from CLIDIVF.RCML01 where (CCust IN (13221)) 
Order By CMALPH   (Elapsed Time=0.05 Seconds)
     select LID, LPROD, LCUST, LORD, LLINE,  LQORD, LQSHP, HCPO, LSDTE, 
CMALPH, 'CLIDIVF' as LIBNAME,  ifnull(XCITEM,LPROD) as XCITEM0, (Select 
Count(XCITEM) from CLIDIVF.LCIXL01 Where ECL.LPROD = XPROD and ECL.LCUST = 
XCUST) as IXCNT0,(Select 
ifnull(max(nullif(ifnull(nullif(ifnull(BLDTSP,0),0),BLRDTE),0)),ECL.LSDTE) 
from CLIDIVF.BLLL12 Where ECL.LORD = BLORD and ECL.LLINE = BLLINE) as 
LSDTE0, SNDESC from CLIDIVF.ECLL12 ECL left outer join CLIDIVF.ECHL02 ECH 
on LORD = HORD left outer join CLIDIVF.LCIXL01 LCIX on ECL.LPROD = XPROD 
and ECL.LCUST = XCUST left outer join CLIDIVF.RCML01 RCM on ECL.LCUST = 
CCUST left outer join CLIDIVF.ESNL01 ESN on ESN.SNTYPE='O' and 
ESN.SNCUST=ECL.LORD and ESN.SNDESC LIKE 'Project Name = %' where (LCUST IN 
(13221)) and LPROD <> '' and (LID='CL' or Exists (select BLID from 
CLIDIVF.BLLL12 BLL where ecl.LORD = BLORD and ecl.LLINE = BLLINE and 
BLDTSP>20061228)) and (LPROD like '%K-F1-024AJ-EM%' or exists (select 
XCITEM from CLIDIVF.LCIXL01 where XPROD=ECL.LPROD and XCITEM like 
'%K-F1-024AJ-EM%'))  ORDER BY XCITEM0, LSDTE0, LQORD

As doctored:
With T1 as (
  select xprod, xcust, count(xcitem) as ixcnto
  from CLIDIVF.LCIXL01
  group by xprod, xcust
  having xprod like '%K-F1-024AJ-EM%'
    and xcust in (13221))
select ecl.LID, ecl.LPROD, ecl.LCUST, ecl.LORD, ecl.LLINE, ecl.LQORD, 
          ecl.LQSHP,
          ech.HCPO, 
          ecl.LSDTE,
          rcm.CMALPH,
          'CLIDIVF' as LIBNAME,
          ifnull(lcix.XCITEM,ecl.LPROD) as XCITEM0,
          ifnull(t1.ixcnto,0) as ixcnto,
          case
             when bll.bldtsp is not null
                 and bll.bldtsp >0
                then bll.bldtsp
             when bll.blrdte is not null
                 and bll.blrdte >0
                then bll.blrdte
             else ecl.lsdte
          end as lsdteo,
          esn.sndesc
from CLIDIVF.ECLL12 ECL left outer join CLIDIVF.ECHL02 ECH on LORD = HORD
   left outer join CLIDIVF.LCIXL01 LCIX on ECL.LPROD = LCIX.XPROD
     and ECL.LCUST = LCIX.XCUST
   left outer join CLIDIVF.RCML01 RCM on ECL.LCUST = CCUST
   left outer join CLIDIVF.ESNL01 ESN on ESN.SNTYPE='O' and
     ESN.SNCUST=ECL.LORD and ESN.SNDESC LIKE 'Project Name = %'
   left outer join CLIDIVF.BLLL12 BLL on ecl.lord = bll.blline
     and ecl.lline=bll.blline
   left outer join t1 on t1.xprod = ecl.lprod
     and t1.xcust = ecl.lcust
where (LCUST IN (13221)) and LPROD <> '' 
   and (LID='CL' or Exists (select BLID from CLIDIVF.BLLL12 BLL
          where ecl.LORD = BLORD and ecl.LLINE = BLLINE and 
BLDTSP>20061228))
   and (LPROD like '%K-F1-024AJ-EM%' or exists (select XCITEM from
          CLIDIVF.LCIXL01 where XPROD=ECL.LPROD and XCITEM like 
'%K-F1-024AJ-EM%'))
ORDER BY XCITEM0, 13, LQORD
for fetch only;


Rob Berendt