Home » SQL & PL/SQL » SQL & PL/SQL » MAX Aggregate function (9.2.0.5.0)
MAX Aggregate function [message #378531] Tue, 30 December 2008 21:31 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
I've an ITEM table that contains a list of items ordered.(per ITEMPARENTID = 0). Here is the SQL for that

CREATE TABLE ITEM
(REGION VARCHAR2(15),
ITEMID NUMBER(4),
ITEMPRDID VARCHAR2(25),
SHIPDATE DATE,
ITEMPARENTID NUMBER(4)
)

INSERT INTO ITEM VALUES ('ID','1','PRD001',TRUNC(SYSDATE),'0')
INSERT INTO ITEM VALUES ('ID','2','PRD002',TRUNC(SYSDATE),'1')
INSERT INTO ITEM VALUES ('ID','3','PRD003',TRUNC(SYSDATE),'1')
INSERT INTO ITEM VALUES ('ID','4','PRD004',TRUNC(SYSDATE),'1')
INSERT INTO ITEM VALUES ('ID','5','PRD005',TRUNC(SYSDATE),'1')
INSERT INTO ITEM VALUES ('ID','6','PRD006',TRUNC(SYSDATE),'1')



I also have an ITEMATTR table that stores the attributes of certain items.

CREATE TABLE ITEMATTR
(
REGION VARCHAR2(15),
ITEMID NUMBER(4),
ATTRID VARCHAR2(15),
ATTRDATE DATE
)

INSERT INTO ITEMATTR VALUES ('ID','2','INSDATE',TRUNC(SYSDATE))
INSERT INTO ITEMATTR VALUES ('ID','4','LAUNCHDATE',TRUNC(SYSDATE-4))
INSERT INTO ITEMATTR VALUES ('ID','6','ENDDATE',TRUNC(SYSDATE-6))



I have three Lookup tables for corresponding to the main item table.
CREATE TABLE LOOKUP1
(
PRDID VARCHAR2(25),
PRD_DESCR VARCHAR2(30),
PRD_CONTRACT VARCHAR2(10)
)

INSERT INTO LOOKUP1 VALUES ('PRD002','Product 2',0)


CREATE TABLE LOOKUP2
(
PRDID VARCHAR2(25),
PRD_DESCR VARCHAR2(30),
PRD_CONTRACT VARCHAR2(10)
)
INSERT INTO LOOKUP2 VALUES ('PRD004','Product 4',0)



CREATE TABLE LOOKUP3
(
PRDID VARCHAR2(25),
PRD_DESCR VARCHAR2(30),
PRD_CONTRACT VARCHAR2(10)
)
INSERT INTO LOOKUP3 VALUES ('PRD006','Product 6',0)



Now, I'm running an SQL like this

SELECT 
MAX(B.PRDID),MAX(B.PRD_DESCR),MAX(B.PRD_CONTRACT),
MAX(C.PRDID),MAX(C.PRD_DESCR),MAX(C.PRD_CONTRACT),
MAX(C.PRDID),MAX(C.PRD_DESCR),MAX(C.PRD_CONTRACT),
MAX(DECODE(B.PRDID,NULL,NULL,E.ATTRDATE)),
MAX(DECODE(D.PRDID,NULL,NULL,E.ATTRDATE))
FROM ITEM A
LEFT JOIN LOOKUP1 B ON A.ITEMPRDID=B.PRDID
LEFT JOIN LOOKUP2 C ON A.ITEMPRDID=C.PRDID
LEFT JOIN LOOKUP3 D ON A.ITEMPRDID=D.PRDID
LEFT JOIN ITEMATTR E ON A.REGION=E.REGION AND A.ITEMID=E.ITEMID AND E.ATTRID IN 
('INSDATE','ENDDATE')
WHERE
A.ITEMPARENTID = '1'



My aim is to get the matching values for a product from the lookup tables in a single SQL.(max of item id)

In this case it works, because, I'm hitting only one row against each of the lookup tables.

However, if I do

INSERT INTO LOOKUP2 VALUES ('PRD003','Product 3',2)



and execute the same SQL, I get erroneous results.

I could figure out that the problem is due to MAX.

What is the other aggregate function that I can use in this case to get the desired result?

More generally, is it possible to achieve this in a single SQL if I hit multiple rows against the lookup table? One point here is, a same product cannot be present across LOOKUP1, LOOKUP2 and LOOKUP3 tables.It can be in only one of these tables. That is why, I chose MAX but hitting a different problem when doing this way.


[Mod-Edit: Frank removed font-tag. Font was too small to be readable (at least for my eyes)]

[Updated on: Wed, 31 December 2008 00:24] by Moderator

Report message to a moderator

Re: MAX Aggregate function [message #378743 is a reply to message #378531] Thu, 01 January 2009 02:04 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
The result execution is explained in the attachment.

The SQL executed in both the cases is

SELECT 
MAX(B.PRDID),MAX(B.PRD_DESCR),MAX(B.PRD_CONTRACT),
MAX(C.PRDID),MAX(C.PRD_DESCR),MAX(C.PRD_CONTRACT),
MAX(D.PRDID),MAX(D.PRD_DESCR),MAX(D.PRD_CONTRACT),
MAX(DECODE(B.PRDID,NULL,NULL,E.ATTRDATE)),
MAX(DECODE(D.PRDID,NULL,NULL,E.ATTRDATE))
FROM ITEM A
LEFT JOIN LOOKUP1 B ON A.ITEMPRDID=B.PRDID
LEFT JOIN LOOKUP2 C ON A.ITEMPRDID=C.PRDID
LEFT JOIN LOOKUP3 D ON A.ITEMPRDID=D.PRDID
LEFT JOIN ITEMATTR E ON A.REGION=E.REGION AND A.ITEMID=E.ITEMID AND E.ATTRID IN 
('INSDATE','ENDDATE')
WHERE
A.ITEMPARENTID = '1'





  • Attachment: results.jpg
    (Size: 88.86KB, Downloaded 102 times)
Re: MAX Aggregate function [message #378744 is a reply to message #378743] Thu, 01 January 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't explain what is the meaning of each result column and what should be the result with the data you gave.

Regards
Michel
Re: MAX Aggregate function [message #378745 is a reply to message #378531] Thu, 01 January 2009 02:51 Go to previous message
srraajesh
Messages: 63
Registered: May 2005
Member
The attachments contains the results obtained during the first execution and during the second execution. It also includes a callout that explains the column that is fetching incorrect result and the expected one.

Anyway,

Here is what each column means.
MAX(B.PRDID),MAX(B.PRD_DESCR),MAX(B.PRD_CONTRACT),
MAX(C.PRDID),MAX(C.PRD_DESCR),MAX(C.PRD_CONTRACT),
MAX(D.PRDID),MAX(D.PRD_DESCR),MAX(D.PRD_CONTRACT),
MAX(DECODE(B.PRDID,NULL,NULL,E.ATTRDATE)),
MAX(DECODE(D.PRDID,NULL,NULL,E.ATTRDATE))


I need to fetch the rows matching across the lookup tables and take the description, contract duration pertaining to the matching ID. The driving table for this purpose is ITEM table.

Since, I need all the matches across the three lookup tables in a single row (pivot), I'm using MAX to get the desired output.

It works like a charm when I hit only one row across the three lookup tables in my left join.

However, if I hit two rows across a look up table by doing this

INSERT INTO LOOKUP2 VALUES ('PRD003','Product 3',2)


the results are incorrect.

I figured out the problem is due to MAX as it takes the MAX of PRD_CONTRACT in the Lookup2 table, but it has to take the values pertaining to the matching PRDID.

So, I need to achieve the correct pivoting, with LEFT JOIN. Not sure if I can use MAX.

I can always do a quick work around for this by doing the following

SELECT 
MAX(B.PRDID),MAX(B.PRD_DESCR),MAX(B.PRD_CONTRACT),
MAX(C.PRDID),MAX(C.PRD_DESCR),MAX(C.PRD_CONTRACT),
MAX(D.PRDID),MAX(D.PRD_DESCR),MAX(D.PRD_CONTRACT),
MAX(DECODE(B.PRDID,NULL,NULL,E.ATTRDATE)),
MAX(DECODE(D.PRDID,NULL,NULL,E.ATTRDATE))
FROM ITEM A
LEFT JOIN LOOKUP1 B ON A.ITEMPRDID=B.PRDID
LEFT JOIN LOOKUP2 C ON A.ITEMPRDID=C.PRDID AND A.ITEMID IN (SELECT MAX(B1.ITEMID) FROM ITEM B1, LOOKUP2 WHERE B1.ITEMPRDID=LOOKUP2.PRDID AND B1.ITEMPARENTID = '1'
)
LEFT JOIN LOOKUP3 D ON A.ITEMPRDID=D.PRDID
LEFT JOIN ITEMATTR E ON A.REGION=E.REGION AND A.ITEMID=E.ITEMID AND E.ATTRID IN 
('INSDATE','ENDDATE')
WHERE
A.ITEMPARENTID = '1'


But, this would use the 'ITEM' table twice.

A WITH .. SELECT can also help here. But, I want to achive this by using all these tables only once.

Thanks..

[Updated on: Thu, 01 January 2009 02:53]

Report message to a moderator

Previous Topic: Is it possible.
Next Topic: How to insert one column from one table to another?
Goto Forum:
  


Current Time: Mon Dec 05 09:00:04 CST 2016

Total time taken to generate the page: 0.09655 seconds