All Forums Database
rakeshsapbo 36 posts Joined 04/13
11 Sep 2014
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.

Rakesh Reddy G
krishaneesh 140 posts Joined 04/13
11 Sep 2014

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;

Raja_KT 1246 posts Joined 07/09
11 Sep 2014

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;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

rakeshsapbo 36 posts Joined 04/13
12 Sep 2014

Raj,
Excellent thanks for the inputs.

Rakesh Reddy G

rakeshsapbo 36 posts Joined 04/13
12 Sep 2014

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?
 

Rakesh Reddy G

Raja_KT 1246 posts Joined 07/09
12 Sep 2014

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 ? :)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

rakeshsapbo 36 posts Joined 04/13
12 Sep 2014

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

Rakesh Reddy G

You must sign in to leave a comment.