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:
- 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.
- 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.
- 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