OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL (9i, 10g )

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

SQL [message #619384]

Tue, 22 July 2014 03:08

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

I want matrix query

Create Table Booking(Booking_Date Date,Booking_No Char(12),Temp_Shipper Char(5),FT20 number,FT40 number,HC40 number);

insert into Booking('05-JUL-2011','201105070001','00001',1,0,0);
insert into Booking('06-JUL-2011','201106070001','00002',0,3,0);
insert into Booking('08-JUL-2011','201106070001','00001',0,0,2);

insert into Booking('05-AUG-2012','201205080001','00004',0,0,2);
insert into Booking('06-SEP-2012','201206090001','00002',0,1,0);
insert into Booking('08-JAN-2013','201308010001','00005',0,1,2);
insert into Booking('08-JUN-2013','201308060001','00017',1,0,2);
insert into Booking('01-JUL-2013','201301070001','00024',0,0,2);

insert into Booking('10-JUL-2013','201310070001','00025',0,2,0);
insert into Booking('22-AUG-2013','201322080001','00002',3,0,0);
insert into Booking('12-FEB-2014','201412020001','00024',0,1,2);

I want matrix query

Query which I tried

select To_Char(Booking_Date,'YYYY'),
C.Temp_Shipper, SUM((Nvl(C.CONTAINER_QTY_20FT,0) + Nvl(C.CONTAINER_QTY_40FT,0) * 2 + Nvl(C.CONTAINER_QTY_40HC,0) * 2
+Nvl(C.CONTAINER_QTY_REEFER,0) * 2)) TEUS
from Cargo_Booking_Header C
Where
C.Cancel_Yn='N'
and C.Booking_Date Between '01-JUL-2011' and '30-JUN-2014'

Group By To_Char(Booking_Date,'YYYY'),Temp_Shipper
Sample what I required.
I want Yearly total

Temp Shipper     Year  2011-2012  JUNe         2012-2013 JUNE                     2013-2014
00024                      10                   0                                  3
00002                      0                     2                                 0

[Edit MC: add code tags]

[Updated on: Tue, 22 July 2014 03:40] by Moderator

Report message to a moderator

Re: SQL [message #619390 is a reply to message #619384]

Tue, 22 July 2014 03:41

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

It is not clear what your result columns are and what they mean, ca you explain?

Also '05-JUL-2011' is a string not a date:

SQL> select to_date('05-JUL-2011') from dual;
select to_date('05-JUL-2011') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Always use TO_DATE and for a test case with DATE columns always use numeric values (format DD/MM/YYYY for instance), we have not all the same language.

[Updated on: Tue, 22 July 2014 03:44]

Report message to a moderator

Re: SQL [message #619394 is a reply to message #619384]

Tue, 22 July 2014 03:54

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Also,

1. The keyword VALUES is missing in your insert statements.

SQL> insert into Booking (to_date('05-JUL-2011','DD-MON-YYYY'),'201105070001','00001',1,0,0);
insert into Booking (to_date('05-JUL-2011','DD-MON-YYYY'),'201105070001','00001',1,0,0)
                            *
ERROR at line 1:
ORA-00917: missing comma


SQL> insert into Booking VALUES (to_date('05-JUL-2011','DD-MON-YYYY'),'201105070001','00001',1,0,0);

1 row created.

2. In your query you have used "Cargo_Booking_Header" table, but you have provided create statement for "booking" table which is not used anywhere in your query.

[Updated on: Tue, 22 July 2014 03:58]

Report message to a moderator

Previous Topic:	SubQuery Problem
Next Topic:	To_number on only number fields

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 11:31:15 CDT 2024