# error being generated using sql plus

**URL:** <https://forums.toadworld.com/t/error-being-generated-using-sql-plus/30659>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [August 11, 2014, 2:24pm UTC](https://forums.toadworld.com/t/error-being-generated-using-sql-plus/30659 "2014-08-11T14:24:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rztour](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@rztour](https://forums.toadworld.com/u/rztour)\
**Post date:** [August 11, 2014, 2:24pm UTC](https://forums.toadworld.com/t/error-being-generated-using-sql-plus/30659/1 "2014-08-11T14:24:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [August 13, 2014, 11:01am UTC](https://forums.toadworld.com/t/error-being-generated-using-sql-plus/30659/2 "2014-08-13T11:01:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rztour](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@rztour](https://forums.toadworld.com/u/rztour)\
**Post date:** [August 13, 2014, 11:42am UTC](https://forums.toadworld.com/t/error-being-generated-using-sql-plus/30659/3 "2014-08-13T11:42:58Z")

</div>

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!!
