CODEWITHSUNDEEP
sqloracle-database
poopp
poopp

Reputation: 1467

How pass parameter to WITH clause

I want pass parameter to WITH class something like this:

WITH sction(id) AS (
  SELECT q.value1
  FROM   Example q
  WHERE q.id=id )

is it possible?Anyone can help me?

Upvotes: 0

Views: 1366

Answers (2)

BK Barathi
BK Barathi

Reputation: 91

with sction as (select q.value1,q.id from example q ) select * from sction where id = 1;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143023

WITH factoring clause a.k.a. CTE (Common Table Expression) is what we some time ago used to call a "subquery". As such, it uses a WHERE clause which you can use to pass that "parameter". For example:

WITH sction AS 
  (SELECT q.id,
          q.value1
     FROM Example q
  )
SELECT * 
FROM sction
WHERE id = 125      --> "125" is that "parameter" you pass while selecting from SCTION CTE

As of a subquery I mentioned: that would have been

select *
from (
      select id, value1 from example    --> this is a CTE
     )
where id = 125

In CTE, it is moved "up", outside your "main" query.

Upvotes: 4

Related Questions