I have a fairly complex query which uses data built dynamically, please please pay no mind to not necessarily seeing all of the data sources, as I think my question is more basic. I have been running the query successfully in Toad and using Toad’s “export dataset” to send it as tab delimited to a file. Obviously I’m now inserting TAB’s myself in attempt to convert it to sql plus, as I’d like to use the spool feature and save myself some steps. Using sql plus, however, I’m now gettng an error message that indicates:
Error at line 265:
ORA-01722: invalid number …and it’s pointing to…
CHR(09) || --“Time Fence (in months)”, --NULL “Time Fence (in months)”
I think it has something to do with the case statement, but I’m not sure. Can you anyone help? Here is the full select statement:
SELECT /*+ PARALLEL(v, 4) */
v.segment1 || – item
CHR(09) || – “Company”, --NULL “Company”
CHR(09) || – “Division”, --NULL “Division”
ds.group_name || – “Corporation”,
CHR(09) || – “Sold To”, --NULL “Sold To”
CHR(09) || – “Department”, --NULL “Department”
v.organization_code || – “Ship To”
–Eliminate the following ASCII characters -> CHR(9): Horizontal Tab, CHR(13): Cariage Return, CHR(10): Line Feed
SUBSTR(replace(replace( replace(ltrim(rtrim(NVL(v.long_description,v.description))), CHR(9),’’), CHR(13),’’),CHR(10),’’) ,1, 25) || – “Description”
SUBSTR(replace(replace( replace(ltrim(rtrim(NVL(v.description,v.long_description))), CHR(9),’’), CHR(13),’’),CHR(10),’’) ,1, 30) || – “Extended Description”
ds.planner_name || – “Planner”
‘IL’ || – “Vendor”
v.class || – “Class”, v.class2 “Class 2”
replace(replace( replace(ltrim(rtrim(v.class3)), CHR(9),’’), CHR(13),’’),CHR(10),’’) || – “Class 3”
v.class4 || – “Class 4”
ds.group_desc || – “Available to User 1”
replace(replace( replace(ltrim(rtrim(v.Availabletouser2)), CHR(9),’’), CHR(13),’’),CHR(10),’’) || – “Available to User 2”
CHR(09) || – “Available to User 3”, --NULL “Available to User 3”
CHR(09) || – “Order Unit”, --NULL “Order Unit”
0 || – “Future Cost”, --v.item_cost “Future Cost” --Current Oracle Cost for now set to 0
v.avg_price || – “Future Price”, --Current Oracle Price
CHR(09) || – “Current Cost”, --NULL “Current Cost”
CHR(09) || – “Current Price”, --NULL “Current Price”
Equivalent_Unit_1 || – “Equivalent Unit 1”
‘Case’ || – “Equivalent Unit 1 Title”
Equivalent_Unit_2 || – “Equivalent Unit 2”
‘Pallet’ || – “Equivalent Unit 2 Title”,
CHR(09) || – “Equivalent Unit 3”, --NULL “Equivalent Unit 3”
CHR(09) || – “Equivalent Unit 3 Title”, --NULL “Equivalent Unit 3 Title”
CHR(09) || – “ABC 1”, --NULL “ABC 1”
CHR(09) || – “ABC 2”, --NULL “ABC 2”
CHR(09) || – “ABC 3”, --NULL “ABC 3”
v.ABC4 || – “ABC 4”
v.ABC5 || – “ABC 5”
v.ABC6 || – “ABC 6”
v.ABC7 || – “ABC 7”,
CHR(09) || – “ABC 8”, --NULL “ABC 8”
CHR(09) || – “Alt Vendor 1”, --NULL “Alt Vendor 1”
CHR(09) || – “Alt Vendor 2”, --NULL “Alt Vendor 2”
to_char(v.creation_Date, ‘YYYYMMDD’) || – “Birth Date” --of the item
CASE WHEN to_number(v.cumulative_total_lead_time) IS NULL THEN
(SELECT to_number(DS_VALUE) from SODA_DS_CTRL_V where DS_TYPE = ‘CTRL_PARM’ AND DS_NAME = ‘LEAD_TIME’)
ELSE to_number(v.cumulative_total_lead_time)
END || – “Lead Time (in days)”
CHR(09) || – “Time Fence (in months)”, --NULL “Time Fence (in months)”
NVL(soi.shipped_orders_count,0) + NVL(ooi.open_orders_count, 0) || – “Month To Date Shipments+Orders”
CHR(09) || – “OnHand Inventory” --NULL “OnHand Inventory”
v.cross_Reference || – “UPC Code”
lPAD(SUBSTR(v.cube, 1, 10) ,10,’ ‘) || – “Cube”
lPAD(SUBSTR(v.weight, 1, 10) ,10,’ ') DATTRIBUTES --“Weight”
FROM items_v v
–Making sure all the Customer groups are looped in whether there exists a combination or not.
CROSS JOIN ( select distinct group_name, group_desc , planner_name
from SODA_CUST_PLAN_GRP_V) ds
LEFT JOIN shipped_orders_info_v soi
ON soi.inventory_item_id = v.inventory_item_id
AND soi.organization_id = v.organization_id
AND soi.group_name = ds.group_name
LEFT JOIN open_orders_info_v ooi
ON ooi.inventory_item_id = v.inventory_item_id
AND ooi.ship_from_org_id = v.organization_id
AND ooi.group_name = ds.group_name