SQL Query to pass multiple parameter with <= instead of in

Question

I am passing two parameters - Month and year

Month can be 01/2022, 02/2022, 03/2022 etc Year can be 2022, 2021,2020

Year is mandatory

I have a condition in the query that compares these dates -

SELECT Max(acrl2.accrual_period)
FROM   anc_per_accrual_entries acrl2
WHERE  person_id = 123
AND    acrl2.plan_id = 1678
acrl2.accrual_period
and to_char(acrl2.accrual_period,'yyyy') = NVL(:p_year,to_char(acrl2.accrual_period,'yyyy'))
AND    acrl2.accrual_period <= Nvl((
       CASE
              WHEN :p_month IS NOT NULL 
              THEN To_date(To_char(Last_day(To_date(:p_month, 'yyyy/mm')), 'yyyymmdd'), 'yyyymmdd')
              WHEN :p_year IS NOT NULL 
              THEN To_date(:p_year  || '1231', 'yyyymmdd')            
       END) ,sysdate)

When i pass one month - say 01/2022 - I am getting the correct output. or when i am passing just 2021, I am getting an output. The issue is when i select multiple months 01/2022, 02/2022 or 2021,2022.

I know i can use in clause. But I am not being able to use it with <=

score 0 · Answer 1 · answered May 20 '22 at 07:08

Not sure about what you expect when the conditions are met, but herre is what you can do with the conditions themselves. Case works in a way that it will accept the first WHEN condition satisfied end exits. So, if first when cond is true the second is not considered at all. I split the two (month, year) to be tested both. Also there is INSTR function which tests p_month and p_year.

SELECT 
    Max(acrl2.accrual_period)
FROM   
    anc_per_accrual_entries acrl2
WHERE  
    person_id = 123 And
    acrl2.plan_id = 1678 And 
    TRUNC(acrl2.accrual_period, 'dd') <= (CASE
                                            WHEN Instr(:p_month, To_Char(acrl2.accrual_period, 'mm/yyyy')) > 0
                                                THEN To_Date(To_char(Last_day(acrl2.accrual_period), 'yyyymmdd'), 'yyyymmdd')
                                          END) And 
    TRUNC(acrl2.accrual_period, 'dd') <= (CASE
                                            WHEN Instr(:p_year, To_Char(acrl2.accrual_period, 'yyyy')) > 0
                                                THEN To_Date(To_char(acrl2.accrual_period, 'yyyy') || '1231', 'yyyymmdd')
                                        END)

SQL Query to pass multiple parameter with <= instead of in

1 Answers1