All Forums Database
Sunar 59 posts Joined 02/08
30 May 2008
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.

dhirajpalse 36 posts Joined 01/08
30 May 2008

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)

Dhiraj

Adeel Chaudhry 773 posts Joined 04/08
30 May 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

Sunar 59 posts Joined 02/08
31 May 2008

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

Adeel Chaudhry 773 posts Joined 04/08
01 Jun 2008

Is it giving some sort of error?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Sunar 59 posts Joined 02/08
02 Jun 2008

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

Adeel Chaudhry 773 posts Joined 04/08
02 Jun 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

Sunar 59 posts Joined 02/08
02 Jun 2008

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.

Adeel Chaudhry 773 posts Joined 04/08
02 Jun 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
02 Jun 2008

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') AND15 >= (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 SUMDieter

Dieter

s@ir@m 35 posts Joined 05/13
06 Nov 2013

Hi all,
we have d_emp table with 1000 records .
just i wrote a qurey 
sel top 10 * from d_emp;
where it filters in 10 records !
directly filters base table (or) total 1000 records get spool and after filters the records.
 
Regards
Sai

You must sign in to leave a comment.