Archives of the TeradataForum

Message Posted: Thu, 01 May 2009 @ 01:22:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

If I run the query below with the 4 tables based upon the template, it completes successfully.

I'm guessing you have not supplied the real query (if you have, I would suggest a different naming convention :-). Is it possible that in preparing the query for submission, you inadvertantly fixed the problem?

Try running my version after you create the 4 tables.

The only changes I've made are to correct the set clause (you can't put the table name on the LHS) and the single quote after the 2.

Follwing is a template you can use to create the 4 tables A->D

     create table table_X (
         column1 varchar (10),
         column2 varchar (10),
         column3 varchar (10),
         column4 varchar (10)
     );

     update table_A
     set column1 =
             case
                 when table_A.column2=table_B.column2
                          and  table_A.column2=table_C.column2
                          and table_C.column3=table_D.column3
                                 then (
                                     case
                                         when table_B.column4 = '1' and
                                             table_C.column4 = '2'
                                                 then  'Y'
                                                 else  'N'
                                      end
                                   )
                  end

Also, it is most curious to put the join conditions in the case statement. The when clause is supposed to evaluate to a true or false condition. A join is about matching data from one table to another, not being true or false. However syntactically it appears acceptable.

You might want to try moving the join conditions to a where clause as in:

     update table_A
     set column1 =
             case
                 when table_B.column4 = '1' and table_C.column4 = '2'
                     then  'Y'
                     else  'N'
             end
     where table_A.column2=table_B.column2
         and  table_A.column2=table_C.column2
         and table_C.column3=table_D.column3

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>