jned29 jned29 - 2 months ago 11
SQL Question

SQL command not properly ended for Union All

I'm having trouble understanding the error on my code. Could somebody help me out?

Here's my sql query

SELECT S.SUBSTR(ASSIGNED_NUMBER, -4) FROM (
SELECT SUBSTR(ASSIGNED_NUMBER, -4) AS SDATE FROM TBL_DATA1 GROUP BY SUBSTR(ASSIGNED_NUMBER, -4)
UNION ALL
SELECT SUBSTR(ASSIGNED_NUMBER, -4) AS SDATE FROM TBL_DATA2 GROUP BY SUBSTR(ASSIGNED_NUMBER, -4)
) AS S GROUP BY S.SUBSTR(ASSIGNED_NUMBER, -4);


Note: Originally, My outer column is S.SDATE and edited it to S.SUBSTR(ASSIGNED_NUMBER, -4)
.

Output:

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"


By the way, the sample date in my
ASSIGNED_NUMBER
column is
58-2016
.
I use substring to get the 2016

Answer

You are using S.SUBSTR(ASSIGNED_NUMBER, -4) when you have given the columns in the subquery alias of SDATE.

Also, when using subquery the naming should be ) s not ) AS S

Your query should be

SELECT S.SDATE FROM (
   SELECT SUBSTR(ASSIGNED_NUMBER, -4) AS SDATE FROM TBL_DATA1 GROUP BY SUBSTR(ASSIGNED_NUMBER, -4)
   UNION ALL
   SELECT SUBSTR(ASSIGNED_NUMBER, -4) AS SDATE FROM TBL_DATA2 GROUP BY SUBSTR(ASSIGNED_NUMBER, -4)
) S GROUP BY S.SDATE;