Stored Procedure unmatch records

  • Hi... need your expertise with this.

    I have a stored procedure below where I match the shipment ID from pacrim and ExpHybrid table. The below stored procedure work but it doesn't display those data from pacrim with no match shipment id in ExpHybrid. I also need to get those information

    CREATE PROCEDURE prcVN_TPC (@wkEnding datetime, @OneMoAgo datetime)

     AS

    select  pacrim.[CHG_ctry],pacrim.[converted_amount] as converted, pacrim.[usd_amount] as USD,

     pacrim.[Shipment_ID] as ship_id,ExpHybrid.[exp date],

     ExpHybrid.[exp date],ExpHybrid.[exp port] as exp_port

    FROM pacrim Left JOIN ExpHybrid on pacrim.[shipment_id] = ExpHybrid.[SHIPMENT ID]

    where pacrim.[shipment_type] = 'TPC' and pacrim.[tdate] = @wkEnding and  pacrim.[CHG_ctry] = 'VN' and  ExpHybrid.[EXP CTRY] = 'VN' and ExpHybrid.[EXP DATE]> @OneMoAgo

    GO

    What i want to do is insert those unmatched record from pacrim into another table. Would it be possible to put it in the same procedure? something like the code below? Or is there any other way?

     if pacrim.[shipment_id] <> ExpHybrid.[SHIPMENT ID] then insert into(ctry_code,expamt,LCamt,usdamt,wkending,Trans_code,Shipment_ID) 

       Values(  pacrim.[CHG_ctry],pacrim.[converted_amount],pacrim.[converted_amount], pacrim.[usd_amount] pacrim.[tdate], pacrim.[shipment_type], pacrim.[Shipment_ID]  );

     end if

     

    Thank you in advance.

  • Even though you have a left join in your original select statement, your where clause requires a successful (inner) join to match the criteria based on ExpHybrid.

    If you want all records from pacrim that fulfil the pacrim field conditions regardless of ExpHybrid, and those that fulfil both conditionson pacrim and ExpHybrid, give the following code a go:

    CREATE PROCEDURE prcVN_TPC (@wkEnding datetime, @OneMoAgo datetime)

    AS

    select pacrim.[CHG_ctry],pacrim.[converted_amount] as converted, pacrim.[usd_amount] as USD,

    pacrim.[Shipment_ID] as ship_id,ExpHybrid.[exp date],

    ExpHybrid.[exp date],ExpHybrid.[exp port] as exp_port

    FROM pacrim Left JOIN ExpHybrid

    on pacrim.[shipment_id] = ExpHybrid.[SHIPMENT ID]

    where pacrim.[shipment_type] = 'TPC'

    and pacrim.[tdate] = @wkEnding

    and pacrim.[CHG_ctry] = 'VN' and

    ( ExpHybrid.[EXP CTRY] is null

    or ( ExpHybrid.[EXP CTRY] = 'VN' and ExpHybrid.[EXP DATE]> @OneMoAgo )

    )

    GO

    Robert

  • Hi Robert.... i have the desired result now. Thank you so much. Great help! 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply