CODEWITHSUNDEEP
sqloracle-databaseparameters
user6651485
user6651485

Reputation: 89

How to define 'with clause' with hardcoded values rather than a subquery?

I have several combinations of values provided and need to build a query with them. It would seem a waste of time to define a subquery that would return those results. Instead, I'm thinking of defining a 'With Clause' that can use them.

To make simple example, let's assume my query takes Country and City parameters. 

WITH INPUT_DATA AS (
'Canada' as COUNTRY
,'Toronto' as CITY
,'USA' as COUNTRY
,'Chicago' as CITY
)
SELECT * FROM SOME_TABLE WHERE COUNTRY=INPUT_DATA.COUNTRY AND CITY=INPUT_DATA.CITY

If there's another way to do this, outside of the 'with clause' and packages, please share, as well. Basically, I want to use some parameters for this query without having to code a subquery to fetch those parameters. Naturally, I need it to support multiple 'rows' of parameter sets.

Please note: Where clause won't do. I can't put all these parameters in the 'where clause' and still have it legible.

Upvotes: 2

Views: 2174

Answers (1)

user5683823
user5683823

Reputation: 

with input_data as (
select 'Canada' as country, 'Toronto' as city from dual union all
select 'USA'    as country, 'Chicago' as city from dual
)
select * from some_table where (country, city) in (select country, city from input_data)
;

Note that the column names from the first set of values will be used, you don't need to repeat them in the second set and beyond (and whatever you put there will be ignored anyway!). Also, starting from Oracle 11.2 you could put those column names in the WITH clause declaration, like so:

with input_data (country, city) as (
  select 'Canada', 'Toronto' from dual union all
  --   [ etc. ]
)

However, it would be a lot simpler to say

where (country, city) in ( ('Canada', 'Toronto'), ('USA', 'Chicago') )

Upvotes: 4

Related Questions