InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 15:35:31 GMT20http://www.teradata.com/teradataforum/Topic11673-9-1.aspxHi 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 SUMDieterMon, 02 Jun 2008 07:58:24 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxFor that you need to specify the table DDLs and sample data, and what exactly you need to achieve, or you can try more. :)Regards,AdeelMon, 02 Jun 2008 01:19:57 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxHi 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.Mon, 02 Jun 2008 01:05:46 GMTMonikahttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxOk, 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,AdeelMon, 02 Jun 2008 00:51:12 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxYes Adeel.The error is :$ *** Failure 3706 Syntax error: expected something between ')' and '">="'. Statement# 1, Info =252 *** Total elapsed time was 1 second.Mon, 02 Jun 2008 00:46:52 GMTMonikahttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxIs it giving some sort of error?Regards,AdeelSun, 01 Jun 2008 23:32:39 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxHi 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 thanksSat, 31 May 2008 07:28:28 GMTMonikahttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxHello,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,AdeelFri, 30 May 2008 05:20:06 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxi 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)Fri, 30 May 2008 03:06:52 GMTdhirajpalsehttp://www.teradata.com/teradataforum/Topic11673-9-1.aspxHi,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.Fri, 30 May 2008 02:48:35 GMTMonika