CODEWITHSUNDEEP
sqloracle-database
Mike
Mike

Reputation: 4405

ORA-00933 Error on Query of 'With' Table

I'm running into an ORA-00933: SQL command not properly ended error. What I am doing is querying on a table I built from a With query. I've been combing through Stack, trying to resolve this issue with every correctly answered question that I could find, but I still run into the error. It's something small and easy to fix I am sure. it's just beyond me at this point. The error occurs on the line with the second select statement:

;WITH sums 
     AS (SELECT a.client_number_id                     AS Client_Number_ID, 
                Count(Decode(a.sub_type_code, 'A', 1)) AS Applications, 
                Count(Decode(a.sub_type_code, 'S', 1)) AS License, 
                Count(Decode(a.sub_type_code, 'L', 1)) AS Lease 
         FROM   mta_spatial.mta_acquired_tenure_svw a 
         WHERE  a.tenure_type_description = 'Coal' 
         GROUP  BY a.client_number_id) 
SELECT client_number_id, 
       applications, 
       license, 
       lease, 
       applications + license + lease AS 'GrandTotal' 
FROM   sums;

Upvotes: 0

Views: 217

Answers (3)

Mike
Mike

Reputation: 4405

Thanks everyone for their contribution. I solved it when I removed the semicolon before the 'WITH' statement. I borrowed the query from another stack thread

To calculate sum() two alias named columns - in sql

and modified it to my own use. They had used a semicolon, so I didn't think to remove it. I'm not sure why they had that there to begin with. this is my first attempt at using the 'WITH' clause.

Upvotes: 0

I_am_Batman
I_am_Batman

Reputation: 915

Enclosing the alias in double quotes would make it case sensitive. In case you wish to make the alias case insensitive, write it without quotes. Oracle treats such cases as upper case by default. Therefore, Grandtotal, GRANDTOTAL, grandtotal would all return the desired result.

applications + license + lease AS GrandTotal

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

applications + license + lease AS 'GrandTotal'

should be

applications + license + lease AS "GrandTotal"
  • quotes are for string
  • double quotes for field names.

Upvotes: 4

Related Questions