none
TSQL Query

    Question

  • I have three tables #Factsource, #Fact and #DimSource

    #factSource and #fact are joined on (Rx_Identifier)

    #FactSource and #DimSource are joined on (Rx_Pres_No, Refill_no)

    I am loading the #Fact from #factSource everyday by joining on Rx_Identifier for incrementals.

    But the some of the rows which are in #DimSource are not in #FactSource, those i need to insert into #Fact

    Data is like :

    Table: #FactSource

    Rx_Identifier Rx_Pres_no Refill_no

    100 10 0

    101 10 1

    Table: #Fact

    Rx_Identifier Rx_Pres_no Refill_no

    100 10 0

    101 10 1

    Table:#DimSource

    Rx_Pres_no Refill_no

    10 0

    10 1

    20 1

    The third row from table #Dimsource is missing in #FactSource, so i want to insert that into #fact.

    I wrote the below query and it is inserting that record into #Fact table everytime i run the query.The row is

    getting duplicated in #Fact table. Where is my query going wrong.

    Query i wrote:

    INSERT INTO #FACT (RX_IDENTIFIER,RX_PRES_NO,REFILL_NO)

    SELECT ISNULL(SO.RX_IDENTIFIER,-1),DIM.RX_PRES_NO,DIM.REFILL_NO FROM #DIMSource DIM LEFT JOIN #FactSource SO ON DIM.RX_PRES_NO = SO.RX_PRES_NO AND DIM.REFILL_NO = SO.REFILL_NO LEFT JOIN #FACT FA ON SO.RX_IDENTIFIER = FA.RX_IDENTIFIER WHERE SO.RX_PRES_NO IS NULL AND SO.REFILL_NO IS NULL AND FA.RX_IDENTIFIER IS NULL AND SO.RX_IDENTIFIER IS NULL

    Tuesday, November 19, 2013 8:47 PM

Answers

  • Problem is you are inserting the values into #Fact table.

    But you are left join is on #FactSource  where you are not inserting any values so that left join will always returns the row.
    Also you are inserting -1 for RX_IDENTIFIER in #Fact table, so the second left join and respective where condition will Evaluate to true for the row identified  by the first left join.


    Try this code.I changed the first left join to Fact table.

    Declare @FactSource table(Rx_Identifier int, Rx_Pres_no  int,   Refill_no int);
    Declare @Fact table(Rx_Identifier int, Rx_Pres_no  int,   Refill_no int);
    Declare @DimSource table(Rx_Pres_no  int,   Refill_no int);
    
    Insert into @FactSource Values(100,10,0)
    Insert into @FactSource Values(101,10,1)
    Insert into @Fact Values(100,10,0)
    Insert into @Fact Values(101,10,1)
    Insert into @DimSource Values(10,0)
    Insert into @DimSource Values(10,1)
    Insert into @DimSource Values(20,1)
    
    INSERT INTO @FACT (RX_IDENTIFIER,RX_PRES_NO,REFILL_NO)
    
    SELECT ISNULL(SO.RX_IDENTIFIER,-1),DIM.RX_PRES_NO,DIM.REFILL_NO
    FROM @DIMSource DIM
    LEFT JOIN @FACT SO
    	ON DIM.RX_PRES_NO = SO.RX_PRES_NO
    	  AND DIM.REFILL_NO = SO.REFILL_NO
    LEFT  JOIN @FactSource FA
    	ON SO.RX_IDENTIFIER = FA.RX_IDENTIFIER
    WHERE SO.RX_PRES_NO IS NULL
    	AND SO.REFILL_NO IS NULL
    	AND FA.RX_IDENTIFIER IS NULL
    	AND SO.RX_IDENTIFIER IS NULL
    
    select * from @FACT
    
    INSERT INTO @FACT (RX_IDENTIFIER,RX_PRES_NO,REFILL_NO)
    SELECT ISNULL(SO.RX_IDENTIFIER,-1),DIM.RX_PRES_NO,DIM.REFILL_NO
    FROM @DIMSource DIM
    LEFT JOIN @FACT SO
    	ON DIM.RX_PRES_NO = SO.RX_PRES_NO
    	  AND DIM.REFILL_NO = SO.REFILL_NO
    LEFT  JOIN @FactSource FA
    	ON SO.RX_IDENTIFIER = FA.RX_IDENTIFIER
    WHERE SO.RX_PRES_NO IS NULL
    	AND SO.REFILL_NO IS NULL
    	AND FA.RX_IDENTIFIER IS NULL
    	AND SO.RX_IDENTIFIER IS NULL
    
    select * from @FACT



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    Tuesday, November 19, 2013 9:10 PM

