Hi I hope I am posting this to the right thread so bear with me and If I am not please direct me to the correct area as I am new here.

I am trying to create a pipe delimited format on this query and pad my fields with the correct number of spaces and remove any hypens from my zip. I thought that I had this right. We are using Informix SQL. Can you please give it a look for me and tell me if you see anything that should be changed? Your help is deeply appreciated!

create temp table tempprod(Primary_Prod_ID char(15),Product_ID_Type char(4),Secondary_Product_ID char(15),
Mfg_Prod_cd_num char (6),Brand_Name char(40),Generic_Name char(40),Strength char(20),
Pkg_Size char(10),Shelf_Inner_pkg_size char(10),Case_Size char(10),Dosage_Form char(20),Pkg_Desc char(15),
Additional_Pkg_Desc char(30),DEA_Class_Code char(1),Unit_Dose_Container char(1),Status char(2),
Disposition_Character char(8),Pkg_Wt char(10),tare_wt char (10),ARCOS_RPT char(1),Always_Count char(1),
Division_Code char (8))with no log;

insert into tempprod
rpad(item.upc Primary_Prod_ID,(15),"")||
rpad("NDC" Product_ID_Type,(4)," ")||
rpad(item.item_num Secondary_Product_ID,(15),"")||
rpad(" " mfg_Prod_cd_num,(6),"")||
rpad(item.desc_1 Brand_Name,(40),"")||
rpad("" Generic_Name,(40),"")||
rpad(item.desc_1 Strength,(20),""||
rpad(item.size_desc Count_pkg_Size,(10),"")||
rpad("1" Shelf_Inner_Pkg_size,(10),"")||
when uom1 in ("CASE","CA","CS") then 1
when uom2 in ("CASE","CA","CS") then qty1in2
when uom3 in ("CASE","CA","CS") then qty1in2*qty2in3
when uom4 in ("CASE","CA","CS") then qty1in2*qty2in3*qty3in4
end Case_QTY,(10),"")||
rpad(item.pack_desc Doseage_Form,(20),"")||
rpad(prod65wm_f.uom1 Pkg_description,(15),"")||
rpad("" Additional_Pkg_Description,(30),"")||
rpad(Case When Sched1="Y" then "1"
when sched2="Y" then "2"
when sched3="Y" then "2"
when sched4="Y" then "3"
when sched5="Y" then "3"
when sched6="Y" then "4"
when sched7="Y" then "5" end DEA_Class_Code,(1),"")||
rpad("" unit_dose_container,(1),"")||
rpad("AC" Status,(2),"")||
rpad(" " ITEM_I.DESC_2 WasteStream,(8)," ")||
rpad(prod65wm_f.wgt1* 453.59237)Pkg_Wt,(10),"")||
rpad("" ARCOS_Reportable,(1),"")||
rpad("" Always_Count,(1),"")||
rpad(item.org_code Division Code,(8), "")||

from uom,Item left outer join spscommon:item_area
on item.item_num=spscommon:item_area.item,prod65wm_f,item_2
where item.org_code in (select org_code from inv where trans_date>=today-365)
and item.org_code in ("AM","EK","JD","SP","SX")
and item.stock_uom_id=uom.uom_id
and item.item_num=prod65wm_f.sku
and item.item_num=item_2.item_num
and prod65wm_f.part_status="RDY"