Archives of the TeradataForum

Message Posted: Mon, 18 Mar 2002 @ 14:57:50 GMT

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

Hi,

May be this may help - this is the same kind of situation i was in and i had do away with stored procedures and hence do away with cursors and replace it with only sql.

This is what i did : i made use of the UPDATE FROM statement. Here you could create a derived table on the run and do what ever computation you would necessary want to do within the derived table and then use that to update your rows.

ie. UPDATE  FROM (
          SEL ..............
      ) DERV(COL1,COL2....)
    SET C1 = ..,C2= ..
    WHERE ......

In fact i replaced all my cursors using the above and more over no STORED PROCEDURES in use.

Now the SELECT that is used inside (as shown above) can again be derived.

ie.
    UPDATE  FROM (
             SEL ........ FROM (
                               SEL ...... FROM ) DERV(..)
     ) DERV1(COL1,COL2.....)
     SET C1=...,C2 = ..
     WHERE ....

So this deriving does give a flexibility if the logic you are trying to arrive at takes more steps, more over you could the CASE construct within the SELECTS which makes it more flexible.

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