SQL QUERY WITH A CASE IN WHERE CLAUSE-CodePudding

I want to write a query - to cover a case :- where I want to check if any misc value present for a code_id (a input vairable) if not then use code_id as default value (i.e OTH).

something like

select MISC_FLAGS  
from systemcode 
where rec_type = 'C' 
   and code_type = 'SAN' 
   and  CODE_ID = 'HT';

If no value for this then it should return result for:

select MISC_FLAGS  
from systemcode 
where rec_type = 'C' 
   and code_type = 'SAN' 
   and  CODE_ID = 'OTH';

CodePudding user response：

you can use this query

select MISC_FLAGS  from systemcode where rec_type = 'C' and code_type = 'SAN' and CODE_ID = 'HT' or (
CODE_ID = 'OTH' and TABLE_ID not in (select TABLE_ID   from systemcode where rec_type = 'C' and code_type = 'SAN' and CODE_ID = 'HT') 
)

CodePudding user response：

This could be one option; read comments within code.

As I ran it in SQL*Plus, I used substitution variable; depending on how you do it, it might be a bind variable (:par_code_id) instead; or, if it is a procedure/function, you'd just use just par_code_id.

SQL> with systemcode (misc_flags, rec_type, code_type, code_id) as
  2    -- sample data
  3    (select 'MSC', 'C', 'SAN', 'OTH' from dual union all
  4     select 'ABC', 'C', 'SAN', 'TT'  from dual
  5    ),
  6  temp as
  7    -- MX = 1 if :PAR_CODE_ID exists in SYSTEMCODE table
  8    -- MX = 0 if :PAR_CODE_ID does not exist in SYSTEMCODE table
  9    (select nvl(max(1), 0) mx
 10     from systemcode
 11     where exists (select null from systemcode
 12                   where code_id = '&&par_code_id'
 13                  )
 14     )
 15  select s.misc_flags
 16  from systemcode s cross join temp t
 17  where s.rec_type = 'C'
 18    and s.code_type = 'SAN'
 19    -- depending on MX value, select either row(s) that match :PAR_CODE_ID or 'OTH'
 20    and s.code_id = case when t.mx = 1 then '&&par_code_id'
 21                         else 'OTH'
 22                    end;
Enter value for par_code_id: HT          --> doesn't exist, so use OTH

MIS
---
MSC

SQL> undefine par_code_id
SQL> /
Enter value for par_code_id: TT         --> it exists

MIS
---
ABC

CodePudding user response：

You may use conditional aggregation to select OTH and CODE_ID of your input in different columns. Then select one of the columns depending on the presence of specified CODE_ID.

create table systemcode (
  MISC_FLAGS,
  rec_type,
  code_type,
  CODE_ID
) as
select 'HT', 'C', 'SAN', 'HT' from dual union all
select 'OTH', 'C', 'SAN', 'OTH' from dual

select decode(
  /*If the specified code is present*/
  max(decode(code_id, 'HT', code_id)),
  'HT',
  /*then select a value for that code*/
  max(decode(code_id, 'HT', MISC_FLAGS)),
  /*else - select default value*/
  max(decode(code_id, 'OTH', MISC_FLAGS))
) as code_id
from systemcode
where code_id in ('HT', 'OTH')

| CODE_ID |
| :------ |
| HT      |

select decode(
  max(decode(code_id, 'AAA', code_id)),
  'AAA',
  max(decode(code_id, 'AAA', MISC_FLAGS)),
  max(decode(code_id, 'OTH', MISC_FLAGS))
) as code_id
from systemcode
where code_id in ('AAA', 'OTH')

| CODE_ID |
| :------ |
| OTH     |

db<>fiddle here

CodePudding user response：

If there is only a single row to return for each code, you can use:

SELECT misc_flags
FROM   (
  SELECT misc_flags 
  FROM   systemcode 
  WHERE  rec_type  = 'C' 
  AND    code_type = 'SAN' 
  AND    code_id   IN ('HT', 'OTH')
  ORDER BY code_id ASC
)
WHERE  ROWNUM = 1;

If there can be multiple rows:

SELECT misc_flags
FROM   (
  SELECT misc_flags,
         RANK() OVER (ORDER BY code_id ASC) AS rnk
  FROM   systemcode 
  WHERE  rec_type  = 'C' 
  AND    code_type = 'SAN' 
  AND    code_id   IN ('HT', 'OTH')
)
WHERE  rnk = 1;