Forum Stats

  • 3,728,151 Users
  • 2,245,559 Discussions
  • 7,853,355 Comments

Discussions

without using a bind parameter

user13328581
user13328581 Member Posts: 1,300 Bronze Badge

Hello experts;

I have a query similar to the following below

with t(id, req_date, place) as

(

select 1, to_date('01/01/2021', 'MM/DD/YYYY'), 'North America' from dual

union all

select 2, to_date('02/01/2021', 'MM/DD/YYYY'), 'South America' from dual

union all

select 3, to_date('03/01/2021', 'MM/DD/YYYY'), 'Africa' from dual

union all

select 4, to_date('04/01/2021', 'MM/DD/YYYY'), 'Asia' from dual

)

select * from t

where t.req_date like '%' || :p_start || '%'

or (t.req_date >= to_date(:p_start, 'MM/DD/YYYY') and

t.req_date <= to_date(:p_end, 'MM/DD/YYYY'))


This works fine, but however I have a quick question, is there a workaround option that allows you to utilize the like without concatenating it to a bind parameter. In otherwords, if the input is left empty then bring back all the date, if not then bring the specified range.


Thanks

Best Answers

  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 13 Accepted Answer
    select * 
    from t
    where (:p_start is null or t.req_date >= to_date(:p_start, 'MM/DD/YYYY')) and
          (:p_end is null or t.req_date <= to_date(:p_end, 'MM/DD/YYYY'))
    
    user13328581
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,315 Red Diamond
    edited April 13 Accepted Answer

    Hi, @user13328581

    The solution Paulzip posted (let's call it Q1) does not produce the same results as the query you posted (let's call it Q0) in a couple of ways.

    • Q0 never includes rows with NULL req_date, no matter what the parameters are. Q1 includes them when both parameters are NULL.
    • In Q0, if p_start is NULL, then it doesn't matter what p_end is. In Q1, if p_start is NULL, rows will still be excluded if req_date is later than p_end. (Personally, I think this is an improvement, but it is a difference.)

    Here's another solution. Like Q0, it only includes rows that have a value for req_date. Like Q1, it allows you to set an upper bound to req_dates without setting a lower bound.

    SELECT   *
    FROM	 t
    WHERE	 req_date  >= NVL ( TO_DATE (:p_start, 'MM/DD/YYYY')
    	 	    	  , TO_DATE ('1', 'J') 
    			  )
    AND      req_date  <= NVL ( TO_DATE (:p_end, 'MM/DD/YYYY')
    	 	    	  , DATE '9999-12-31'
    			  )
    ORDER BY id -- or whatever you want
    ;
    

    Here's another thing to think about. In all three solutions, rows on the same day as p_end will be excluded unless their time is 00:00:00. For example, if p_end = '04/30/2021', then rows where req_date is 00:00:00 on April 30, 2021 can be included, but rows with any times between 00:00:01 and 23:59:59 on the same day will be excluded. Is that what you want? (In most applications, when you specify only the year, month and day as an end point, it means to include everything on that calendar day, regardless of time.) In all three solutions, that behavior can easily be changed.

    In any event, the operands to LIKE are supposed to be strings. Q0 is trying to use a DATE as an operand to LIKE. If you're ever tempted to use a DATE in a place where a string is expected (or use any data type where a different data type is expected) then stop and look for a better way. There almost always is one.

Answers

  • Paulzip
    Paulzip Member Posts: 8,245 Blue Diamond
    edited April 13 Accepted Answer
    select * 
    from t
    where (:p_start is null or t.req_date >= to_date(:p_start, 'MM/DD/YYYY')) and
          (:p_end is null or t.req_date <= to_date(:p_end, 'MM/DD/YYYY'))
    
    user13328581
  • user13328581
    user13328581 Member Posts: 1,300 Bronze Badge

    thanks @paulzip

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,315 Red Diamond
    edited April 13 Accepted Answer

    Hi, @user13328581

    The solution Paulzip posted (let's call it Q1) does not produce the same results as the query you posted (let's call it Q0) in a couple of ways.

    • Q0 never includes rows with NULL req_date, no matter what the parameters are. Q1 includes them when both parameters are NULL.
    • In Q0, if p_start is NULL, then it doesn't matter what p_end is. In Q1, if p_start is NULL, rows will still be excluded if req_date is later than p_end. (Personally, I think this is an improvement, but it is a difference.)

    Here's another solution. Like Q0, it only includes rows that have a value for req_date. Like Q1, it allows you to set an upper bound to req_dates without setting a lower bound.

    SELECT   *
    FROM	 t
    WHERE	 req_date  >= NVL ( TO_DATE (:p_start, 'MM/DD/YYYY')
    	 	    	  , TO_DATE ('1', 'J') 
    			  )
    AND      req_date  <= NVL ( TO_DATE (:p_end, 'MM/DD/YYYY')
    	 	    	  , DATE '9999-12-31'
    			  )
    ORDER BY id -- or whatever you want
    ;
    

    Here's another thing to think about. In all three solutions, rows on the same day as p_end will be excluded unless their time is 00:00:00. For example, if p_end = '04/30/2021', then rows where req_date is 00:00:00 on April 30, 2021 can be included, but rows with any times between 00:00:01 and 23:59:59 on the same day will be excluded. Is that what you want? (In most applications, when you specify only the year, month and day as an end point, it means to include everything on that calendar day, regardless of time.) In all three solutions, that behavior can easily be changed.

    In any event, the operands to LIKE are supposed to be strings. Q0 is trying to use a DATE as an operand to LIKE. If you're ever tempted to use a DATE in a place where a string is expected (or use any data type where a different data type is expected) then stop and look for a better way. There almost always is one.

  • user13328581
    user13328581 Member Posts: 1,300 Bronze Badge
    edited April 16

    Thanks Frank, you are right. The time matters but can be neglected in the above example though

Sign In or Register to comment.