Toad World® Forums

error being generated using sql plus

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

is that query running in pure Oracle SQL*Plus from the same Oracle client as Toad is connected through?

Damir: Thank you very much for your reply. I have resolved the issue. As long as I stared at it, it was not coming to me, so I slowly and methodically eliminated a bunch of columns from the select and then just started adding them back in. I’m embarassed to say that this statement, “NVL(soi.shipped_orders_count,0) + NVL(ooi.open_orders_count, 0)” was the cause of the issue and it was because of the piping around that calculated field. The simple solution was to simply add brackets to allow the addition to take place separately without interference from other values piped before and after. I only came upon this solution a little while after your post. Again…I very much appreciate your follow up!!