Home » SQL & PL/SQL » SQL & PL/SQL » select value that doesn;t match teh outerjoin constant
select value that doesn;t match teh outerjoin constant [message #384690] Wed, 04 February 2009 21:24 Go to next message
tico03
Messages: 2
Registered: February 2009
Location: sydney
Junior Member
I have created 2 tables for test case as below

SQL> 
SQL> CREATE TABLE CUSTOMER( ID  VARCHAR2(10),FULL_NAME VARCHAR2(25));
 
Table created
SQL> CREATE TABLE ORDERS ( CUSTOMER_ID VARCHAR2(10), ORDER_NO VARCHAR2(10),ORDER_YR VARCHAR2(4), ORDER_SEQ NUMBER(6));
 
Table created
 
SQL> 
INSERT INTO CUSTOMER VALUES ( '903', 'Roger Smith');
INSERT INTO CUSTOMER VALUES ( '904', 'Alan Jones');
INSERT INTO CUSTOMER VALUES ( '905', 'Roger Federer');
INSERT INTO CUSTOMER VALUES ( '906', 'Sarina Williams');

INSERT INTO ORDERS VALUES ( '903', '101','2000',1);
INSERT INTO ORDERS VALUES ( '903', '102','2001',2);
INSERT INTO ORDERS VALUES ( '904', '103','2000',2);
INSERT INTO ORDERS VALUES ( '904', '104','2005',4);
INSERT INTO ORDERS VALUES ( '905', '105','2006',1);



My select statement is as below

SELECT CUSTOMER.ID,CUSTOMER.FULL_NAME,O1.ORDER_NO,O1.ORDER_YR,O2.ORDER_NO,O2.ORDER_YR,O3.ORDER_NO,O3.ORDER_YR,O4.ORDER_NO,O4.ORDER_YR
from CUSTOMER, ORDERS O1, ORDERS O2, ORDERS O3,ORDERS O4
WHERE CUSTOMER.ID = O1.CUSTOMER_ID  AND
O1.CUSTOMER_ID = O2.CUSTOMER_ID (+) AND
O1.CUSTOMER_ID = O3.CUSTOMER_ID (+) AND
O1.CUSTOMER_ID = O4.CUSTOMER_ID (+) AND
O1.ORDER_SEQ (+)= 1 AND 
O2.ORDER_SEQ (+)= 2  AND
O3.ORDER_SEQ(+) = 3   AND
O4.ORDER_SEQ (+)= 4   ;



Result as below
ID         FULL_NAME                 ORDER_NO   ORDER_YR ORDER_NO   ORDER_YR ORDER_NO   ORDER_YR ORDER_NO   ORDER_YR
---------- ------------------------- ---------- -------- ---------- -------- ---------- -------- ---------- --------
903        Roger Smith               101        2000     102        2001                                    
905        Roger Federer             105        2006                                                        
 
SQL> 


Now i didn't want Sarina Williams so thats ok. But is there any solution if I want Alan Jones in my result with all his order numbers and order years.
I am looking for all teh order numbers and years in one line. And when i save it in excel i would like them in seperate columns. Thanks in Advance.
Re: select value that doesn;t match teh outerjoin constant [message #384694 is a reply to message #384690] Wed, 04 February 2009 21:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Use pivot query instead of those multiple joins.
It is described e.g. in this server here: http://www.orafaq.com/wiki/PIVOT
Re: select value that doesn;t match teh outerjoin constant [message #384727 is a reply to message #384690] Thu, 05 February 2009 00:08 Go to previous messageGo to next message
tico03
Messages: 2
Registered: February 2009
Location: sydney
Junior Member
Thanks for the help. I could not experiment with pivot as i am using 10g. i tried the alternative but i was hoping to getget all the orders for each customer in one line and in different coulmns. Please find the test code below that i have tried.

SQL> SELECT * FROM (SELECT CUSTOMER.ID,CUSTOMER.FULL_NAME, decode(ORDERS.ORDER_SEQ ,1,ORDERS.ORDER_NO), decode(ORDERS.ORDER_SEQ ,1,ORDERS.ORDER_YR),
  2  decode(ORDERS.ORDER_SEQ ,2,ORDERS.ORDER_NO), decode(ORDERS.ORDER_SEQ ,2,ORDERS.ORDER_YR),
  3  decode(ORDERS.ORDER_SEQ ,3,ORDERS.ORDER_NO), decode(ORDERS.ORDER_SEQ ,3,ORDERS.ORDER_YR),
  4  decode(ORDERS.ORDER_SEQ ,4,ORDERS.ORDER_NO), decode(ORDERS.ORDER_SEQ ,4,ORDERS.ORDER_YR)
  5  from CUSTOMER, ORDERS
  6  WHERE CUSTOMER.ID = ORDERS.CUSTOMER_ID
  7  GROUP BY CUSTOMER.ID,CUSTOMER.FULL_NAME,ORDERS.ORDER_NO,ORDERS.ORDER_YR,ORDERS.ORDER_SEQ)
  8  ORDER BY 1;
 
ID         FULL_NAME                 DECODE(ORDERS.ORDER_SEQ,1,ORDE DECODE(ORDERS.ORDER_SEQ,1,ORDE DECODE(ORDERS.ORDER_SEQ,2,ORDE DECODE(ORDERS.ORDER_SEQ,2,ORDE DECODE(ORDERS.ORDER_SEQ,3,ORDE DECODE(ORDERS.ORDER_SEQ,3,ORDE DECODE(ORDERS.ORDER_SEQ,4,ORDE DECODE(ORDERS.ORDER_SEQ,4,ORDE
---------- ------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
903        Roger Smith               101                            2000                                                                                                                                                                                      
903        Roger Smith                                                                             102                            2001                                                                                                                        
904        Alan Jones                                                                              103                            2000                                                                                                                        
904        Alan Jones                                                                                                                                                                                                          104                            2005
905        Roger Federer             105                            2006                                                                                                                                                                                      
 


Any sugggestions? Thanks
Re: select value that doesn;t match teh outerjoin constant [message #384744 is a reply to message #384727] Thu, 05 February 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thanks for the help. I could not experiment with pivot as i am using 10g.

BEFORE replying first read the link.
The WORD pivot has existed far before the FEATURE was introduced in 11g.

And reduce the size of the columns in order to shrink the lines.

Regards
Michel
Re: select value that doesn;t match teh outerjoin constant [message #384760 is a reply to message #384727] Thu, 05 February 2009 01:24 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
tico03 wrote on Thu, 05 February 2009 07:08
Thanks for the help. I could not experiment with pivot as i am using 10g. i tried the alternative but i was hoping to getget all the orders for each customer in one line and in different coulmns. Please find the test code below that i have tried.
Any sugggestions?

Yes, read that link again and more precisely.
You may find these differences:
- SUM aggregate function is missing on DECODED expressions in the SELECT clause
- so your GROUP BY clause contains unnecessarily many columns


Previous Topic: SQL based on condition
Next Topic: insufficient privileges for SYSDBA ?
Goto Forum:
  


Current Time: Thu Dec 08 20:13:19 CST 2016

Total time taken to generate the page: 0.12326 seconds