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

Hi,

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

"If you think education is expensive, try ignorance." (Derek Bok)

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.catview catview on
itmmst.itmnum = catview.itmnum left outer join ec30data.itmprc itmprc 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.