MIHIR KAR
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 .
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 .
In Oracle SQL I think the following is what you want.
You can also do the same using COALESCE, as in:
»bp
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;
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;
»bp
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
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
is this a homework question ?
otherwise it's like asking ... you can use the database but not SQL
ASKER
I need to generate a report based on this scenario as per my client required .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot bill . I got exactly what i'm looking for .
Welcome.
»bp
»bp
Open in new window
Obviously, I have not tested the code...