Re: Can this be done in SQL instead of traditional 'CHAIN'? -- RPG400-L

Birgitta,
Thank you so much for enlightening me on the concept of CTE's. I solved the
problem using this query and it worked perfectly for me. Performance is not
too bad either. May be I can improve on that...:).

With x as (Select ITMNUM as xItemNo, Max(DTEEFF) as xMyDate, PRCUOM as
xPRCUOM
           from ec30data.ITMPRC
           where IBBRCH = ' '
           Group By ITMNUM, PRCUOM),
y as (Select xItemNo as yItemNo, xMyDate as yMyDate, xPRCUOM as yPRCUOM
          from x where xPRCUOM <> '99')
Select distinct a.*
 from ec30data.ITMPRC a join y on ITMNUM = yItemNo
          and DTEEFF = yMyDate and PRCUOM <> '99';

Thanks again,

cheers,

Jake.



On 11/3/06, Jake M <jakeroc@xxxxxxxxx> wrote:


Birgitta,
I appreciate your assistance. I am a tad bewildered though. This the query
that I am using and I am not grouping by prcuom but still getting both the
uom's. I think the reason is because there are two numbers coming out of
price field and two 'CS' and '99' coming out of uom field. Hmm...Any
thoughts?

  With x as (Select ITMNUM as xItemNo, Max(DTEEFF) as xMyDate
            from ec30data.ITMPRC
            where IBBRCH = ' ' and PRCUOM = 'CS'
            Group By ITMNUM)
  Select a.*
  from ec30data.ITMPRC a join x on ITMNUM = xItemNo and DTEEFF = xMyDate;



Thanks again,

cheers.

Jake.




