Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can i implement the following if elseif else condition in SQL?

msMar 18 2022

Hello Everyone,
I have a date column release_date of date datatype in table.
There is another column multi_select which can be either Y or N. If it is Y then we can select multiple values and if N then only one value can be selected.
When the multi_select flag is set to Y , I concatenate both the selected dates from Front end
to a variable. This variable is used as a parameter to a sql query.
How can i achieve the following results:

  1. If the multi select flag is Y then i want to compare the release_date column with multiple date values for e.g. 01-jan-2022 and 02-jan-2022 and so on.
  2. If the multi select flag is N then i want to compare the release_date column with any single date value for e.g 10-jan-2022.
  3. If no dates are passed as parameter from Front end then i have to include the condition that parameter1 IS NULL.
    I have created a Global temporary table and inserted the multiple values from Front end to DB.
    (Exists (select 1 from gtt_1 tmp
    where tmp.col_name = 'p_delivery_date'
    and TO_DATE(tmp.col_value,'YYYY/MM/DD')=(TRUNC (NVL (woh.delivery_date, wah.release_date))))
    OR '01-JUN-2021;03-JUN-2021' IS NULL
    OR (
    TRUNC (NVL (woh.delivery_date, wah.release_date)) ='01-JUN-2021;03-JUN-2021' and
    '01-JUN-2021;03-JUN-2021' is not null
    ));

However it s failing in some cases where it is comparing the concatenated dates with the release_date column when there is no data returned by the GTT table and throwing the error:
ORA-01861: literal does not match format string
Is this possible in SQL query itself or else how can this be achieved in PL/SQL ?

Thanks

This post has been answered by James Su on Mar 22 2022
Jump to Answer
Comments
Post Details
Added on Mar 18 2022
6 comments
610 views