Geek Answers Handbook

SQL selects the default value only if a specific value does not exist

I am trying to join two tables based on multiple columns, but I want to return the results from the default row if there is no specific value for any column.

Below are samples from 2 tables that I am trying to join:

DATA_TABLE:

ID CATEGORY_1 CATEGORY_2 CATEGORY_3 1 YYY 2 YNN 3 YNY

ACCOUNT_TABLE:

  CATEGORY_1 CATEGORY_2 CATEGORY_3 ACCT_NUM YYY 123 YN ALL 234 ALL ALL Y 345

I am trying to reach ACCOUNT_TABLE.ACCT_NUM if the data matches CATEGORY_1, 2 and 3, but if it is not, select ACCT_NUM associated with the value "ALL" from ACCOUNT_TABLE. In addition, if several records can be found due to the value of "ALL" in ACCOUNT_TABLE, I want to return ACCT_NUM based on priority (i.e., a match on CATEGORY_1 will take precedence over a match on CATEGORY_2).

So far, the query that I wrote looks like this:

  select d.*, a.acct_num from data_table d, account_table a where d.category_1 = DECODE(a.category_1,'ALL',d.category_1,a.category_1) and d.category_2 = DECODE(a.category_2,'ALL',d.category_2,a.category_2) and d.category_3 = DECODE(a.category_3,'ALL',d.category_3,a.category_3)

But my conclusion is as follows:

  ID CATEGORY_1 CATEGORY_2 CATEGORY_3 ACCT_NUM 1 YYY 123 1 YYY 345 2 YNN 234 3 YNY 234 3 YNY 345

For ID 1, I need to return ACCT_NUM 123, because matching categories 1, 2 and 3 should take precedence over the entry "ALL".

ID 2 matches correctly and returns the expected ACCT_NUM.

ID 3 I want to return ACCT_NUM 234, since matching in categories 1 + 2 should take precedence over matching in category 3.

So below will be my expected result:

  ID CATEGORY_1 CATEGORY_2 CATEGORY_3 ACCT_NUM 1 YYY 123 2 YNN 234 3 YNY 234

Any help / advice on writing a query to achieve the above?

+4
source share
1 answer

This is best done using a correlated subquery. In Oracle 12g you can do:

 select d.*, (select a.acct_num from account_table a where (d.category_1 = a.category_1 or a.category_1 = 'ALL') and (d.category_2 = a.category_2 or a.category_2 = 'ALL') and (d.category_3 = a.category_3 or a.category_3 = 'ALL') order by ((case when a.category_1 = 'ALL' then 1 else 0 end) + (case when a.category_2 = 'ALL' then 1 else 0 end) + (case when a.category_3 = 'ALL' then 1 else 0 end) ) asc fetch first 1 row only ) as acct_num from data_table d;

That is, select the match with the smallest All values.

In earlier versions of Oracle, you can do the same with aggregation and keep :

 select d.*, (select max(a.acct_num) keep (dense_rank first order by ((case when a.category_1 = 'ALL' then 1 else 0 end) + (case when a.category_2 = 'ALL' then 1 else 0 end) + (case when a.category_3 = 'ALL' then 1 else 0 end) ) asc ) from account_table a where (d.category_1 = a.category_1 or a.category_1 = 'ALL') and (d.category_2 = a.category_2 or a.category_2 = 'ALL') and (d.category_3 = a.category_3 or a.category_3 = 'ALL') ) as acct_num from data_table d;

You can also do something similar with join , and then use row_number() to rank.

+2
source

More articles:


All Articles