On 11/3/06, HauserBirgitta <Hauser@xxxxxxxxxxxxxxx> wrote:
>
> Hi Jake,
>
> just have a look at the example I changed. I not only added the where
> condition, but also removed the case statement and the column PRCUOM
> from
> the CTE.
> You had a group by clause on ItemNo and PRCUOM. In this way you'll get
> two
> rows, one for each PRCUOM.
>
> Mit freunlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars."
> (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
>
>
>
>
> ----- Original Message -----
> From: "Jake M" <jakeroc@xxxxxxxxx>
> To: "RPG programming on the AS400 / iSeries" < rpg400-l@xxxxxxxxxxxx>
> Sent: Friday, November 03, 2006 20:48
> Subject: Re: Can this be done in SQL instead of traditional 'CHAIN'?
>
>
> Birgitta,
> That is exactly what I thought and also tried adding PRCUOM = 'CS' but
> doing
> this is still showing item 'b' two times with PRCUOM as '99' in one
> record
> and 'CS' in one record. Am I missing something here? Thanks again for
> your
> assistance.
>
> cheers,
>
> Jake.
>
> On 11/3/06, HauserBirgitta < Hauser@xxxxxxxxxxxxxxx> wrote:
> >
> > Hi,
> >
> > >>I have another field named 'PRCUOM' now an item may have 'CS' or
> '99'
> > what
> > >>if I want to select only items with 'CS'...So, for item 'b', I want
> to
> > see
> > >>only one record...
> >
> > ... just add a where condition to the CTE:
> >   With x as (Select ITMNUM as xItemNo, Max(DTEEFF) as xMyDate,
> >                from ec30data.ITMPRC
> >                where     IBBRCH = ' '
> >                      and PRCUCOM = 'CS'
> >             Group By ITMNUM)
> > Select a.*
> > from    ec30data.ITMPRC a join x
> >       on ITMNUM = xItemNo and DTEEFF = xMyDate and PRCUOM = xprcuom;
> >
> > Just an other comment:
> > A Select-Statement is not restricted to a single CTE, i.e. you can
> define
> > as
> > much as you want (there may be a restriction at 256? CTEs).
> > And it's even possible to use a CTE in an other CTE.
> > In the following example the ItemNo with the highest Sales (including
> the
> > total sales) will be determined.
> > Example:
> > with a as (select ItemNo as aItemNo, sum(Sales) as SalesPerItem
> >                      from Sales
> >                      group by ItemNo),
> >      b as (Select Max(SalesPerItem) as MaxSales from a)
> > select a.*
> >    from a join b on SalesPerItem = MaxSales ;
> >
> > Mit freunlichen Grüßen / Best regards
> >
> > Birgitta Hauser
> >
> > "Shoot for the moon, even if you miss, you'll land among the stars."
> (Les
> > Brown)
> > "If you think education is expensive, try ignorance." (Derek Bok)
> >
> >
> >
> >
> > ----- Original Message -----
> > From: "Jake M" <jakeroc@xxxxxxxxx>
> > To: "RPG programming on the AS400 / iSeries" < rpg400-l@xxxxxxxxxxxx>
> > Sent: Friday, November 03, 2006 18:45
> > Subject: Re: Can this be done in SQL instead of traditional 'CHAIN'?
> >
> >
> > I appreciate and thank you all for all the suggestions.
> > Birgitta,
> > Thanks a bunch for the CTE suggestion. It helped out a great deal. I
> am
> > almost through but for a small problem. What if I want to throw in
> another
> > parameter?
> >
> > I have another field named 'PRCUOM' now an item may have 'CS' or '99'
> what
> > if I want to select only items with 'CS'...So, for item 'b', I want to
> see
> > only one record...
> > b     ''        20060712      59.46
> >
> > This is where I am at..
> >
> >   With x as (Select ITMNUM as xItemNo, Max(DTEEFF) as xMyDate, PRCUOM
> as
> > xprcuom,
> >             CASE WHEN (PRCUOM = 'CS' or PRCUOM = 'EA')
> >             THEN PRCUOM = 'CS'
> >             ELSE PRCUOM
> >             END
> >             from ec30data.ITMPRC
> >             where IBBRCH = ' '
> >             Group By ITMNUM, PRCUOM)
> > Select a.*
> > from ec30data.ITMPRC a join x on ITMNUM = xItemNo
> > and DTEEFF = xMyDate and PRCUOM = xprcuom;
> >
> > data
> > ===
> > item      branch  dateeff                 price      prcuom
> > a               1     20060108             15.18     bg
> > a               3     20060108             38.16     cs
> > a               2     20060509             48.15     cs
> > a                      20061015             39.12     99
> >
> > b               4     20060915             48.36      di
> > b               3     20060101             99.12      lm
> > b                      20060712             59.46      cs
> > b               2     20051225             74.69      cs
> > b                      20060712             18.45      99
> >
> > c               1     20050815             10.15      cs
> > c                      20061012             19.04      99
> > c               3     20061030             18.77      cs
> >
> > d               6     20051215              0.79       di
> > d               7     20061010             19.65      pr
> > d                      20061015             44.22      im
> > d               2     20050512             33.98      pr
> >
> > once again, thanks for all the suggestions. I appciate the help.
> >
> > cheers,
> >
> > Jake.
> >
> >
> >
> >
> >
> >
> > On 11/3/06, HauserBirgitta < Hauser@xxxxxxxxxxxxxxx> wrote:
> > >
> > > Hi,
> > >
> > > instead of a sub-subselect also a common table expression (CTE). A
> CTE
> > is
> > > nothing else than a temporary view, that is only valid for the
> > > select-statement where specified. IMHO CTEs are more readeable than
> > nested
> > > subselects.
> > >
> > > With x as (Select ItemNo as xItemNo, Max(MyDate) as xMyDate
> > >              from MyTable
> > >              where branch = ' '
> > >              Group By ItemNo)
> > > Select a.*
> > >    from MyTable a join x on    ItemNo = xItemNo
> > >                             and MyDate = xMyDate;
> > >
> > >
> > > Mit freunlichen Grüßen / Best regards
> > >
> > > Birgitta Hauser
> > >
> > > "Shoot for the moon, even if you miss, you'll land among the stars."
>
> > (Les
> > > Brown)
> > > "If you think education is expensive, try ignorance." (Derek Bok)
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "David Keck" < DavidKeck@xxxxxxxx>
> > > To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
> > > Sent: Thursday, November 02, 2006 23:55
> > > Subject: Re: Can this be done in SQL instead of traditional 'CHAIN'?
> > >
> > >
> > >
> > > You could try working in something along the lines of the code shown
> > > below.
> > > Maybe better is to build a work file (in advance of running your
> > > application) which has only one price record per item.  BTW, your
> boss
> > > might be able to code this application rather quickly using "CHAIN"
> ;-)
> > >
> > > select p.price
> > >       from Item i left join ItemPrice p
> > >       on i.item = p.item
> > >       where p.branch = ' ' and
> > >             p.date = (select max( x.date) from ItemPrice x
> > >                         where x.item = i.item and
> > >                               x.branch = ' ')
> > >
> > > David Keck
> > > NBTY, Inc
> > > Phone (631) 200-5809
> > > DavidKeck@xxxxxxxx
> > >
> > >
> > >
> > > Hello All;
> > > I am working on an embedded SQL code instead of using the
> traditional
> > > 'CHAIN' suggested by my boss. Now, I am stuck and was wondering if
> > anybody
> > > could point me in the right direction. I am dabbling in quite a few
> > files
> > > but I will try and keep it very relevant. Firstly, here is a little
> > subset
> > > of the data.
> > >
> > >    item branch dateeff price  a 1 20060108 15.18  a 3 20060108 38.16
>   a
> > 2
> > > 20060509 48.15  a
> > > 20061015 39.12  b 4 20060915 48.36  b 3 20060101 99.12  b
> > > 20060710 59.46  b 2 20051225 74.69  b
> > > 20051012 18.45  c 1 20050815 10.15  c
> > > 20061012 19.04  c 3 20061030 18.77  d 6 20051215 0.79  d 7 20061010
> > 19.65
> > > d
> > > 20061015 44.22  d 2 20050512 33.98
> > > Now, here is my difficulty. I am going to join the item later on
> with my
> > > grand query. From this file this is what I need...
> > > If I pick up an item 'a' I want my query to give the price with the
> > latest
> > > date in 'dateeff' and the 'branch' needs to be blank. So, for item
> 'a'
> > it
> > > would be '39.12'. That is too trivial. Now, if I want the pricing
> for
> > 'b'
> > > the latest date is 20060915 but the branch is not blank so I need to
> > look
> > > the next latest date and see if the branch associated with that date
> is
> > > blank and if it then I need to pick that pricing. So, it is 59.46.
> For
> > > item
> > > 'c', it is 19.04. For item 'd' it is 44.22.
> > >
> > > I hope I have made sense out of my problem. I have been working on
> this
> > > for
> > > the past four hours but to no avail. This is my whole query till
> > > now....(if
> > > anybody wants to make sense out of it..:-0))
> > >
> > > (My apologies for non-formatted SQL)...
> > >
> > > select itmmst.itmnum, itmdsc, unit, vendor, itmlgm,
> > REPLACE(substr(nccxpp,
> > > 9, 50), '@', ':') AS $$PATH40, desc, shortdescription, price, dteeff
> > from
> > > (select max(dteeff) as max_date, itmnum from ec30data.itmprc group
> by
> > > itmnum) maxresults, ec30data.itmmst itmmst left outer join
> > > ec30data.bwixrfbwixrf on
> > > itmmst.itmlgm = bwixrf.nccxcn left outer join testing.catviewcatview on
> > > itmmst.itmnum = catview.itmnum left outer join ec30data.itmprcitmprc on
> > > itmmst.itmnum = itmprc.itmnum WHERE (maxresults.max_date =
> itmprc.dteeff
> > > and
> > > itmprc.itmnum = maxresults.itmnum )
> > >
> > > Any help in this matter would be much appreciated.
> > >
> > > Thanks in advance.
> > >
> > > cheers,
> > >
> > > Jake.
> > > --
> > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> mailing
> > list
> > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/rpg400-l.
> > >
> > >
> > >
> > >
> > >
> > >
> >
> 
--------------------------------------------------------------------------------
>
> > >
> > >
> > > > --
> > > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> mailing
> > > list
> > > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/rpg400-l.
> > > >
> > > >
> > >
> > >
> > > --
> > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> mailing
> > list
> > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/rpg400-l.
> > >
> > >
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l .
>
>