Need help with task

Database
Fan

Need help with task

Hello, All! I'm new to Teradata. Please, can anyone help me with that task?

Main aim for this task – it's to write most optimal set of update statements.

Below description of two tables contains information about statuses of products.

CREATE MULTISET TABLE PROD_TYPE_L

     (

      PROD_TYPE_L_ID INTEGER NOT NULL, --The primary key is generated when inserting
new record (surrogate key)

      REC_STATUS BYTEINT NOT NULL DEFAULT -1 ,

     
PROD_ID INTEGER NOT NULL,

      PROD_TYPE_ID SMALLINT NOT NULL)

UNIQUE PRIMARY INDEX PROD_TYPE_L_UPI ( PROD_TYPE_L_ID ) -- It can be regarded as the primary key

UNIQUE INDEX PROD_TYPE_L_USI ( PROD_ID ,PROD_TYPE_ID);

CREATE MULTISET TABLE PROD_TYPE_LS

     (

      PROD_TYPE_L_ID INTEGER NOT NULL, --A foreign key to the first table

      MSG_CHG_DATE TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

      REC_STATUS BYTEINT NOT NULL DEFAULT -1

     )

PRIMARY INDEX PROD_TYPE_LS_UPI ( PROD_TYPE_L_ID ) -
-It can be regarded as the primary key

UNIQUE INDEX PROD_TYPE_LS_USI ( PROD_TYPE_L_ID ,MSG_CHG_DATE );



First table it's Link, and the second table Satellite for the 1st.

These tables are used to store the history of communication product (PROD_ID) to its type (PROD_TYPE_ID) loaded from an external system.

At some point o
f time one product can have only one active type.

The relationship
(product with prod. status) and its current status is stored in PROD_TYPE_L.

Activity
of the records can be determined by the value of REC_STATUS = 1

The
Satellite (PROD_TYPE_LS ) is contain history of activation different statuses of each record in the Link.

REC_STATUS
in the Satellite must be synchronized with a REC_STATUS in the Link in the certain way.

One active
record in the Link must have no more than one active corresponding record in the Satellite. Not active record in the Link can't have any active record in the Satellite.

Following processing of message (data) is given to explain the status value.

When a new message
(data) is coming with changing date more than date in MSG_CHG_DATE of current active record in the table PROD_TYPE_L, then activated link with the appropriate combination of PROD_ID, PROD_TYPE_ID, if this appropriate record does not exist in the Link, a new record is inserted.

Previous active record in the Link status can be nulled if the coming message (data) activate another connection, otherwise the status doesn't change.

In the Satellite REC_STATUS set to zero of previous active record and adding new record with MSG_CHG_DATE equal to the modified date of the incoming message (data).

When a new message (data) is coming with changing date is less than the date in MSG_CHG_DATE of active record, that record inserting with not active status.

If that record isn't in the Satellite it's message (data) inserting as new record in the Satellite, also not active.

When a new message
(data) is coming with information similar to the already existing record in the database for key fields (PROD_ID, PROD_TYPE_ID) and date change in the Satellite, nothing is done, came entry is ignored completely.

What needed from me:

Need to write set of update statement (up to 4 units), which could bring that tables to a consistent state.

Provide opportunities to get the actual record based on a combination of appropriate records in the Link and in the Satellite, and set the correct statuses (REC_STATUS) for actual records, and set another records as not active (set statuses of another records with the same combination of key fields (PROD_ID, PROD_TYPE_ID) and date change in the Satellite to zero).

Creating staging tables is unacceptable for solving the problem.

Possible situations that need to be taken into account
:

1. Active record in the Link and not active record in the Satellite.

2.
Not active record in the Link and active record in the Satellite.

3. Multiple active
records in the Satellite at one record in the Link.

4.
Not active record in the Link and not active record in the Satellite, though they should be active both.

Sorry for my English.

Any help is appreciated.

