Home » SQL & PL/SQL » SQL & PL/SQL » Using "with as" in a select statment. (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit)
Using "with as" in a select statment. [message #430240] Mon, 09 November 2009 13:53 Go to next message
TNK101
Messages: 4
Registered: November 2009
Junior Member
How do I get the "as" statement to recognize the SQL stored in the qryGetSinglePOList variable?

qryGetSinglePOList constant varchar2(1024) :=
'select * ' ||
'from POList ' ||
'where POList.fpbpohd_code = :poNum';

with	testThing as (qryGetSinglePOList)
select	*
from	testThing;


I get the following error:

LINE/COL ERROR
-------- -----------------------------------------
53/4 PL/SQL: SQL Statement ignored
53/23 PL/SQL: ORA-00928: missing SELECT keyword

[Updated on: Mon, 09 November 2009 13:56]

Report message to a moderator

Re: Using "with as" in a select statment. [message #430241 is a reply to message #430240] Mon, 09 November 2009 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the whole query in the variable.

Regards
Michel
Re: Using "with as" in a select statment. [message #430242 is a reply to message #430241] Mon, 09 November 2009 14:10 Go to previous messageGo to next message
TNK101
Messages: 4
Registered: November 2009
Junior Member
I don't follow.

The example I have given is contrived.

The varchar2 after the as statement will be different depending on a control variable.

Example:

procedure DoStuff(callType in varchar2, refCurOut out ref cursor)
begin
  if(callType = 'one') then
    with temp as (qry1)
    select *
    from temp
    where temp.col1 = something;
  elsif(callType = 'two') then
    with temp as (qry2)
    select *
    from temp
    where temp.col2 = something;
  end if;
end DoStuff;


I want to have a varchar2 used in the bracketted part of the as clause.

What is the syntax for that or is it not possible in PLSQL to do something this simple?
Re: Using "with as" in a select statment. [message #430245 is a reply to message #430242] Mon, 09 November 2009 14:34 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dynamic SQL and put the whole query in a string.

Regards
Michel
Previous Topic: ORA-01779: cannot modify a column which maps to a non key-preserved table
Next Topic: number format issue
Goto Forum:
  


Current Time: Sat Dec 10 20:35:18 CST 2016

Total time taken to generate the page: 0.05831 seconds