0

I have a query below from source this is how Active flag for target is derived

 select case when active_end_date is null then 'Y' else 'N' end
    from csi_item_instances cii
    where instance_id = <<INSTALL BASE ID>> --- (MP.INSTALL_BASE_ID)

I am comparing the active field value using the SQL below, is there better way to do this?

 select * from stgdba.Stg_s_csi_item_instances cii, MDHDBA.M_CUSTOMER_PRODUCT mp
    where cii.instance_id= MP.INSTALL_BASE_ID
    and cii.active_end_date is null
    and MP.ACTIVE_FLAG = 'N'
user803860
  • 299
  • 3
  • 4
  • 13
  • Your queries appear to be contradictory. Your first suggests that MP_ACTIVE_FLAG in the second is N when active_end_date is not null. Your second, therefore selects where active_end_date is both null and not null, which is a contradiction. – Ben Nov 28 '12 at 21:52
  • In first qry i am saying active end flag is null that means project is still active so mark it as active in the target column, i have a typo in second qry it should be 'Y' – user803860 Nov 29 '12 at 16:14

1 Answers1

0

If that value is to be permanently calculated like that, you could do that in a view / computed column, which would make the logic a bit more permanent and not repeated all over the place.

(Stylistically, I would also try using ANSI joins a bit more.)

Andrew
  • 26,629
  • 5
  • 63
  • 86