Simple Query

Database
Enthusiast

Simple Query

Hi,
Can anyone tell me why i am getting this error.
select * from emp a where (select emp_no from emp b where a.emp_no=b.emp_no) >5;
*** Failure 3706 Syntax error: expected something between ')' and '>'.
Statement# 1, Info =80
*** Total elapsed time was 1 second.

But this works fine in oracle database.

we can do this in different way but, i want to do it in same way. Could anyone suggest on the error.

10 REPLIES
Enthusiast

Re: Simple Query

i think foll is the right query...let know in case of wrong query..

select * from employee a
where 5 <
(select employee_number from employee b where a.employee_number=b.employee_number)
Teradata Employee

Re: Simple Query

Hello,

Exactly what are you trying to achieve is pretty ambigious. The query you are trying to write can be easily written as:

SELECT * FROM emp a WHERE a.emp_no = a.emp_no;

If you want to have a self join then you should be doing something as follows:

SELECT * FROM emp a INNER JOIN emp b ON a.emp_no = b.emp_no WHERE b.emp_no > 5;

HTH.

Regards,

Adeel
Enthusiast

Re: Simple Query

Hi Adeel,
in the where condition i want to a boolean expression. if it is true then it has to give me all the employee details.

This runs in oracle but not in teradata.
Please give some alternative for me.

select emp_id from emp a where (
(select emp_no from emp_sk where emp_id = a.emp_id and skill = 'AC_DT_TS') >=11 and
(select emp_no from emp_sk where emp_id = a.emp_id and skill = 'AC_DT_TS') <=15 and
(select emp_no from emp_sk where emp_id = a.emp_id and skill = 'AC_US') = 1 and
(select emp_no from emp_sk where emp_id = a.emp_id and skill = 'AC_EN') = 1 and
(select emp_no from emp_sk where emp_id = a.emp_id and skill = 'AC_Active') = 1)

Advance thanks

Teradata Employee

Re: Simple Query

Is it giving some sort of error?

Regards,

Adeel
Enthusiast

Re: Simple Query

Yes Adeel.The error is :
$
*** Failure 3706 Syntax error: expected something between ')' and '">="'.
Statement# 1, Info =252
*** Total elapsed time was 1 second.

Teradata Employee

Re: Simple Query

Ok, can you remove the SELECTs used in WHERE clause and re-write the query using JOINS?

I guess that is the only way to to something similar.

Regards,

Adeel
Enthusiast

Re: Simple Query

Hi Adeel,
Thanks for your quick reply. I tried in many ways but in vein.
Could you please suggest, how this can be acheived.

Thanks is advance.
Teradata Employee

Re: Simple Query

For that you need to specify the table DDLs and sample data, and what exactly you need to achieve, or you can try more. :)

Regards,

Adeel
Senior Apprentice

Re: Simple Query

Hi Monika,
did you try dhirajpalse's suggestion, it's working:

SELECT emp_id FROM emp a WHERE (
11 <= (SELECT emp_no FROM emp_sk WHERE emp_id = a.emp_id AND skill = 'AC_DT_TS') AND
15 >= (SELECT emp_no FROM emp_sk WHERE emp_id = a.emp_id AND skill = 'AC_DT_TS') AND
1 = (SELECT emp_no FROM emp_sk WHERE emp_id = a.emp_id AND skill = 'AC_US') AND
1 = (SELECT emp_no FROM emp_sk WHERE emp_id = a.emp_id AND skill = 'AC_EN') AND
1 = (SELECT emp_no FROM emp_sk WHERE emp_id = a.emp_id AND skill = 'AC_Active'))

You design seems to be a bit unusual, but i think this will return the same rows:
SELECT emp_id FROM emp a WHERE emp_id IN
(
SELECT emp_id
FROM emp_sk
GROUP BY 1
HAVING
COUNT(DISTINCT
CASE WHEN emp_no BETWEEN 11 AND 15 AND skill = 'AC_DT_TS' THEN 1
WHEN emp_no = 1 AND skill = 'AC_US' THEN 2
WHEN emp_no = 1 AND skill = 'AC_EN' THEN 3
WHEN emp_no = 1 AND skill = 'AC_Active' THEN 4
END) = 4
)

If the combination of "emp_no" and "skill" is unique per emp_id then you might get rid of the distinct:
HAVING
COUNT(CASE WHEN emp_no BETWEEN 11 AND 15 AND skill = 'AC_DT_TS' THEN 1 END) >= 1
AND
COUNT(CASE WHEN emp_no = 1 AND skill IN ('AC_US', 'AC_EN', 'AC_Active') THEN 1 END) = 3

And if the skills per emp_id are unique then it's down to:
HAVING
COUNT(CASE WHEN emp_no BETWEEN 11 AND 15 AND skill = 'AC_DT_TS' THEN 1
WHEN emp_no = 1 AND skill IN ('AC_US', 'AC_EN', 'AC_Active') THEN 1 END) = 4

If there are lots of rows you always might add emp_no and skills to the WHERE condition reducing the number of rows for the aggregate, rewriting the previous version:
WHERE
(emp_no BETWEEN 11 AND 15 AND skill = 'AC_DT_TS')
OR
emp_no = 1 AND skill IN ('AC_US', 'AC_EN', 'AC_Active')
GROUP BY 1
HAVING
COUNT(*) = 4

Any solution using aggregates might be easily enhanced, e.g.
- at least three out of four skill just use: ">=3"
- assign weights to skills and use SUM

Dieter