Archives of the TeradataForum

Message Posted: Fri, 11 Jan 2013 @ 09:16:11 GMT

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

<-- Anonymously Posted: Friday, January 11, 2013 00:28 -->

I am trying to convert the below two statements in to one merge-in SQL but nothing is working as expected. Any help is greatly appreciated.

     INSERT INTO DB_X.TBL_S
     (
     A,
     B,
     C,
     D,
     E,
     F,
     G,
     H)
     Select
     A,
     B,
     C,
     D,
     E,
     F,
     G,
     H
     From
     (
     SELECT
     G_T1.B as B,
     G_T1.A as A,
     G_T1.C as C,
     G_T1.D as D,
     G_T1.E as E,
     G_T1.F as F,
     G_T1.G as G,
     G_T1.H as H ,
     CASE WHEN G_T0.B IS NULL THEN 'I' ELSE 'U' END as STATUS_IND,
     CASE WHEN G_T0.B IS NULL THEN 'I' ELSE 'U' END as STATUS
     FROM DB_X.TBL_S G_T0
        RIGHT OUTER JOIN DB_Y.TBL_S G_T1
        ON  G_T1.A = G_T0.A
        AND G_T1.B = G_T0.B
        AND G_T1.C = G_T0.C
        WHERE G_T1.A = 'ZZ' and STATUS = 'I')A;


     UPDATE DB_X.TBL_S
     FROM
     (
     SELECT
     G_T1.B as B,
     G_T1.A as A,
     G_T1.C as C,
     G_T1.D as D,
     G_T1.E as E,
     G_T1.F as F,
     G_T1.G as G,
     G_T1.H as H ,
     CASE WHEN G_T0.B IS NULL THEN 'I' ELSE 'U' END as STATUS_IND,
     CASE WHEN G_T0.B IS NULL THEN 'I' ELSE 'U' END as STATUS
     FROM DB_X.TBL_S G_T0
        RIGHT OUTER JOIN DB_Y.TBL_S G_T1
        ON  G_T1.A = G_T0.A
        AND G_T1.B = G_T0.B
        AND G_T1.C = G_T0.C
        WHERE G_T1.A? = 'ZZ' and STATUS = 'U')A
     SET
     D = A.D,
     E = A.E,
     F = A.F,
     G = A.G,
     H = A.H
     where
     DB_X.TBL_S.A = A.A and
     DB_X.TBL_S.C = A.C and
     DB_X.TBL_S.B = A.B
     ;

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