|
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...
midrange.com
... 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.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.
>
>
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
