Easy case statement that I cant do

[NoncentzTheGreat]

Hello All, This is me first Post ..Yesssss

I am new to sql so I hope I explain this correctly:

I used a PATINDEX statement to create a field called UPS_Shipper in my view and a field called UPSCodeShipTo which is renamed from a field I pulled earlier.

What I have been tasked to do it to create a statement that will do the following:
If there is a value in UPS_CodeShipTo (which there always is) then I need to use that value in a new column (UPS_FINAL) UNLESS there is a value in the UPS_Shipper column in which the value would go to the new column and would REPLACE the value set by UPS_CodeShipTo in the new column. Im new to SQL but this is what i have gathered....

I need to use a CASE statement but I have little experience with this, here is the view I am currently working with...

Any help in the right direction would be much appreciated
Antony
Systems Administrator
--------------------------------------------------------------------------------------------
SELECT P21PLAY.dbo.p21 _view_oe_pick_t icket.pick_tick et_no, P21PLAY.dbo.p21 _view_oe_hdr.or der_no, P21PLAY.dbo.p21 _view_oe_hdr.cu stomer_id,
P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_name, P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_add1, P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_add2,
P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_city, P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_state, P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_zip,
P21PLAY.dbo.p21 _view_oe_hdr.po _no, P21PLAY.dbo.p21 _view_oe_pick_t icket.carrier_i d AS Carrier, P21PLAY.dbo.p21 _view_oe_pick_t icket.carrier_i d AS Supplier,
P21PLAY.dbo.p21 _view_oe_hdr.co d_flag, P21PLAY.dbo.p21 _view_oe_hdr.te rms, P21PLAY.dbo.p21 _view_oe_hdr.sh ip2_country,
P21PLAY.dbo.p21 _view_oe_hdr.sh ip_to_phone, P21PLAY.dbo.p21 _view_oe_hdr.de livery_instruct ions,


SUBSTRING(P21PL AY.dbo.p21_view _oe_hdr.deliver y_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21 _view_oe_hdr.de livery_instruct ions) + 1, 6) AS UPS_Shipper,

(CASE WHEN charindex('@', [email_address]) > 0 THEN [email_address] ELSE 'email@domain.c om' END) AS alternate_addre ss, 'Y' AS QVN,
'email@domain.c om' AS failureaddress,

P21PLAY.dbo.p21 _view_contacts. email_address, dbo.[Address Table].ups_code AS UPS_CodeShipTo

FROM P21PLAY.dbo.p21 _view_contacts INNER JOIN
dbo.[Address Table] ON P21PLAY.dbo.p21 _view_contacts. address_id = dbo.[Address Table].id RIGHT OUTER JOIN
P21PLAY.dbo.p21 _view_oe_pick_t icket INNER JOIN
P21PLAY.dbo.p21 _view_oe_hdr ON P21PLAY.dbo.p21 _view_oe_pick_t icket.order_no = P21PLAY.dbo.p21 _view_oe_hdr.or der_no ON
P21PLAY.dbo.p21 _view_contacts. id = P21PLAY.dbo.p21 _view_oe_hdr.co ntact_id
WHERE (P21PLAY.dbo.p2 1_view_oe_pick_ ticket.ship_dat e IS NULL)