|
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:
midrange.com
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.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.
>
>
--
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.
