0

Just wondering if it is possible to define variables in a WITH query? I've tried but query keeps failing.

Just something like

WITH

DEFINE var1 = 'ABC';

SUBQUERY1 (col1, col2)
AS
(SELECT col1, col2 FROM table1 WHERE COL1 = &var1)

SELECT * FROM subquery1

;

Thanks in advance

K

1

I'm not clear on exactly what it is you want to do, but you could also do something like this (all the code below is available on the fiddle here):

CREATE TABLE table1 (col1 VARCHAR (50), col2 INT);

populate:

INSERT INTO table1 VALUES ('ABC', 1); 
INSERT INTO table1 VALUES ('DEF', 2);

and then run the following query:

WITH cte1 (x, y) AS
(
  SELECT col1, col2 
  FROM table1 WHERE col1 = (SELECT 'ABC' FROM dual)
)  
SELECT * FROM cte1;

Result:

  X  Y
ABC  1
0

Why use WITH in your example? That said, in SQL*Plus the DEFINE must be separate from the query:

DEFINE var1 = 'ABC';
WITH SUBQUERY1 (col1, col2) AS
     (SELECT col1, col2 FROM table1 WHERE COL1 = &var1)
SELECT * FROM subquery1;

A better way to do this specific example would be not to use WITH at all:

DEFINE var1 = 'ABC';
SELECT col1, col2 FROM table1 WHERE COL1 = &var1;
0

A WITH clause can only contain SQL or PL/SQL, not DEFINE, because queries are sent to the server for execution.

You could define a function for each parameter you needed, for example:

with function param1 return varchar2 as
     begin
         return 'X';
     end;
select * from dual d
where  d.dummy = param1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.