jned29 jned29 - 1 month ago 7
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;