Stored Procedure unmatch records

Question

Stored Procedure unmatch records

isay

SSC Enthusiast

Points: 192
More actions
March 23, 2006 at 12:38 am

#168789

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.

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

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

rcavill Default port Points: 1482 More actions · Answer 1

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

isay SSC Enthusiast Points: 192 More actions · Answer 2

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