i am getting an error : ORA-00933: SQL command not properly ended

jasmeen kaur
jasmeen kaur used Ask the Experts™
on
on running the below query, i am getting the error ORA-00933: SQL command not properly ended

select to_char(cast(A.xx as date),'dd/mm/yyyy')as clnt_date_of_birth from t2 A join
t2 B on A.clnt_id=B.exrf_table_id
where B.column='1004620399';
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
von AxlSystem Engineer

Commented:
What is B.column?
johnsoneSenior Oracle DBA

Commented:
Assuming that those are not the real table and column names, there is actually no issue with that query, other that you cannot create a table with a column named COLUMN easily.  The sample is a self join and if you create the table this way:
CREATE TABLE t2 
  ( 
     xx            VARCHAR2(10), 
     clnt_id       NUMBER, 
     exrf_table_id NUMBER, 
     col           VARCHAR2(20) 
  ); 

Open in new window

Then run the query:
SELECT To_char(Cast(A.xx AS DATE), 'dd/mm/yyyy')AS clnt_date_of_birth 
FROM   t2 A 
       JOIN t2 B 
         ON A.clnt_id = B.exrf_table_id 
WHERE  B.col = '1004620399'; 

Open in new window

You get no errors.

So, you need to post the real query with the real column and table names.  Something may be odd with one of the identifiers.

Also, why are you casting A.XX to a date?  One would assume that the data type is already date.  Casting a DATE to a DATE is pretty useless.

If you really had a column with the name COLUMN, you can use these commands to generate your sample:
CREATE TABLE t2 
  ( 
     xx            VARCHAR2(10), 
     clnt_id       NUMBER, 
     exrf_table_id NUMBER, 
     "COLUMN"      VARCHAR2(20) 
  ); 
SELECT To_char(Cast(A.xx AS DATE), 'dd/mm/yyyy')AS clnt_date_of_birth 
FROM   t2 A 
       join t2 B 
         ON A.clnt_id = B.exrf_table_id 
WHERE  B."COLUMN" = '1004620399'; 

Open in new window

Still won't generate the error.  Even if you leave the quotes off the select, it will generate an error, but not the one you are getting.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
If you are calling that select statement from inside code like .Net, remove the ending semi-colon: ';'.  You normally only need that inside PL/SQL, sqlplus or similar query tool.

That said:  What data type is A.xx and why cast it as a date then turn around and make it a string?

Author

Commented:
the column had to be converted to date since it was not in date data type.
johnsoneSenior Oracle DBA

Commented:
If the column is not a DATE datatype, then I wouldn't convert it to a date.  If you have a bad date, your query will fail on the conversion.  You are better off doing string manipulation to get it into the format you want.

If you really want to convert it to a DATE, you should specify the date format.  Never rely on implicit conversion.  It is way too easy for someone to change their default date format and break your query.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial