Oracle SQL - using if clauses to drive variables

Question

Good morning all

I've written the following query:

    define AssetVar = "'BA G-ZBJA'"
define DateFromVar = "'12/08/2016 15:00:00'"

define AssetClause = " where Asset = &AssetVar";
define

BEGIN
  IF DateFromVar = ""
    Then define DateFromClause = ""
    ELSE
    define DateFromClause ="and DATETIME >= TO_DATE(&DateFromVar, 'dd/mm/yyyy hh24:mi:ss')";
  End IF;
End;
define

select * from SCHEDULE &AssetClause &DateFromClause ;

undefine AssetVar DateFromVar AssetClause DateFromClause;

The theory is, the end user can set variables and the query will be built based on the variables set, if they set it to nothing, the if statement basically excludes that clause from the final query.

If I provide all variables and remove the IF everything is fine, I think the variables are not being passed into the IF correctly but I'm not sure

Has anyone ever achieved this or know why i's not working?

Alex Poole · Accepted Answer · 2018-06-25 08:49:03Z

1

define is a client command, it is not part of the PL/SQL (or indeed SQL) syntax. You can refer substitution variables within an anonymous PL/SQL block but they are evaluated then the block is compiled and you cannot change their values.

You can move all that logic into the query, e.g.:

define AssetVar = "BA G-ZBJA"
define DateFromVar = "12/08/2016 15:00:00"

select * from SCHEDULE
where Asset = '&&AssetVar'
and ('&&DateFromVar' is null or DATETIME >= TO_DATE('&&DateFromVar', 'dd/mm/yyyy hh24:mi:ss'));

With set verify on you can see the SQL you end up with:

old:select * from SCHEDULE
where Asset = '&&AssetVar'
and ('&&DateFromVar' is null or DATETIME >= TO_DATE('&&DateFromVar', 'dd/mm/yyyy hh24:mi:ss'))
new:select * from SCHEDULE
where Asset = 'BA G-ZBJA'
and ('12/08/2016 15:00:00' is null or DATETIME >= TO_DATE('12/08/2016 15:00:00', 'dd/mm/yyyy hh24:mi:ss'))

If you're defining the values then the logic is a bit pointless; it's more likely you're prompting:

accept AssetVar
accept DateFromVar

with schedule(asset, datetime) as (select 'test', sysdate from dual)
select * from SCHEDULE
where Asset = '&&AssetVar'
and ('&&DateFromVar' is null or DATETIME >= TO_DATE('&&DateFromVar', 'dd/mm/yyyy hh24:mi:ss'));

so if you don't enter a date value the comparison ends up as:

...
and ('' is null or DATETIME >= TO_DATE('', 'dd/mm/yyyy hh24:mi:ss'))

Unfortunately you can't specify a format for an option variable, which would be convenient.

answered Jun 25, 2018 at 8:49

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GordonBennett Over a year ago

I'm not sure you understood the point of the question, if the variable is null the entire clause should be excluded

PKey · Accepted Answer · 2018-06-25 08:33:41Z

0

I would use bind variables (:variable) instead of substitution variables (&variable).

Also, you could use case statement in where clause instead of if.

So the solution would look something like the following:

select * from SCHEDULE
where 
Asset = :AssetVar and 
case when DateFromClause = ''  
         then 1
else 
          case when DATETIME >= TO_DATE(:DateFromVar, 'dd/mm/yyyy hh24:mi:ss')
               then 1
          else 
                    0
          end
end   
     = 1

answered Jun 25, 2018 at 8:33

PKey

3,8712 gold badges21 silver badges45 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - using if clauses to drive variables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related