NCR Teradata Select case

[kartdb]

THIS IS A SAMPLE QUERY BUT MY QUERY LOoKS ALMOST THE SAME...I am getting the 2646 no more spool error..can any one advice me onit
select CASE
WHEN EMPCD='12' THEN 'SAMPLE'
WHEN EMPCD='13' THEN 'SAMPLE1'
END AS TEST1,
CASE
WHEN DEPTCD='12' THEN 'SAMPLE'
WHEN DEPTCD='13' THEN 'SAMPLE1'
END AS TEST2
FROM EMP,DEPT

 

[tdgulbra]

This is a product join. Every EMP row will be joined to every DEPT row. Is that really what you want?

If not, add the following line at the end

WHERE EMP.DEPTCD = DEPT.DEPTCD

This assumes that each EMP only works in one DEPT and that the EMP row has a DEPTCD for the EMP.

Let me know if I missed what you are trying to do.

Tony

 

[kartdb]

Thank you for the reply....

What I wanted is not the relation between the two.I want to validate separatly both the values but should come in one SELECT STATEMENT...

......

 

[tdgulbra]

I don't think you are going to get the right answer with what you have coded. You will get the number of rows in EMP times the number of rows in DEPT with null values for the false legs of your case statements. I would guess that this multiplication is what is causing your spool problem.

If there are only 2 values you are looking for in each table, perhaps you could qualify in a where clause:

WHERE EMP.EMPCD IN ('12','13')
OR DEPT.DEPTCD IN ('12','13')

This would at least screen out rows that do not apply to your situation.

 

[BillDHS]

I don't think you can do it in one "Select" without a product join. You could do it with a derived table, or Union query so it is one query with multiple select statements.

 

[tdgulbra]

BILLDHS - Great call on the UNION. I didn't even think of that.

Tony

 

[kartdb]

Thank you it helped...