Home » SQL & PL/SQL » SQL & PL/SQL » all the rows from two tables with conditions (Oracle)
| all the rows from two tables with conditions [message #689954] |
Fri, 16 August 2024 05:06  |
 |
suji6281
Messages: 151
Registered: September 2014
|
Senior Member |
|
|
Hi All,
Please help me with the SQL query to fetch all the rows. i.e., rows that are in both the tables (merged), rows that are only in PO_TBL and rows that are only in GL_TBL.
below is the sql script explained with the example.
CREATE TABLE PO_TBL (
BU_UNIT varchar(5),
SOURCE varchar(3),
ACCOUNT varchar(7),
PRODUCT varchar(6),
AMOUNT int
);
CREATE TABLE GL_TBL (
BU_UNIT varchar(5),
SOURCE varchar(3),
ACCOUNT varchar(7),
PRODUCT varchar(6),
AMOUNT int
);
INSERT INTO PO_TBL VALUES ('58AD1', 'ONL', '1435189', '618010', 127.00);
INSERT INTO PO_TBL VALUES ('126AV', 'ONL', '1260001', '618567', 200.00);
INSERT INTO PO_TBL VALUES ('126ST', 'ONL', '1260002', '618568', 300.00);
INSERT INTO GL_TBL VALUES ('58AD1', 'ONL', '1435189', '618010', 100.00);
INSERT INTO GL_TBL VALUES ('901TT', 'ONL', '8901040', '901227', 50.00);
INSERT INTO GL_TBL VALUES ('902AA', 'ONL', '8901040', '523819', 89.00);
Formula for VARIANCE IN %:
IF GL_TBL.AMOUNT <> 0 THEN VARIANCE/GL_TBL.AMOUNT
IF VARIANCE = 0 AND GL_TBL.AMOUNT = 0 THEN 0.00
IF VARIANCE <> 0 AND GL_TBL.AMOUNT = 0 THEN 1.00
ELSE 1.00
Output should be as below:
BU_UNIT SOURCE ACCOUNT PRODUCT GL_AMT PO_AMT VARIANCE VARIANCE IN %
58AD1 ONL 1435189 618010 100 127 -27 1.00%
126AV ONL 1260001 618567 0 200 -200 1.00%
126ST ONL 1260002 618568 0 300 -300 1.00%
901TT ONL 8901040 901227 50 0 50 1.00%
902AA ONL 8901040 523819 89 0 89 1.00%
Regards
Suji
|
|
|
|
|
|
| Re: all the rows from two tables with conditions [message #689958 is a reply to message #689957] |
Fri, 16 August 2024 05:57  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've not explicitly stated what the tables should be joined on, though it seems likely it's bu_unit, source, account and product.
As John says - use a full outer join to join the two tables, you'll need to use nvl on all columns in the select list to ensure you get values regardless of which table(s) actually has data. That'll get you the first 7 columns from your output.
Then you probably want to nest the whole thing and use a case statement to work out VARIANCE IN %
[Updated on: Fri, 16 August 2024 06:02] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: all the rows from two tables with conditions [message #689963 is a reply to message #689962] |
Fri, 16 August 2024 09:48 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Side note about this:
IF GL_TBL.AMOUNT <> 0 THEN VARIANCE/GL_TBL.AMOUNT
IF VARIANCE = 0 AND GL_TBL.AMOUNT = 0 THEN 0.00
IF VARIANCE <> 0 AND GL_TBL.AMOUNT = 0 THEN 1.00
ELSE 1.00
If the last IF gives the same result as the ELSE then the last IF isn't needed. Though in this case the ELSE would never kick in (if variance or gl_tbl.amount could be null then it could, but you're nvling the amounts to 0 so variance can't be null either).
It simplifies to
IF GL_TBL.AMOUNT <> 0 THEN VARIANCE/GL_TBL.AMOUNT
ELSIF VARIANCE = 0 THEN 0
ELSE 1
|
|
|
|
|
|
| Re: all the rows from two tables with conditions [message #690013 is a reply to message #690011] |
Sat, 14 September 2024 09:59 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SELECT NVL(PO.ACCOUNT,GL.ACCOUNT) ACCOUNT,
NVL(PO.BU_UNIT,GL.BU_UNIT) BU_UNIT,
NVL(PO.SOURCE,GL.SOURCE) SOURCE,
NVL(PO.PRODUCT,GL.PRODUCT) PRODUCT,
NVL(GL.AMOUNT,0) GL_AMT,
NVL(PO.AMOUNT,0) PO_AMT,
NVL(GL.AMOUNT,0) - NVL(PO.AMOUNT,0) VARIANCE,
CASE
WHEN NVL(GL.AMOUNT,0) != 0 THEN ROUND((NVL(GL.AMOUNT,0) - NVL(PO.AMOUNT,0)) *100 / NVL(GL.AMOUNT,0),2)
WHEN NVL(GL.AMOUNT,0) - NVL(PO.AMOUNT,0) = 0 THEN 0
ELSE 100
END || '%' VARIANCE_PCT
FROM PO_TBL PO
FULL JOIN
GL_TBL GL
ON PO.ACCOUNT = GL.ACCOUNT
AND PO.BU_UNIT = GL.BU_UNIT
AND PO.SOURCE = GL.SOURCE
AND PO.PRODUCT = GL.PRODUCT
/
ACCOUNT BU_UN SOU PRODUC GL_AMT PO_AMT VARIANCE VARIANCE_PCT
------- ----- --- ------ ---------- ---------- ---------- ------------
1435189 58AD1 ONL 618010 100 127 -27 -27%
8901040 901TT ONL 901227 50 0 50 100%
8901040 902AA ONL 523819 89 0 89 100%
1260002 126ST ONL 618568 0 300 -300 100%
1260001 126AV ONL 618567 0 200 -200 100%
SQL>
|
|
|
|
| Re: all the rows from two tables with conditions [message #690016 is a reply to message #690013] |
Mon, 16 September 2024 05:58 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
SELECT PO.ACCOUNT, PO.BU_UNIT, PO.SOURCE, PO.PRODUCT, PO.AMOUNT, GL.AMOUNT
FROM PO_TBL PO FULL JOIN GL_TBL GL
ON PO.ACCOUNT = GL.ACCOUNT
AND PO.BU_UNIT = GL.BU_UNIT
AND PO.SOURCE = GL.SOURCE
AND PO.PRODUCT = GL.PRODUCT
WHERE PO.ACCOUNT IS NOT NULL;
With an outer join there is an optional table(s) and oracle will make up a row of nulls for the optional table if it can't find an actual row that satisfies the ON clause.
In the case of a full outer join both tables are optional.
So that full outer join will get:
1. all rows from PO_TBL and GL_TBL that join together as specified by the ON clause
2. all rows from PO_TBL where there are no corresponding rows in GL_TBL and it'll make up nulls for all the GL_TBL columns
3. all rows from GL_TBL where there are no corresponding rows in PO_TBL and it'll make up nulls for all the PO_TBL columns
Once it's done that it'll apply the where clause.
And the where clause states that PO.ACCOUNT IS NOT NULL. But in option 3 above PO.ACCOUNT is always null - it has to be as oracle made up nulls for the PO table columns.
The outer join states you can have output without PO data.
There where clause states you have to have PO data.
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 00:25:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
