Query Issue

Database
Enthusiast

Query Issue

Hi All,

i got stuck in between with the query.

SELECT  EMPNO, ENAME, 
CAST(JOB ||'_'|| MGR AS VARCHAR2(50)),
HIREDATE(FORMAT AS 'YYYY-MM-DD') AS JDATE,
CASE WHEN SAL <500 THEN 'LOW',
WHEN SAL >500 THEN 'MEDIUM',
AND SAL<=1000 THEN 'AVERAGE',
ELSE 'HIGH',
END AS TOT_SAL,
COMM,
DEPTNO
FROM EMP;

it's throwing an error missing right paranthesis ,i suppose the syntax issue ,can any one suggest me the resolution......

Note : Database Oracle Table EMP.

6 REPLIES
Enthusiast

Re: Query Issue

not sure what your note field states. however the query in TD can be as below.

SELECT EMPNO, ENAME,

cast((JOB ||'_'|| MGR)  AS VARCHAR(50)),

HIREDATE (FORMAT  'YYYY-MM-DD') AS JDATE,

CASE 

WHEN SAL <500 THEN 'LOW'

WHEN SAL >500 THEN 'MEDIUM'

when SAL<=1000 THEN 'AVERAGE'

ELSE 'HIGH'

END AS TOT_SAL,

COMM,

DEPTNO

FROM EMP;

Enthusiast

Re: Query Issue

You put comas in case when. I use to_char too for date conv.

SELECT EMPNO, ENAME,

CAST(JOB ||'_'|| MGR AS VARCHAR2(50)),

TO_CHAR(HIREDATE,'YYYY-MM-DD') AS JDATE,

CASE 

WHEN SAL <500 THEN 'LOW'

WHEN SAL >500 THEN 'MEDIUM'

when SAL<=1000 THEN 'AVERAGE'

ELSE 'HIGH'

END AS TOT_SAL,

COMM,

DEPTNO

FROM EMP;

Enthusiast

Re: Query Issue

Raj,

Excellent thanks for the inputs.

Enthusiast

Re: Query Issue

Raj,

Quick suggestion is it advisable to include Between the CASE statement and specify the range to get the desired out put?

CASE 

WHEN SAL BETWEEN 800 AND 1300 THEN 'LOW'

WHEN SAL BETWEEN 1300 AND 1600 THEN 'MEDIUM'

WHEN SAL BETWEEN 1600 AND 3000 THEN 'AVERAGE'

ELSE 'HIGH'

i have tested it's working fine.

SELECT EMPNO, ENAME,


CAST(JOB ||'_'|| MGR AS VARCHAR2(50)), TO_CHAR(HIREDATE,'YYYY-MM-DD') AS JDATE, SAL,

CASE

WHEN SAL BETWEEN 800 AND 1300 THEN 'LOW'

WHEN SAL BETWEEN 1300 AND 1600 THEN 'MEDIUM'

WHEN SAL BETWEEN 1600 AND 3000 THEN 'AVERAGE'

ELSE 'HIGH'

END AS TOT_SAL,

COMM, DEPTNO FROM EMP;

Just one last doubt HIREDATE (FORMAT  'YYYY-MM-DD') AS JDATE,  

you have suggested the below 

TO_CHAR(HIREDATE,'YYYY-MM-DD') AS JDATE,

for date converstion why you have used TO_CHAR?

Enthusiast

Re: Query Issue

If the requirement drives for it and you get what is required and there is no performance issue, then good.

TO_CHAR is oracle function. I am confused here, are you learning oracle here in Teradata forum ? :)

Enthusiast

Re: Query Issue

Thanks for the input .i am learning TD only but iam using the source from oracle to pull the data using BTEQ.