Home » SQL & PL/SQL » SQL & PL/SQL » subquery factoring
subquery factoring [message #241745] Wed, 30 May 2007 09:35 Go to next message
ns_oracle
Messages: 2
Registered: May 2007
Junior Member
I have the following query works fine in SQL Plus 9.2.0.6.0 but not in SQL*Plus: Release 8.0.6.0.0. In SQL Plus 8.0 it is reurning unknown command beginning "WITH xun A..." - rest of line ignored.


WITH xun AS (select LU_NO, STATUS_DS, CREATE_TS, VERS_NO, EXPT_REC_CT from XMIT_UNIT xu join STATUS st on xu.status_cd = st.status_cd join SYSTEM_CONTROL sc on xu.CUR_OPEN_PER_NO = sc.cur_per_no and xu.CUR_OPEN_YR_NO = sc.CUR_YR_NO) select * from xun;


Any ideas?
TIA

NS


Re: subquery factoring [message #241748 is a reply to message #241745] Wed, 30 May 2007 09:44 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

The WITH clause became available in Oracle9i R2
Re: subquery factoring [message #241771 is a reply to message #241745] Wed, 30 May 2007 10:32 Go to previous messageGo to next message
ns_oracle
Messages: 2
Registered: May 2007
Junior Member
Thanks for responding. Do client tool also version specific? They are just a pass through to server, right?


Regards
Re: subquery factoring [message #241781 is a reply to message #241771] Wed, 30 May 2007 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Plus is a special client.
It "knows" what are the different statement beginnings and "with" is not part of 8.0 syntax.

Regards
Michel
Re: subquery factoring [message #241924 is a reply to message #241745] Thu, 31 May 2007 01:26 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
If you look closer you'll find that it is SQL*Plus error message (you can guess it by error code), not Oracle error message.
You can of course try to fool SQL*Plus using something like:
SQL> select * from (
2 with a as (select 1 from dual)
3 select * from a);

1
---------------
1

1 row selected.

Gints Plivna
http://www.gplivna.eu
Re: subquery factoring [message #241929 is a reply to message #241924] Thu, 31 May 2007 01:53 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is a good workaround. ./fa/2115/0/

Regards
Michel
Previous Topic: Program from V$Session not going away
Next Topic: teradata rollback functionality in oracle
Goto Forum:
  


Current Time: Sun Dec 11 00:21:17 CST 2016

Total time taken to generate the page: 0.10000 seconds