Trying to join a max value to a subselect

[Sambo8]

Hi All,

My eyes are starting to go funny, below is my code the result without the subselect brings up the correct details so far, what I want to do is join the max(c.itmref_0) to the itmref_0 in the subselect, Hope this makes sense:-

select a.crscode_0,
a.body_0 || ' ' || a.paperlevel_0 || ' ' || a.paper_0 || ' ' || a.crstype_0 as COURSE_NAME,
decode (a.crstype_0, 'BOOSTER', 'ILT', 'CBA', 'ILT', 'CONDENSED', 'ILT', 'EXAM', 'ILT', 'EXTFEED', 'ILT', 'EXTREV', 'ILT', 'FEEDER', 'ILT', 'FULLTIME','ILT',
'HOMESTUDY', 'WBT', 'HSPLUS', 'WBT', 'HSUPGRADE', 'WBT', 'INTENSIVE', 'ILT',
'LINKEXAM', 'ILT', 'MOCK', 'ILT', 'QBREV', 'ILT', 'QUESTDAY', 'ILT', 'REFERRAL', 'ILT', 'RETREV', 'ILT', 'SKILLS', 'ILT', 'STDREV', 'ILT', 'TECH', 'ILT', 'VC', 'WBT', 'WEREV', 'ILT') as DELIVERY_METHOD,

b.genproduct_0, max(c.itmref_0), sub.blah
from bpplive.ybpcrsinst a, bpplive.ybpcimater b, bpplive.sb_transgender c,
(select baspri_0 as blah, itmref_0
from bpplive.itmsales
group by itmref_0, baspri_0) sub
where a.body_0 in ('ACCA', 'CIMA')
and a.crsactive_0 = 2
and a.crscode_0 = 'IP9F16LOI02'
and a.crscode_0 = b.crscode_0
and b.crscode_0 = c.crscode_0
and b.sitting_0 = c.sitting_0
and a.paper_0 = c.paper_0
and b.genproduct_0 = c.genproduct_0
and max(c.itmref_0) = sub.itmref_0
group by a.crscode_0,
a.body_0 || ' ' || a.paperlevel_0 || ' ' || a.paper_0 || ' ' || a.crstype_0,
decode (a.crstype_0, 'BOOSTER', 'ILT', 'CBA', 'ILT', 'CONDENSED', 'ILT',

'EXAM', 'ILT', 'EXTFEED', 'ILT', 'EXTREV', 'ILT', 'FEEDER', 'ILT', 'FULLTIME','ILT', 'HOMESTUDY', 'WBT', 'HSPLUS', 'WBT', 'HSUPGRADE', 'WBT', 'INTENSIVE', 'ILT', 'LINKEXAM', 'ILT', 'MOCK', 'ILT', 'QBREV', 'ILT', 'QUESTDAY', 'ILT', 'REFERRAL', 'ILT', 'RETREV', 'ILT', 'SKILLS', 'ILT', 'STDREV', 'ILT', 'TECH', 'ILT', 'VC', 'WBT', 'WEREV', 'ILT'),

b.genproduct_0, sub.blah


many thanks in advance.

Sam

 

[SantaMufasa]

Sam,

Please re-read your post as though you are we...knowing nothing more about your need than what you have written there.

Now, with that in mind, please consider my questions:

1) When you add in the subquery, are you receiving:
-- Syntax errors (Oracle believes you broke a semantic rule)
-- Run-time errors (Oracle encountered problems while executing)
-- Logic errors (i.e., results are not what you wanted)

2) What results are your receiving under Scenario 1 (without the subquery) and Scenario 2 (with the subquery).

We need as much information as possible when trying to resolve an obscure scenario.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[rac2]

Good advice Dave.

At the risk of encouraging sloppy posts may I observe that there is an aggregate function in the WHERE clause. Is that allowed by Oracle SQL syntax, or any SQL?

Possibly max(c.itmref_0) should be in the subquery, given an alias and referred to in the JOIN conditions by the alias. I mean, Im just taking a cursory shot at some possibilities.

Say Dave, is it Christmas in Utah?

 

[SantaMufasa]

