Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian Product query
Cartesian Product query [message #443205] Sat, 13 February 2010 11:29 Go to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi

I need some suggestions regarding getting the desired result.

I have two tables tab1 and tab2.

tab2 data:

C_number      Yr   month  amount
-------       --   -----  ------
c1            2009  1     100
c2            2009  2     50


tab2 data:
 
Yr       month 
-----    -----
2009     1
2009     2
2009     4
2009     5
2009     6
2009     7
2009     8
2009     9
2009     10
2009     11
2009     12


and i want the output using the table tab1 and tab2 like below

C_number      Yr   month  amount 
-------       --   -----  -----
c1            2009  1     100
c1            2009  2       0
c1            2009  3       0
c1            2009  4       0
c1            2009  5       0
c1            2009  6       0
c1            2009  7       0
c1            2009  8       0
c1            2009  9       0
c1            2009  10      0
c1            2009  11      0
c1            2009  12      0
c2            2009  1      50
c2            2009  2       0
c2            2009  3       0 
c2            2009  4       0
c2            2009  5       0
c2            2009  6       0
c2            2009  7       0
c2            2009  8       0
c2            2009  9       0
c2            2009  10      0
c2            2009  11      0
c2            2009  12      0
--------------------------------


Please suggest me .

Thanks
Nusrat




Re: Cartesian Product query [message #443208 is a reply to message #443205] Sat, 13 February 2010 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an outer join.

If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Sat, 13 February 2010 12:05]

Report message to a moderator

Re: Cartesian Product query [message #443244 is a reply to message #443208] Sun, 14 February 2010 00:22 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi

I am using Oracle9i Enterprise Edition Release 9.2.0.5.0

we have two tables tab1 and tab2

CREATE TABLE tab1 
  (cust_num  VARCHAR2(3),
   yr        NUMBER(5),
   mnth      NUMBER(4), 
   amt NUMBER(6)
   );
   
CREATE TABLE tab2 
   (yr  number(5),
    mnth number(4)
    );


  insert into tab1 values('C1' ,2009,1,100);
  insert into tab1 values('C1' ,2009,2,50);
  insert into tab1 values('C2' ,2008,1,200);

  insert into tab2 values( 2009,1);
  insert into tab2 values( 2009,2);
  insert into tab2 values( 2009,3);
  insert into tab2 values( 2009,4);
  insert into tab2 values( 2009,5);
  insert into tab2 values( 2009,6);
  insert into tab2 values( 2009,7);
  insert into tab2 values( 2009,8);
  insert into tab2 values( 2009,9);
  insert into tab2 values( 2009,10);
  insert into tab2 values( 2009,11);
  insert into tab2 values( 2009,12);
  insert into tab2 values( 2008,1);
  insert into tab2 values( 2008,2);
  insert into tab2 values( 2008,3);
  insert into tab2 values( 2008,4);
  insert into tab2 values( 2008,5);
  insert into tab2 values( 2008,6);
  insert into tab2 values( 2008,7);
  insert into tab2 values( 2008,8);
  insert into tab2 values( 2008,9);
  insert into tab2 values( 2008,10);
  insert into tab2 values( 2008,11);
  insert into tab2 values( 2008,12);


SQL> select * from tab1;

CUS        YR      MNTH       AMT
--- --------- --------- ---------
C1       2009         1       100
C1       2009         2        50
C2       2008         1       200

SQL> select * from tab2

       YR      MNTH
--------- ---------
     2009         1
     2009         2
     2009         3
     2009         4
     2009         5
     2009         6
     2009         7
     2009         8
     2009         9
     2009        10
     2009        11
     2009        12
     2008         1
     2008         2
     2008         3
     2008         4
     2008         5
     2008         6
     2008         7
     2008         8
     2008         9
     2008        10
     2008        11
     2008        12


Using the above two tables i need the below result..
cust_num     Yr   mnth  amt 
-------       --   -----  -----
c1            2009  1     100
c1            2009  2      50
c1            2009  3       0
c1            2009  4       0
c1            2009  5       0
c1            2009  6       0
c1            2009  7       0
c1            2009  8       0
c1            2009  9       0
c1            2009  10      0
c1            2009  11      0
c1            2009  12      0

c1            2008  1       0
c1            2008  2       0
c1            2008  3       0 
c1            2008  4       0
c1            2008  5       0
c1            2008  6       0
c1            2008  7       0
c1            2008  8       0
c1            2008  9       0
c1            2008  10      0
c1            2008  11      0
c1            2008  12      0

c2            2009  1       0
c2            2009  2       0
c2            2009  3       0
c2            2009  4       0
c2            2009  5       0
c2            2009  6       0
c2            2009  7       0
c2            2009  8       0
c2            2009  9       0
c2            2009  10      0
c2            2009  11      0
c2            2009  12      0

c2            2008  1     200
c2            2008  2       0
c2            2008  3       0 
c2            2008  4       0
c2            2008  5       0
c2            2008  6       0
c2            2008  7       0
c2            2008  8       0
c2            2008  9       0
c2            2008  10      0
c2            2008  11      0
c2            2008  12      0


Please suggest me how to write the query to get the above result.

Thanks in advance

Nusrat
Re: Cartesian Product query [message #443249 is a reply to message #443244] Sun, 14 February 2010 01:07 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> break on yr dup skip 1
SQL> with 
  2    cus_month as (
  3      select cust_num, yr, mnth 
  4      from (select distinct cust_num from tab1), tab2
  5    )
  6  select t2.cust_num, t2.yr, t2.mnth, nvl(t1.amt,0) amt
  7  from cus_month t2, tab1 t1
  8  where t1.cust_num (+) = t2.cust_num 
  9    and t1.yr (+) = t2.yr
 10    and t1.mnth (+) = t2.mnth
 11  order by 1,2,3
 12  /
CUS         YR       MNTH        AMT
--- ---------- ---------- ----------
C1        2008          1          0
C1        2008          2          0
C1        2008          3          0
C1        2008          4          0
C1        2008          5          0
C1        2008          6          0
C1        2008          7          0
C1        2008          8          0
C1        2008          9          0
C1        2008         10          0
C1        2008         11          0
C1        2008         12          0

C1        2009          1        100
C1        2009          2         50
C1        2009          3          0
C1        2009          4          0
C1        2009          5          0
C1        2009          6          0
C1        2009          7          0
C1        2009          8          0
C1        2009          9          0
C1        2009         10          0
C1        2009         11          0
C1        2009         12          0

C2        2008          1        200
C2        2008          2          0
C2        2008          3          0
C2        2008          4          0
C2        2008          5          0
C2        2008          6          0
C2        2008          7          0
C2        2008          8          0
C2        2008          9          0
C2        2008         10          0
C2        2008         11          0
C2        2008         12          0

C2        2009          1          0
C2        2009          2          0
C2        2009          3          0
C2        2009          4          0
C2        2009          5          0
C2        2009          6          0
C2        2009          7          0
C2        2009          8          0
C2        2009          9          0
C2        2009         10          0
C2        2009         11          0
C2        2009         12          0

Regards
Michel
Previous Topic: call a function which returns multiple values
Next Topic: How To Get Week Number From specified date.
Goto Forum:
  


Current Time: Thu Dec 08 22:10:17 CST 2016

Total time taken to generate the page: 0.08299 seconds