Hi Tom,
I have a similar situation here but cannot get any better way to write this.
CREATE TABLE ARCH
(
FILE_ID NUMBER,
ACTIVITY_ID NUMBER,
SALE_START VARCHAR2(50 BYTE),
ACTIVITY_START VARCHAR2(50 BYTE),
ACTIVITY_END VARCHAR2(50 BYTE),
ITEM_CODE_CUST VARCHAR2(50 BYTE),
ADVERTISED VARCHAR2(25 BYTE),
DIVISION VARCHAR2(50 BYTE),
ACTIVITY_RETAIL VARCHAR2(50 BYTE),
ACTIVITY_MULT VARCHAR2(50 BYTE)
)
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2505878, '08/22/2009', '08/14/2009', '08/20/2009',
'00514757', 'Y', '005', '2', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2505879, '08/22/2009', '08/14/2009', '08/20/2009',
'00514757', 'Y', '005', '2', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2541390, '08/22/2009', '08/14/2009', '08/20/2009',
'00514757', 'Y', '005', '1', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2541391, '08/22/2009', '08/14/2009', '08/20/2009',
'00514757', 'Y', '005', '2', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2541392, '08/22/2009', '08/14/2009', '08/20/2009',
'00514757', 'Y', '005', '3', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2505998, '08/15/2009', '08/24/2009', '08/31/2009',
'00515555', 'Y', '006', '5', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2505493, '08/15/2009', '08/24/2009', '08/31/2009',
'00515555', 'Y', '006', '7', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2541542, '08/15/2009', '08/24/2009', '08/31/2009',
'00515555', 'Y', '006', '8', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2541874, '08/15/2009', '08/24/2009', '08/31/2009',
'00515555', 'Y', '006', '3', '1');
Insert into ARCH
(FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END,
ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
Values
(217, 2541762, '08/15/2009', '08/24/2009', '08/31/2009',
'00515555', 'Y', '006', '5', '1');
COMMIT;
SELECT a.file_id,
a.sale_start,
a.activity_start,
a.activity_end,
a.item_code_cust,
a.division,
(SELECT MAX (DISTINCT (NVL (b.advertised, 'No')))
FROM arch b
WHERE b.file_id = a.file_id
AND NVL (b.sale_start, 'NULL') = NVL (a.sale_start, 'NULL')
AND NVL (b.activity_start, 'NULL') = NVL (a.activity_start, 'NULL')
AND NVL (b.activity_end, 'NULL') = NVL (a.activity_end, 'NULL')
AND b.item_code_cust = a.item_code_cust
AND b.division = a.division) advertised,
(SELECT activity_id
FROM (SELECT c.activity_id,
ROW_NUMBER () OVER (ORDER BY (c.activity_retail / c.activity_mult)) rnk
FROM arch c
WHERE NVL (c.sale_start, 'NULL') = NVL (a.sale_start, 'NULL')
AND NVL (c.activity_start, 'NULL') = NVL (a.activity_start, 'NULL')
AND NVL (c.activity_end, 'NULL') = NVL (a.activity_end, 'NULL')
AND c.item_code_cust = a.item_code_cust
AND c.division = a.division
AND UPPER (NVL (c.advertised, 'N')) = a.advertised
AND c.file_id = 217)
WHERE rnk = 1) primary_id,
COUNT (*)
FROM arch a
WHERE a.file_id = 217
GROUP BY a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division
HAVING COUNT (*) > 1;
ORA-00904: "A"."ADVERTISED": invalid identifier
I am not sure how to use inner join in a scenario like this.
Need your 10 cents please !!!
Thanks,
Raj
August 07, 2009 - 9:20 am UTC
looking at:
(SELECT MAX (DISTINCT (NVL (b.advertised, 'No')))
FROM arch b
WHERE b.file_id = a.file_id
AND NVL (b.sale_start, 'NULL') = NVL (a.sale_start, 'NULL')
AND NVL (b.activity_start, 'NULL') = NVL (a.activity_start,
'NULL')
AND NVL (b.activity_end, 'NULL') = NVL (a.activity_end, 'NULL')
AND b.item_code_cust = a.item_code_cust
AND b.division = a.division) advertised,
that is just saying "for this FILE_ID (217), and every row that has the same sale_start, activity_start, activity_end, item_code_cust, division - find the max advertised"
That is more readily expressed as:
max( nvl(advertised,'No') ) over
(partition by sale_start, activity_start, activity_end,
item_code_cust, division) advertised,
I'm making the "reasonable" assumption that item_code_cust and division are in real life NOT NULL - since you didn't use NVL() on them. If they are NULLABLE - we'd have to modify that slightly be to exactly like yours - but probably - that is it.
break the data for file_id = 217 up by "sale_start, activity_start, activity_end, item_code_cust, division" and for every row in that partition - assign the max(advertised) value.
Next, in looking at:
(SELECT activity_id
FROM (SELECT c.activity_id,
ROW_NUMBER () OVER (ORDER BY (c.activity_retail /
c.activity_mult)) rnk
FROM arch c
WHERE NVL (c.sale_start, 'NULL') = NVL (a.sale_start,
'NULL')
AND NVL (c.activity_start, 'NULL') = NVL
(a.activity_start, 'NULL')
AND NVL (c.activity_end, 'NULL') = NVL (a.activity_end,
'NULL')
AND c.item_code_cust = a.item_code_cust
AND c.division = a.division
AND UPPER (NVL (c.advertised, 'N')) = a.advertised
AND c.file_id = 217)
WHERE rnk = 1) primary_id,
You are basically saying:
o break the data up by sale_start, activity_start, activity_end, item_code_cust, division and ADVERTISED WITHOUT REGARDS TO CASE
o find the first value of activity id after sorting by a number divided by a string (ouch, ouch - i hate that, I hate your table actually, you are using strings for numbers, bad idea, you will get burned hugely by that as will the customers of your application)
That is more readily stated by:
11 first_value(activity_id) over
12 ( partition by sale_start, activity_start, activity_end, item_code_cust, division, upper(advertised)
13 order by activity_retail/TO_NUMBER(activity_mult)) primary_id
So, I think your query is probably:
ops$tkyte%ORA10GR2> select x.*, count(*)
2 from (
3 SELECT a.file_id,
4 a.sale_start,
5 a.activity_start,
6 a.activity_end,
7 a.item_code_cust,
8 a.division,
9 max( nvl(advertised,'No') ) over
10 (partition by sale_start, activity_start, activity_end, item_code_cust, division) advertised,
11 first_value(activity_id) over
12 ( partition by sale_start, activity_start, activity_end, item_code_cust, division, upper(advertised)
13 order by activity_retail/TO_NUMBER(activity_mult)) primary_id
14 FROM arch a
15 WHERE a.file_id = 217
16 ) x
17 GROUP BY x.file_id, x.sale_start, x.activity_start, x.activity_end, x.item_code_cust, x.division, x.advertised, x.advertised, x.primary_id
18 HAVING COUNT (*) > 1;
FILE_ID SALE_START ACTIVITY_S ACTIVITY_E ITEM_CODE_ DIV ADV PRIMARY_ID COUNT(*)
------- ---------- ---------- ---------- ---------- --- --- ---------- ----------
217 08/22/2009 08/14/2009 08/20/2009 00514757 005 Y 2541390 5
217 08/15/2009 08/24/2009 08/31/2009 00515555 006 Y 2541874 5