Well, if by "Christmas in Utah" you are wondering if it's still winter here, then judging by the 6 inches of snow that fell on my yard Monday morning (and another 1/2 inch dusting yesterday), then, yes, it is still "Christmas in Utah". Of course, wherever "Santa" is, it is also typically "Christmas".

Is (an aggregate function in the WHERE clause) allowed by Oracle SQL syntax, or any SQL?

Although it is syntactically reasonable to have an aggregate function in the WHERE clause, we typically find it more useful to use the "...HAVING <aggregate expression> <relationship conditional>..."

Frankly, though, in this case, you have taken a "longer" look at Sam's issue than I have, because I was just waiting for Sam's reply to my questions before I took a more in-depth look at his need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Sambo8]

Hi Both,

Apologies for the sloppy post, think the brain had given up was trying to work out a way to force the join on the maximum. You both gave me food for thought had completely forgotten the having clause. In case of interest the final working query is below:

select COURSE_NAME, DELIVERY_METHOD, sum(samsum)
FROM(select
a.crscode_0, a.body_0 || ' ' || a.paperlevel_0 || ' ' || a.paper_0 || ' ' || a.crstype_0 as COURSE_NAME,
DECODE (a.crstype_0, 'BOOSTER', 'ILT', 'CBA', 'ILT', 'CONDENSED', 'ILT', 'EXAM', 'ILT', 'EXTFEED', 'ILT', 'EXTREV', 'ILT', 'FEEDER', 'ILT', 'FULLTIME','ILT', 'HOMESTUDY', 'WBT', 'HSPLUS', 'WBT', 'HSUPGRADE', 'WBT', 'INTENSIVE', 'ILT', 'LINKEXAM', 'ILT', 'MOCK', 'ILT', 'QBREV', 'ILT', 'QUESTDAY', 'ILT', 'REFERRAL', 'ILT', 'RETREV', 'ILT', 'SKILLS', 'ILT', 'STDREV', 'ILT', 'TECH', 'ILT', 'VC', 'WBT', 'WEREV', 'ILT') as DELIVERY_METHOD, sub.sam as samsum
from bpplive.ybpcrsinst a, bpplive.ybpcimater b,
(select x.genproduct_0, max(y.itmref_0) as itm, max(y.baspri_0) as sam, x.crscode_0 as crscode_0, x.sitting_0 as sitting_0, x.paper_0 as paper_0 from bpplive.sb_transgender x, bpplive.itmsales y
where x.itmref_0 = y.itmref_0
group by x.genproduct_0, x.crscode_0, x.sitting_0, x.paper_0
having max(y.itmref_0) < 'ZZZZZZZZZZZZZZZZ') sub
where a.body_0 in ('ACCA', 'CIMA')
and a.crsactive_0 = 2
and a.crscode_0 = 'IP9F16LOI02'
and a.crscode_0 = b.crscode_0
and b.crscode_0 = sub.crscode_0
and b.sitting_0 = sub.sitting_0
and a.paper_0 = sub.paper_0
and b.genproduct_0 = sub.genproduct_0
group by a.crscode_0,
a.body_0 || ' ' || a.paperlevel_0 || ' ' || a.paper_0 || ' ' ||
a.crstype_0,
decode (a.crstype_0, 'BOOSTER', 'ILT', 'CBA', 'ILT', 'CONDENSED', 'ILT', 'EXAM', 'ILT', 'EXTFEED', 'ILT', 'EXTREV', 'ILT', 'FEEDER', 'ILT', 'FULLTIME','ILT',
'HOMESTUDY', 'WBT', 'HSPLUS', 'WBT', 'HSUPGRADE', 'WBT', 'INTENSIVE', 'ILT', 'LINKEXAM', 'ILT', 'MOCK', 'ILT', 'QBREV', 'ILT', 'QUESTDAY', 'ILT', 'REFERRAL', 'ILT', 'RETREV', 'ILT', 'SKILLS', 'ILT', 'STDREV', 'ILT', 'TECH', 'ILT', 'VC', 'WBT', 'WEREV', 'ILT'),
b.genproduct_0,
sub.sam)
GROUP By COURSE_NAME, DELIVERY_METHOD

Many thanks

Sam