All replies

  • Hi, i tested your code and it work fine just please add a distinct clause may be there is some duplicated row after you select statement

    create  Table #FactSource (Rx_Identifier int, Rx_Pres_no int, Refill_no int)
    create Table #Fact(Rx_Identifier int, Rx_Pres_no int,Refill_no int)
    create Table #DimSource(Rx_Pres_no int, Refill_no int)
    
    insert  #FactSource values (100 ,10 ,0)
    
    insert  #FactSource values (101 ,10 ,1)
    
    
    
    
    insert  #Fact values (100 ,10 ,0)
    
    insert  #Fact values (101 ,10 ,1)
    
    insert #DimSource values (10,0)
    insert #DimSource values (10,1)
    insert #DimSource values (20,1)
    
    INSERT INTO #FACT (RX_IDENTIFIER,RX_PRES_NO,REFILL_NO)
    
    SELECT distinct ISNULL(SO.RX_IDENTIFIER,-1),DIM.RX_PRES_NO,DIM.REFILL_NO 
    FROM #DIMSource DIM LEFT JOIN #FactSource SO 
    ON DIM.RX_PRES_NO = SO.RX_PRES_NO AND DIM.REFILL_NO = SO.REFILL_NO LEFT JOIN #FACT FA 
    ON SO.RX_IDENTIFIER = FA.RX_IDENTIFIER 
    WHERE SO.RX_PRES_NO IS NULL AND SO.REFILL_NO IS NULL AND FA.RX_IDENTIFIER IS NULL AND SO.RX_IDENTIFIER IS NULL
    Best Regards.


    • Edited by KH MR Tuesday, November 19, 2013 9:05 PM
    Tuesday, November 19, 2013 9:04 PM
  • Problem is you are inserting the values into #Fact table.

    But you are left join is on #FactSource  where you are not inserting any values so that left join will always returns the row.
    Also you are inserting -1 for RX_IDENTIFIER in #Fact table, so the second left join and respective where condition will Evaluate to true for the row identified  by the first left join.


    Try this code.I changed the first left join to Fact table.

    Declare @FactSource table(Rx_Identifier int, Rx_Pres_no  int,   Refill_no int);
    Declare @Fact table(Rx_Identifier int, Rx_Pres_no  int,   Refill_no int);
    Declare @DimSource table(Rx_Pres_no  int,   Refill_no int);
    
    Insert into @FactSource Values(100,10,0)
    Insert into @FactSource Values(101,10,1)
    Insert into @Fact Values(100,10,0)
    Insert into @Fact Values(101,10,1)
    Insert into @DimSource Values(10,0)
    Insert into @DimSource Values(10,1)
    Insert into @DimSource Values(20,1)
    
    INSERT INTO @FACT (RX_IDENTIFIER,RX_PRES_NO,REFILL_NO)
    
    SELECT ISNULL(SO.RX_IDENTIFIER,-1),DIM.RX_PRES_NO,DIM.REFILL_NO
    FROM @DIMSource DIM
    LEFT JOIN @FACT SO
    	ON DIM.RX_PRES_NO = SO.RX_PRES_NO
    	  AND DIM.REFILL_NO = SO.REFILL_NO
    LEFT  JOIN @FactSource FA
    	ON SO.RX_IDENTIFIER = FA.RX_IDENTIFIER
    WHERE SO.RX_PRES_NO IS NULL
    	AND SO.REFILL_NO IS NULL
    	AND FA.RX_IDENTIFIER IS NULL
    	AND SO.RX_IDENTIFIER IS NULL
    
    select * from @FACT
    
    INSERT INTO @FACT (RX_IDENTIFIER,RX_PRES_NO,REFILL_NO)
    SELECT ISNULL(SO.RX_IDENTIFIER,-1),DIM.RX_PRES_NO,DIM.REFILL_NO
    FROM @DIMSource DIM
    LEFT JOIN @FACT SO
    	ON DIM.RX_PRES_NO = SO.RX_PRES_NO
    	  AND DIM.REFILL_NO = SO.REFILL_NO
    LEFT  JOIN @FactSource FA
    	ON SO.RX_IDENTIFIER = FA.RX_IDENTIFIER
    WHERE SO.RX_PRES_NO IS NULL
    	AND SO.REFILL_NO IS NULL
    	AND FA.RX_IDENTIFIER IS NULL
    	AND SO.RX_IDENTIFIER IS NULL
    
    select * from @FACT



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    Tuesday, November 19, 2013 9:10 PM
  • Hi vinay,

    I dont have column Refill_No in #Fact table.I only have Rx_Identifier,Rx_Presc_No in #Fact.

    And i only need to insert Rx_Identifier and Rx_Presc_no into #fact not #Refill_no.

    Sorry, my example is showing that my #fact table also has Refill_no.It would be simple if i had REfill_no in #fact table. But we dont have it in #Fact.

    Suggestions?

    Tuesday, November 19, 2013 9:56 PM
  • Without the Rx_Presc_No in Fact table, I see a problem in your design.There does not seem to be a way to identify inserted rows in the fact table from previous runs.

    I suggest you change the design.

    Either you need to have Rx_Identifier in DimSource Or Refill_no in Fact Table.These can be used to check if a row is already inserted.

    Note:i am not sure what happened while replying to the post.previous answer got proposed as answer by mistake .you may unpropose it



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Tuesday, November 19, 2013 10:36 PM
    Tuesday, November 19, 2013 10:32 PM