Thanks in advance.



 

3 REPLIES
Fan

Re: Need help with task

I tried to solve this task by my self.

Below few solutions for this task.

Can anyone help me decide what variant the best one?

/**********************************First variant****************************************/

UPDATE    l

  FROM    PROD_TYPE_L        AS l

   SET    REC_STATUS = 0

UPDATE    ls

  FROM    PROD_TYPE_LS    AS ls

  SET    REC_STATUS = 0

UPDATE    ls

  FROM    PROD_TYPE_LS    ls

     ,    (SELECT

                MAX(MSG_CHG_DATE)    AS MAX_DATE,    

                l.PROD_ID        AS PROD_ID

           FROM            PROD_TYPE_L        l

                JOIN    PROD_TYPE_LS    B ON B.PROD_TYPE_L_ID = l.PROD_TYPE_L_ID

          GROUP BY  PROD_ID)    s

   SET    REC_STATUS            = 1

 WHERE    1                        = 1

   and    MSG_CHG_DATE            = MAX_DATE

   and    ls.PROD_TYPE_L_ID    in (SELECT

                                            DISTINCT PROD_TYPE_L_ID

                                      FROM    PROD_TYPE_L z

                                     WHERE    z.PROD_ID = s.PROD_ID)

UPDATE    l

  FROM    PROD_TYPE_L  l

     ,    PROD_TYPE_LS ls

   SET    REC_STATUS        = 1

WHERE    l.PROD_TYPE_L_ID    = ls.PROD_TYPE_L_ID

  and    ls.REC_STATUS    = 1

/******************************************************************************************/

/************************************Second variant**************************************/

UPDATE    ls

  FROM    PROD_TYPE_LS    ls

     ,    (SELECT

                MAX(MSG_CHG_DATE)    AS MAX_DATE,

                l.PROD_ID        AS PROD_ID

           FROM            PROD_TYPE_L        l

                JOIN    PROD_TYPE_LS    B ON B.PROD_TYPE_L_ID = l.PROD_TYPE_L_ID

          GROUP BY    PROD_ID) s

   SET    REC_STATUS = (CASE WHEN MSG_CHG_DATE = MAX_DATE THEN 1 ELSE 0 END)

 WHERE    1                        =    1

   and    ls.PROD_TYPE_L_ID    in (SELECT

                                            DISTINCT PROD_TYPE_L_ID

                                      FROM    PROD_TYPE_L z

                                     WHERE    z.PROD_ID = s.PROD_ID)

UPDATE    l

  FROM    PROD_TYPE_L l

   SET    REC_STATUS =  (SELECT

                                  MAX(REC_STATUS)

                             FROM PROD_TYPE_LS B

                            WHERE B.PROD_TYPE_L_ID = l.PROD_TYPE_L_ID)

                            WHERE B.DWH_WHS_FRMT_L_ID = l.DWH_WHS_FRMT_L_ID)

/******************************************************************************************/

/*************************************Third variant*************************************/

UPDATE    ls

  FROM    PROD_TYPE_LS    AS    ls

   SET    REC_STATUS = 0

UPDATE    ls

  FROM    PROD_TYPE_LS ls

     ,    (SELECT

                MAX(MSG_CHG_DATE) AS    MAX_DATE,

                l.PROD_ID        AS    PROD_ID

           FROM            PROD_TYPE_L        l

                JOIN    PROD_TYPE_LS    B ON B.PROD_TYPE_L_ID = l.PROD_TYPE_L_ID

          GROUP BY    PROD_ID) s

  SET    REC_STATUS = 1

WHERE    1                        =    1

  and    ls.PROD_TYPE_L_ID    in    (SELECT

                                            DISTINCT PROD_TYPE_L_ID

                                       FROM    PROD_TYPE_L    z

                                      WHERE    z.PROD_ID = s.PROD_ID)

  and    ls.MSG_CHG_DATE        =    s.MAX_DATE

