CODEWITHSUNDEEP
sqloracle-database
Programmer
Programmer

Reputation: 723

Oracle SQL trick

I want to do something like below in oracle SQL in the where clause of a SQL query

$p="All" then buildingname in(Select buildingname from Building) else buildingname="$p"

The issue is I cant parse this $p variable i.e. I cant check what it contains

Upvotes: 0

Views: 339

Answers (3)

hol
hol

Reputation: 8423

If your variable within the string is replaced by whatever it contains (hence it is not a bind variable but just a variable from a language like PERL or PHP and used within the select string you are building):

$selectstring = "SELECT *
  FROM sometable
 WHERE (    '$p' = 'All' 
        AND buildingname IN (SELECT buildingname
                              FROM building))
     OR (   '$p' <> 'All' AND buildingname = '$p'"

Become aware of the danger of SQL injection. Bind variables are better. Anyhow the statement for variable $p containing the value 'All' would be

SELECT *
  FROM sometable
 WHERE (    'All' = 'All' 
        AND buildingname IN (SELECT buildingname
                              FROM building))
     OR (   'All' <> 'All' AND buildingname = 'All')

While you would have a selectstring for variable $p containing 'XYZ' of

SELECT *
  FROM sometable
 WHERE (    'XYZ' = 'All' 
        AND buildingname IN (SELECT buildingname
                              FROM building))
     OR (   'XYZ' <> 'All' AND buildingname = 'XYZ')

But honestly then you could also do something like

if ($p eq 'All') {
...   one select
} else {
...   another select
}

Upvotes: 0

Matt Smucker
Matt Smucker

Reputation: 5244

You can use an OR in your subquery, so it either selects the building select or all of them if $p is 'All' 

buildingname in (Select buildingname from Building WHERE buildingname = $p OR $p = 'All')

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231761

It sounds like you just want an OR condition, 

SELECT *
  FROM sometable
 WHERE (    :bind_variable = 'All' 
        AND buildingname IN (SELECT buildingname
                              FROM building))
     OR buildingname = :bind_variable

Upvotes: 3

Related Questions