How to handle if value comes with 0 and multiple dynamically in oracle sql?-CodePudding

#following 5001 (param s_id) is dynamically called. this was not worked and not return any rows from student table. what is the root cause for this issue?#

select * from student where 
   ( ( 1 = CASE WHEN to_char('5001') = to_char(0) THEN
                            1
                        ELSE
                            0
                    END )
              OR student.roll_id IN ( 5001 ) );

CodePudding user response：

Assuming there is at least 1 row in your table - Your where clause will not select a row if there is no row with ROLL_ID = 5001 (number) and your parameter s_id has never the value of 0 (number zero). If there is the row with ROLL_ID = 5001 that row will be selected and if param s_id is 0 (number zero) then all rows will be selected....

Just structured your sql a bit (it's the same as it was)
Test 1: there is a row with ROLL_ID = 5001

WITH
    students AS
        (   Select 1 "ROLL_ID", 'Not 5001' "SOME_COLUMN" From dual  Union All   
            Select 5001 "ROLL_ID", 'Here I am' "SOME_COLUMN" From dual      )

Select  * 
From    students 
Where   ( ( CASE WHEN to_char(5001) = to_char(0) THEN 1
            ELSE 0 
            END = 1 )
          OR
            students.ROLL_ID IN ( 5001 ) 
        );
--  
--  R e s u l t :
--     ROLL_ID SOME_COLUMN
--  ---------- -----------
--        5001 Here I am

Test 2: same code - with new sample data where there is no row with ROLL_ID = 5001

WITH
    students AS
        (   Select 1 "ROLL_ID", 'Not 5001' "SOME_COLUMN" From dual  Union All   
            Select 5002 "ROLL_ID", 'Here I was' "SOME_COLUMN" From dual     )¸
-- same sql returns no rows

Test 3: sample data still without a row with ROLL_ID = 5001 but param s_id = 0

Select  * 
From    students 
Where   ( ( CASE WHEN to_char(0) = to_char(0) THEN 1
            ELSE 0 
            END = 1 )
          OR
            students.ROLL_ID IN ( 5001 ) 
        );
--  
--  R e s u l t :
--     ROLL_ID SOME_COLUMN
--  ---------- -----------
--           1 Not 5001    
--        5002 Here I was

Test 3 will select all rows - always