SQL Where-Clause with Case Statement sort date-CodePudding

I have a problem again and can't ask anyone at the moment. I want to include a switch-case in a SQL SELECT which filters by date. The goal is to display a simple grid in a window and filter my data by a date "where".

On the one hand I want to search for everything that exists in the year = e.g. '2022'.

Then I want to search only for calendar weeks = e.g. '23'.

Then show me only the month = eg. '07'

and just the complete date = e.g. '22.07.2022'.

   SELECT
     ID
   , GEN_DATUM
   FROM
      TABLE_XY
   WHERE
     CASE 
       WHEN '{DATUM_MODUS}' = '#JA'    THEN  TO_CHAR(GEN_DATUM, 'yyyy')       = TO_CHAR('{DATUM}' , 'yyyy') 
       WHEN '{DATUM_MODUS}' = '#KW'    THEN  TO_CHAR(GEN_DATUM, 'IW')         = TO_CHAR('{DATUM}' , 'IW') 
       WHEN '{DATUM_MODUS}' = '#MO'    THEN  TO_CHAR(GEN_DATUM, 'MM')         = TO_CHAR('{DATUM}' , 'MM') 
       WHEN '{DATUM_MODUS}' = '#WO'    THEN  TO_CHAR(GEN_DATUM, 'DD.MM.YYYY') = TO_CHAR('{DATUM}' , 'MM')
    ELSE NULL
  END

In the source code I pass parameters that I need for further processing. Here I want to say if I want to search for year 'DATUM_MODUS' = #JA then show me the year

System.Collections.Hashtable htPARAM = new System.Collections.Hashtable()
{
 ["DATUM"]       = Datum,
 ["DATUM_MODUS"] = Datum_MODUS,
};

CodePudding user response：

Use AND and OR rather than a CASE expression.
Never build an SQL statement using string concatenation or template strings as you introduce SQL injection vulnerabilities; use bind variables instead.
If you use functions on the gen_datum column then Oracle will not be able to use an index on that column (and would require function-based indexes instead). Instead, change the datum value into a date and match the start and end of each date range and then Oracle can use an index on the gen_datum column.

Like this:

SELECT ID
     , GEN_DATUM
FROM   TABLE_XY
WHERE  (   :datum_modus = '#JA'
       -- Search by year
       AND gen_datum >= TO_DATE(:datum || '-01-01', 'YYYY-MM-DD')
       AND gen_datum <  TO_DATE((:datum   1) || '-01-01', 'YYYY-MM-DD')
       )
OR     (   :datum_modus = '#KW'
       -- Search by ISO week in the current year
       AND gen_datum >= TRUNC(TRUNC(SYSDATE, 'YY')   3, 'IW')   7 * (:datum - 1)
       AND gen_datum <  TRUNC(TRUNC(SYSDATE, 'YY')   3, 'IW')   7 * :datum
       )
OR     (   :datum_modus = '#MO'
       -- Search by month in the current year
       AND gen_datum >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), :datum - 1)
       AND gen_datum <  ADD_MONTHS(TRUNC(SYSDATE, 'YY'), :datum)
       )
OR     (   :datum_modus = '#WO'
       -- Search by date
       AND gen_datum >= TO_DATE(:datum, 'DD.MM.YYYY')
       AND gen_datum <  TO_DATE(:datum, 'DD.MM.YYYY')   1
       )

CodePudding user response：

The with clause is here just to generate some sample data and, as such, it is not a part of the answer.
You missunderstood the CASE expression. CASE expression returns a value and you are trying to get another expression out of it. Also you have to keep in mind that CASE is tested sequentially meaning that the first one with WHEN condition satisfied will return the THEN part and EXIT CASE. Having that in mind all you have to do is compare two CASE expresions (two returned values) and try to get the result. WHEN clauses in both of them should be rearrenged too. Try something like this:

WITH 
    tbl AS
        (
            SELECT 1 "ID", To_Date('08.01.2022', 'dd.mm.yyyy') "GEN_DATUM" FROM DUAL UNION ALL
            SELECT 2 "ID", To_Date('23.04.2022', 'dd.mm.yyyy') FROM DUAL UNION ALL
            SELECT 3 "ID", To_Date('17.06.2022', 'dd.mm.yyyy') FROM DUAL UNION ALL
            SELECT 4 "ID", To_Date('19.06.2022', 'dd.mm.yyyy') FROM DUAL 
        )
   SELECT
     ID, 
     TO_CHAR(GEN_DATUM, 'dd.mm.yyyy') "GEN_DATUM",
     TO_CHAR(GEN_DATUM, 'iw') "WEEK_OF_GEN_DATUM",
     TO_CHAR(GEN_DATUM, 'mm') "MONTH_OF_GEN_DATUM",
     TO_CHAR(GEN_DATUM, 'yyyy') "YEAR_OF_GEN_DATUM",
     :DATUM_MODUS "DATUM_MODUS",
     TO_CHAR(:DATUM, 'dd.mm.yyyy') "DATUM"
   FROM
      tbl
   WHERE
        CASE 
            WHEN :DATUM_MODUS = '#WO'    THEN  TO_CHAR(GEN_DATUM, 'dd.mm.yyyy')     -- the most distinctive (365)
            WHEN :DATUM_MODUS = '#KW'    THEN  TO_CHAR(GEN_DATUM, 'iw')             -- second (52)
            WHEN :DATUM_MODUS = '#MO'    THEN  TO_CHAR(GEN_DATUM, 'mm')             -- next (12)
            WHEN :DATUM_MODUS = '#JA'    THEN  TO_CHAR(GEN_DATUM, 'yyyy')           -- the most undistinctive (1)
        ELSE 
            NULL
        END     
      =
        CASE 
            WHEN :DATUM_MODUS = '#WO'    THEN  TO_CHAR(:DATUM, 'dd.mm.yyyy') 
            WHEN :DATUM_MODUS = '#KW'    THEN  TO_CHAR(:DATUM, 'iw')
            WHEN :DATUM_MODUS = '#MO'    THEN  TO_CHAR(:DATUM, 'mm')
            WHEN :DATUM_MODUS = '#JA'    THEN  TO_CHAR(:DATUM, 'yyyy')        
        ELSE 
            NULL
        END
--
--  If you use bind variables :DATUM_MODUS = '#KW' and :DATUM = To_Date('19.06.2022', 'dd.mm.yyyy') the result is:
--          ID GEN_DATUM  WEEK_OF_GEN_DATUM MONTH_OF_GEN_DATUM YEAR_OF_GEN_DATUM DATUM_MODUS DATUM    
--  ---------- ---------- ----------------- ------------------ ----------------- ----------- ----------
--           3 17.06.2022 24                06                 2022              #KW         19.06.2022 
--           4 19.06.2022 24                06                 2022              #KW         19.06.2022
--
--  For :DATUM_MODUS = '#MO' and :DATUM = To_Date('14.01.2022', 'dd.mm.yyyy') the result is:
--          ID GEN_DATUM  WEEK_OF_GEN_DATUM MONTH_OF_GEN_DATUM YEAR_OF_GEN_DATUM DATUM_MODUS DATUM    
--  ---------- ---------- ----------------- ------------------ ----------------- ----------- ----------
--           1 08.01.2022 01                01                 2022              #MO         14.01.2022

... and so on....
In the SELECT part there are all the values involved so you can check the results and/or (if needed) filter them even more if you make this a subquery of a main one. In that case you can manipulate the data further more. Regards...