UPDATE    l

  FROM    PROD_TYPE_L l

   SET    l.REC_STATUS        =  (SELECT

                                            MAX(REC_STATUS)        AS MAX_STATUS

                                      FROM PROD_TYPE_LS    B

                                     WHERE B.PROD_TYPE_L_ID        = l.PROD_TYPE_L_ID)

/******************************************************************************************/

/***********************************Fourth variant***************************************/

UPDATE    ls

  FROM    PROD_TYPE_LS    AS    ls

   SET    REC_STATUS = 0

UPDATE    ls

  FROM    PROD_TYPE_LS        ls

     ,    (SELECT

                l.PROD_TYPE_L_ID        AS PROD_TYPE_L_ID

              ,    B.MSG_CHG_DATE        AS MSG_CHG_DATE

              ,    RANK( ) OVER(PARTITION    BY PROD_ID ORDER BY MSG_CHG_DATE DESC) AS RANK1

           FROM            PROD_TYPE_L    l

                JOIN    PROD_TYPE_LS    B ON B.PROD_TYPE_L_ID = l.PROD_TYPE_L_ID

        QUALIFY    RANK1    = 1)    s

   SET    REC_STATUS            = 1

 WHERE    1                        = 1

   and    ls.PROD_TYPE_L_ID    = s.PROD_TYPE_L_ID

   and    ls.MSG_CHG_DATE        = s.MSG_CHG_DATE

UPDATE    l

  FROM    PROD_TYPE_L l

   SET    l.REC_STATUS        =  (SELECT

                                            MAX(REC_STATUS)            AS MAX_STATUS

                                      FROM    PROD_TYPE_LS    B

                                     WHERE    B.PROD_TYPE_L_ID            = l.PROD_TYPE_L_ID)

/******************************************************************************************/

Enthusiast

Re: Need help with task

Hi Alkuin,

I didn't catched your problem completly. In general erasing a flag and then setting it in some cases is inefficient and dangerous. So I would concentrate on solution 2. Additional I would only update records, which are changing, so for solution 2, update 1 I would add:

and REC_STATUS<> (CASE WHEN MSG_CHG_DATE = MAX_DATE THEN 1 ELSE 0 END)

Also consider to make both updates in one transaction.

BR Roland

Fan

Re: Need help with task

Hi, BR Roland!

Thank you for your answer and addition to for solution 2. Before I saw your answer, did another one variant:

/***********************************Fifth variant***************************************/

UPDATE    ls

  FROM    PROD_TYPE_LS        ls

     ,    (SELECT

                l.PROD_TYPE_L_ID        AS PROD_TYPE_L_ID

              ,    B.MSG_CHG_DATE        AS MSG_CHG_DATE

              ,    RANK( ) OVER(PARTITION    BY DWH_WHS_ID ORDER BY MSG_CHG_DATE DESC) AS RANK1

              , CASE WHEN RANK1 = 1 THEN 1 ELSE 0 END AS STAT

           FROM            PROD_TYPE_L    l

                JOIN    PROD_TYPE_LS    B ON B.PROD_TYPE_L_ID = l.PROD_TYPE_L_ID

         )    s

   SET    REC_STATUS            = STAT

 WHERE    1                        = 1

   and    ls.PROD_TYPE_L_ID    = s.PROD_TYPE_L_ID

   and    ls.MSG_CHG_DATE        = s.MSG_CHG_DATE

UPDATE    l

  FROM    PROD_TYPE_L l

   SET    l.REC_STATUS        =  (SELECT

                                            MAX(REC_STATUS)            AS MAX_STATUS

                                      FROM    PROD_TYPE_LS    B

                                     WHERE    B.PROD_TYPE_L_ID            = l.PROD_TYPE_L_ID)

/******************************************************************************************/

I'll complete my task by mixing last variant and your addition.

With best regards,

Alkuin.