I would like to put the following SQL statement in the where clause in Oracle and introduce it into the case statement to branch the conditions.

I would like oracle to include the following SQL statements in the where clause and introduce them into the case statement to branch the conditions.

Prerequisites include columns similar to the following
A=ID Last Use Date
B=ID registration date
C = ID Published

SQL

that you want to include in the where clause below and introduce in the case statement to branch conditions.

If column A is not null, see column A
If column A is null and Published > Registration Date, see Published
See registration date if column A is null and release date <registration date

I would like to put SQL like the above in where clause, introduce it in case statement, and then branch the conditions.
If you put it in the where clause, the case statement does not understand the grammar according to the above, so it does not work.

What grammar is appropriate in oracle to deploy the algorithms like the one above?

sql oracle

2022-09-30 21:45

1 Answers

You can use case statements in the where clause, so refer to the A,B,C column in the where clause case statement.
The grammar in oracle that is appropriate for deploying such algorithms is to use nvl and greatest in conjunction with greatest to refer to the larger values of B and C in the absence of A.
However, the conditions in the questionnaire do not describe the behavior when B and C are equivalent, so please modify them according to your request.

select nvl(A,greatest(B,C)) as NVL_GREATEST,
       case when A is null then
            case when B>C then B
                 when B<C then C
                 else null -- this line does not have to be the same
            e n d
            else A
       end CASE_NEST
from(select to_date('January 1, 2019') as A, to_date('November 2, 2019') as B, to_date('November 3, 2019') as C from dual union all -- A exists
       select null as A, to_date('11/02/2019') as B, to_date('01/03/2009) as C from dual union all -- B is large
       select null as A, to_date('01/02/2009) as B, to_date('11/03/2019') as C from dual union all -- C is large
       select null as A, to_date('11/11/11/2019') as B, to_date('11/11/2019') as C from dual --B and C are equivalent
      )
where date > case when A is null then
                     case when B>C then B
                          when B<C then C
                          else null
                     e n d
                     else A
                e n d
--where sysdate>nvl(A,greatest(B,C))

2022-09-30 21:45

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656