jasmeen kaur
asked on
i am getting an error : ORA-00933: SQL command not properly ended
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';
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';
What is B.column?
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:
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,
col VARCHAR2(20)
);
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';
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';
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the column had to be converted to date since it was not in date data type.
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.
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.