I am using something like below , the query runs fine in oracle SQL developer.
But I would like to generate a text file by running the script, so I added the Spool command, and those settings, I tried to run it in SQL developer or SQLplus, they both fail.
In SQL developer: I got:
ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 16 Column: 1,542
In SQLPlus, but I got an error: SP2-0734: unknown command beginning "CteRace As..." - rest of line ignored.
it seems it doesn't understand the common expression table.
-- SQL code below:
set heading off
set feedback off
set newpage none
set echo off
set termout off
set verify off
set trimspool on
set pause off
set linesize 1000
set pagesize 0
spool c:\csvtest\NED.dat
with cteIsConfidential as (Select distinct L.Studentid
From Log L
Inner Join gen G
On G.ID = L.LOGTYPEID
And G.Cat = 'logtype'
And G.name = 'Confidential Student'),
CteRace As (Select Studentid,Max(Racecd)as raceCd From Studentrace Group By Studentid)
SELECT
St.Student_Number,
st.Enroll_Status,
St.Schoolid,
St.First_Name,
st.Middle_Name,
St.Last_Name,
St.Gender,
Ethnicity || NVL(sr.raceCd,'') EthnicityRace,
To_Char(Dob,'yyyymmdd') Birthdate,
st.Grade_Level,
null Apartment,
MAILING_CITY,
Mailing_State,
Mailing_Zip,
Nvl2(Co.Studentid,'Y','N')Confidential,
Sps_Student.Get_504_Status(St.Student_number) Status504
From Students St
Left Join Cteisconfidential Co On St.Id=Co.Studentid
Left join cteRace sr on st.ID=sr.studentID
Where Enroll_Status In (-1,0)