RE: Need help in drafting SQL query

From: Sourav Biswas <biswas.sourav_at_hotmail.com>
Date: Sat, 24 Feb 2018 20:28:40 +0000
Message-ID: <PN1PR0101MB2063D5B7CA5A5C2DBC2C5A8FF0C30_at_PN1PR0101MB2063.INDPRD01.PROD.OUTLOOK.COM>

Hi Dimitar,

Thank you so very much!!! I never thought, someone would reply on a weekend.

I was exactly looking for this solution.

Thank you once again Sir! You saved my weekends!!! 😊

Thanks & Regards,
Sourav Biswas
+91-9650017306

From: Dimitar Draginov [mailto:dimitar.draginov_at_gmail.com] Sent: 24 February 2018 23:20
To: biswas.sourav_at_hotmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Need help in drafting SQL query

• Here is the simplest and hopefully most powerful solution.
• From what I've read it looks like table A is highest priority for retrieving data, therefore avoiding any complexity I've just created an outer join of A and C to main table B.
• With NVL() I retrieve A data and only when a value is missing I retrieve B data.
• As seen this resolves your ID values of 1,2,7 restriction too.
• NOTE 1: I assume that all columns are not null-able. Otherwise side effects will occur..
• NOTE 2: As seen I use the Oracle syntax for outer join..

select NVL(a.ID, c.ID) as ID,
       NVL(a.FIN_ID,c.FIN_ID) as FIN_ID,
       NVL(a.DATE,c.DATE) as DATE

  from B, A, C
 where a.FIN_ID(+) = b.FIN_ID
   and c.FIN_ID(+) = b.FIN_ID;

Regards,
  Dimitar

On Sat, Feb 24, 2018 at 8:31 AM, Sourav Biswas <biswas.sourav_at_hotmail.com<mailto:biswas.sourav_at_hotmail.com>> wrote: Hello Everyone,

I need some help in drafting a query. We have below mentioned 3 tables (I’ve limited the columns for brevity).

Column FIN_ID is the primary key on Table B and Table A and C, has respective foreign keys.

FIN_ID column in Table A and C have 3 values in common, i.e. 1,2,7.

So, when I join these 3 tables, I want the output to display ID, FIN_ID and DATE columns from Table A and C. However, when FIN_ID is used in predicate, and value matches either 1 or 2 or 7 and, also, these values are present in Table A, then all 3 columns should be fetched from Table A, or else, it should get the details from Table C.

For rest of the unique values of FIN_ID, it should fetch rows from respective tables. Table A

Table B

Table C

id

fi_ID (FK)

date

fin_ID (PK)

id

fin_ID (FK)

date

a1

1

01-01-2018

1

c1

1

01-01-2017

a2

2

02-01-2018

2

c2

2

02-01-2017

a3

4

03-01-2018

3

c3

3

03-01-2017

a4

7

04-01-2018

4

c4

5

04-01-2017

a5

8

05-01-2018

5

c5

6

05-01-2017

6

c6

7

06-01-2017

7

c7

9

07-01-2017

8

c8

10

08-01-2017

9

10

NOTE: We are using 11.2.0.4 database.

Thanks & Regards,
Sourav Biswas
+91-9650017306<tel:+91%2096500%2017306>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 24 2018 - 21:28:40 CET