OraFAQ Forum: SQL & PL/SQL » all the rows from two tables with conditions

Home » SQL & PL/SQL » SQL & PL/SQL » all the rows from two tables with conditions (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

all the rows from two tables with conditions [message #689954]

Fri, 16 August 2024 05:06

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

Report message to a moderator

Re: all the rows from two tables with conditions [message #689957 is a reply to message #689954]

Fri, 16 August 2024 05:27

John Watson
Messages: 8949
Registered: January 2010
Location: Global Village

Senior Member

Start with a full outer join on bu_unit. Then work on the column projection.

Report message to a moderator

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: 13937
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

John Watson
Messages: 8949
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)

Report message to a moderator

Re: all the rows from two tables with conditions [message #689960 is a reply to message #689959]

Fri, 16 August 2024 08:23

cookiemonster
Messages: 13937
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.

Report message to a moderator

Re: all the rows from two tables with conditions [message #689961 is a reply to message #689954]

Fri, 16 August 2024 09:21

Michel Cadot
Messages: 68683
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

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.

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: 13937
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

Report message to a moderator

Re: all the rows from two tables with conditions [message #690011 is a reply to message #689963]

Sat, 14 September 2024 06:03

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

Report message to a moderator

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: 3283
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.

Report message to a moderator

Previous Topic:	Get most recent Value in one row only. (6 merged)
Next Topic:	remove comments from all_source output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 15 18:23:33 CDT 2024