Re: Order by derived fields

Thanks, now I know I need to go to a meeting tonight..............

On 1/26/07, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:

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
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.