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 Go to next message
suji6281
Messages: 145
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:  GL_TBL.AMOUNT  - PO_TBL.AMOUNT
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%


Thank you.

Regards
Suji
Re: all the rows from two tables with conditions [message #689957 is a reply to message #689954] Fri, 16 August 2024 05:27 Go to previous messageGo to next message
John Watson
Messages: 8954
Registered: January 2010
Location: Global Village
Senior Member
Start with a full outer join on bu_unit. Then work on the column projection.
Re: all the rows from two tables with conditions [message #689958 is a reply to message #689957] Fri, 16 August 2024 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 13946
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 #689959 is a reply to message #689958] Fri, 16 August 2024 08:01 Go to previous messageGo to next message
John Watson
Messages: 8954
Registered: January 2010
Location: Global Village
Senior Member
Quote:
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.
I was thinking of projecting something like
coalesce(po_tbl.source,gl_tbl.source)
Re: all the rows from two tables with conditions [message #689960 is a reply to message #689959] Fri, 16 August 2024 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 13946
Registered: September 2008
Location: Rainy Manchester
Senior Member
Same, except with nvl. Unless the query is returning huge amounts of data there will be no noticeable difference between the two.
Re: all the rows from two tables with conditions [message #689961 is a reply to message #689954] Fri, 16 August 2024 09:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68701
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See examples in Join wiki page.

[Updated on: Fri, 16 August 2024 09:22]

Report message to a moderator

Re: all the rows from two tables with conditions [message #689962 is a reply to message #689960] Fri, 16 August 2024 09:25 Go to previous messageGo to next message
suji6281
Messages: 145
Registered: September 2014
Senior Member
Thank you all for the update, I will use FULL JOIN and apply NVL for the amount fields.
Re: all the rows from two tables with conditions [message #689963 is a reply to message #689962] Fri, 16 August 2024 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 13946
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 #690011 is a reply to message #689963] Sat, 14 September 2024 06:03 Go to previous messageGo to next message
suji6281
Messages: 145
Registered: September 2014
Senior Member
Hi All,

I have tried with the following query but results are not as expected.

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;

I got the results 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%


The unmatched rows whatever we have in GL_TBL not shown in the query.

Please help me with sql query to get all the rows.

Regards
suji
Re: all the rows from two tables with conditions [message #690013 is a reply to message #690011] Sat, 14 September 2024 09:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3297
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>
SY.
Re: all the rows from two tables with conditions [message #690016 is a reply to message #690013] Mon, 16 September 2024 05:58 Go to previous message
cookiemonster
Messages: 13946
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;
The problem is that where clause makes no sense when combined with a full outer join.
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.
Previous Topic: remove comments from all_source output
Next Topic: Get most recent Value in one row only. (6 merged)
Goto Forum:
  


Current Time: Sat Oct 12 14:10:19 CDT 2024