OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL PROBLEM!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL PROBLEM! [message #9998]

Fri, 19 December 2003 07:59

Gopala K Tasupalli
Messages: 14
Registered: October 2002

Junior Member

Hi Friends,

I have a problem, i wrote a SQL and i am getting the followin result,

ZIP TERR ADDR_TYPE
--------------------------
90025 V2HE Shipping
90403 V2HE Storage

my problem is instead of 2 records i shoudl get only one record like this

TERR SHIPPINGZIP STORAGEZIP
V2HE 90025 90403

Can any body help me? or if you need further details please let me know?

Thanks in Advance
Gopal

Report message to a moderator

Re: SQL PROBLEM! [message #9999 is a reply to message #9998]

Fri, 19 December 2003 08:39

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

SELECT   t.terr
,        MAX(DECODE(t.addr_type,'Shipping',t.zip)) shippingzip
,        MAX(DECODE(t.addr_type,'Storage',t.zip))  storagezip
FROM     t
GROUP BY t.terr

HTH,

A.

Report message to a moderator

Re: SQL PROBLEM! [message #10000 is a reply to message #9999]

Fri, 19 December 2003 10:05

Gopala K Tasupalli
Messages: 14
Registered: October 2002

Junior Member

Hi

Thank you verymuch for the reply, but when use the query given by you i am getting both the shippingzip and storezip fields as BLANK, can you tell me why?

Thanks
Gopal

Report message to a moderator

Re: SQL PROBLEM! [message #10001 is a reply to message #10000]

Fri, 19 December 2003 10:39

sverch
Messages: 582
Registered: December 2000

Senior Member

Try TRIM:
SELECT t.terr, MAX(DECODE(TRIM(t.addr_type),'Shipping',TRIM(t.zip))) shippingzip,
MAX(DECODE(TRIM(t.addr_type),'Storage',TRIM(t.zip))) storagezip
FROM t GROUP BY t.terr

Report message to a moderator

Re: SQL PROBLEM! [message #10025 is a reply to message #10001]

Mon, 22 December 2003 23:23

Srinivas
Messages: 138
Registered: June 2000

Senior Member

CREATE TABLE table_name (terr VARCHAR2(10),zip VARCHAR2(10),addr_type VARCHAR2(20))

INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V2HE', '90025', 'Shipping');
INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V2HE', '90403', 'Storage');
INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V3HE', '90025', 'Shipping');
INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V3HE', '90403', 'Storage');
INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V4HE', '90000', 'Shipping');
INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V4He', '80000', 'Storage');
INSERT INTO TABLE_NAME ( TERR, ZIP, ADDR_TYPE ) VALUES (
'V5HE', '09808', 'Shipping');

SELECT terr,SUM(shippingzip)shippingzip,SUM(storagezip) storagezip
FROM
(SELECT terr,
DECODE(addr_type,'Shipping',zip,NULL) shippingzip,
DECODE(addr_type,'Storage',zip,NULL) storagezip
FROM
table_name
)
GROUP BY terr

This works IFF the ADDR_TYPE IS shipping or storage.

HTH
Srini

Report message to a moderator

Previous Topic:	to_date format
Next Topic:	RETURNING in EXECUTE IMMEDIATE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:40:30 CDT 2024