CODEWITHSUNDEEP
sqloracle-databasedecode
amsko
amsko

Reputation: 111

using if-else/decode in where clause

I have a cursor which has multiple conditions, to retrieve a set of data. I recently added a date condition in it to retrieve data between particular dates that the user enters as below:

cursor c1
  is
     select  t.*
         from (select v.*, dense_rank () over (order by created desc)
                                                                  as rank
                 from test.table_v2 v
                where some condition
                and some condition
                  and some condition
                  and some condition
                  and some condition
                  and some condition
                 and ((ended) between to_date(sd,'mm/dd/rrrr')
                                  and to_date(sd,'mm/dd/rrrr')+3
                     or ended like decode(sd,null,'%')) --new condition
                 and some condition
                ) t
        where rank < rmax+1
     order by ended desc;

Here rmax = 1000

I need to add a condition in the where clause so that when the sd (user entered date) is null the rows get limited to 1000 and when its not null the row limitation should not be considered.

I'm not sure if decode can be used in a where clause. is there any way to do this?

Upvotes: 1

Views: 200

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

Just add or sd is [not] null

select  t.*
     from (select v.*, dense_rank () over (order by created desc)
                                                              as rank
             from test.table_v2 v
            where some condition
            and some condition
              and some condition
              and some condition
              and some condition
              and some condition
             and (ended between to_date(sd,'mm/dd/rrrr')
                            and to_date(sd,'mm/dd/rrrr')+3
                 or sd is null) --new condition
             and some condition
            ) t
    where rank < rmax+1 or sd is not null

Upvotes: 1

Related Questions