March 23, 2006 at 12:38 am
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.
March 23, 2006 at 1:45 am
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
March 23, 2006 at 2:32 am
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