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

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

If a record with the branch = ' '  per item occurs only once in the file,
then

SELECT item, price 
FROM file WHERE branch = ' '  AND item = :hostvariable

Regards,
Carel Teijgeler
 

*********** REPLY SEPARATOR  ***********

On 2-11-2006 at 15:42 Jake M wrote:

> 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.