Link to home
Start Free TrialLog in
Avatar of MIHIR KAR
MIHIR KARFlag for India

asked on

Oracle sql .

I have 2 table
tab1                                          tab2
-------                                         --------
col1     col2                               col1           col2
------     -------                             -------          --------
 1      22-MAY-17                        2      23-MAY-17
 3      24-MAY-17                        4      25-MAY-17
 5      26-MAY-17                        6      27-MAY-17
 7      28-MAY-17                        8      29-MAY-17
 9      30-MAY-17                       10     31-MAY-17

but  o/p should like this  from both table without using any set operater(ex-union all)
how is it possible .
-----------------------------------
col1     col2
------     ------
1       22-MAY-17
2       23-MAY-17
3       24-MAY-17
4       25-MAY-17
5       26-MAY-17
6       27-may-17

Thanks in advance .
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Unfortunately, I am not it position to type much and I am NOT a PL/SQL developer....still...try something on following lines...

select nvl(t1.col1, t2.col1) as col1, nvl(t1.col2, t2.col2) as col2
from tab1 t1
left outer join tab2 t2 on t1.col1 + 1 = t2.col1
order by 1

Open in new window


Obviously, I have not tested the code...
Avatar of Bill Prew
Bill Prew

In Oracle SQL I think the following is what you want.

SELECT NVL(t1.col1, t2.col1) AS col1, 
       NVL(t1.col2, t2.col2) AS col2
FROM tab1 t1
FULL OUTER JOIN tab2 t2 
ON t2.col1 = t1.col1
ORDER BY 1;

Open in new window


You can also do the same using COALESCE, as in:

SELECT COALESCE(t1.col1, t2.col1) AS col1, 
       COALESCE(t1.col2, t2.col2) AS col2
FROM tab1 t1
FULL OUTER JOIN tab2 t2 
ON t2.col1 = t1.col1
ORDER BY 1;

Open in new window


»bp
Avatar of MIHIR KAR

ASKER

Thnks a lot for the query
But Its showing error when i tried to select only 1st  6 row .
the result should be like  1-6 only .
col1     col2
------     ------
1       22-MAY-17
2       23-MAY-17
3       24-MAY-17
4       25-MAY-17
5       26-MAY-17
6       27-may-17
why do you have the constraint of not using "union all"

is this a homework question ?

otherwise it's like asking ... you can use the database but not SQL
I need to generate a report based on this scenario  as per my client required .
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot bill . I got exactly what i'm looking for .
Welcome.


»bp