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


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

  • Follow
  • Get RSS Feed

1 Answer

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


    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.



    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


      t1.control_id = t5.control_id

      and t1.account_num = t5.account_num;

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