Skip to Content
avatar image
Former Member

Control Null values in a table with Previous values

Hi Team ,


We are into a tricky situation and hoping some inputs.


We have two tables say A and B

They have a Common filed A.controlId and B.control ID on which that are Joined through a Outer Join


A is snapshot table and have only a Particular time range data current .


Fields in Table A will have Only one record say for an account number

Control ID

Created Date

Status

Account Number


Fields In Table B SCD will contain multiple records for an account with there status and previous status

Control ID

Created Date

Previous Status

Account Number


In table B the data is not pushed out on regular basis and we can have Null value for Previous status.

Issue is because Of data not available at times in table B , an account number can show null values for Previous status, we are looking for a Solution through which it should automatically take previous status values if there is null value's


Can this be done at Universe level ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Apr 27, 2016 at 10:32 AM

    Hi,

    You can create a Derived table from Table B where you will just add the condition in Where Clause of that SQL as PreviousStatus In Not Null and CreateDate=Max(CreateDate) for that Control Id.

    So this derived table will always pull the latest Non Null Previous Status.

    Hope this helps.

    Note: This is just a pseudo code you may have to convert that into valid SQL statement.

    Regards

    Niraj

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      yes Niraj's solution will work.

      Pls refer to below sample data that I generated :-

      Table Name: test1

      The query for derived table would be :-

      select 1.control_id,t1.account_num, t1.created_date, t1.prev_status,t5.prev_status 

      from test1 t1, ( select t4.control_id,t4.account_num,t4.prev_status

                              from (select t2.control_id, t2.account_num,max(t2.created_date) created_dt from test1                                    t2 where t2.prev_status is not null group by  t2.control_id, t2.account_num ) t3,

                                      test1 t4

                              where t3.control_id = t4.control_id

                              and t3.account_num = t4.account_num

                              and t3.created_dt = t4.created_date) t5

      where

      t1.control_id = t5.control_id

      and t1.account_num = t5.account_num;

      Here's the output of the Derived table query :-