Search code examples
sqlwhere-clauseclause

SQL Combine same bind variable

I have a where clause that says:

select * from tablename 
where :x < y or y is null 
and :x > z

I tried to rewrite it so that it uses :x once as shown below but I don't understand why I keep getting an error that says 'SQL command not properly ended'.

where z < :x < y or y is null

Any help would be appreciated, thanks.

Solution

  • The expression you are trying to use z < x < y is not standard SQL.

    You can come close with:

    where :x between y and z or y is null;

    The difference is that between is really 'y <= x <= z'. If inequality is really needed and the values are integers, you can easily do:

    where :x between y + 1 and z - 1 or y is null;