Oracle Invalid Number Error when trying to use "Loop Dataset" in automation, after sorting and taking a subset of the initial query

Toad Data Point
1

Hello, I am relatively new to the world of SQL and Toad. I am trying to use TD Automation to loop over the rows of an initial query to create excel files for each sub-analysis.

For some background: I’m using Toad 3.6, Oracle DB.

My initial query looks at a large database of medical claim data, and sums up all of the bills for a given claim. To make it manageable I am trying to take the top XX most expensive claims:

SELECT * FROM
(SELECT JCN, SUM(TOT_CHARGE_PER_BILL) FROM
(SELECT JCN, TOT_CHARGE_PER_BILL FROM MED_BILLS WHERE TOT_CHARGE_PER_BILL < 50000)
GROUP BY JCN
ORDER BY SUM(TOT_CHARGE_PER_BILL) DESC)
WHERE ROWNUM <= 10

The output from this initial query is named TOP_CLAIMS_SQL in the automation setup. My goal is to look at the details for each claim number(JCN) within my subset.

I then followed the tutorials and used the “Select to File” to write the output. It seemed to mostly work(numbering was not making sense) but when I tried to use a JOIN to get some missing data(names):

SELECT a.JCN,b.FIRST_NAME, b.LAST_NAME FROM MED_BILLS a
JOIN PROVIDERS b on a.IDN=b.IDN
WHERE (a.JCN = :TOP_CLAIMS_SQL.JCN)

it now throws the following error:

File_1 - ORA-01722: invalid number

I’m wondering if perhaps I’m using the WHERE ROWNUM <=XX incorrectly(I’ve also tried changing the exact value for troubleshooting), or if the problem is elsewhere.

If I run this in the SQL editor and manually define the JCN e.g WHERE a.JCN = ‘number’ it works, but not in the Automation Tool.

Here is the entire error log:

12:02:54 PM Thread (23) Export Started [5/4/2017 12:02:54 PM]
12:02:54 PM Thread (23) Export using connection: ####(removed by me)
12:02:54 PM Thread (23) Export using connection: ####
12:02:54 PM Thread (23) Export Wizard: Building object list
12:02:54 PM Thread (23) Exporting Data (1 of 1)
12:02:54 PM Thread (23) Object SQL Query started.
12:02:55 PM Thread (6) InternalReadBackground - start
12:02:57 PM Thread (6) InternalReadBackground - exception Exception occurred in other process
12:02:57 PM Thread (6) InternalReadBackground - stack at Dell.NamedPipeRPC.MessagePumpClient.Invoke(Delegate target, Object[] parameters)
at Quest.Toad.Db.ConnectionProxy.ProxyConnectionCommand.ExecuteReader()
at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()
12:02:57 PM Thread (24) DoneReadTreadWriter
12:02:57 PM Thread (24) DoneReadThreadWriter finally - adapter.RowsRead 0
12:02:57 PM Thread (23) Object SQL Query finished with error: ORA-01722: invalid number
12:02:57 PM Thread (23) at Devart.Data.Oracle.a1.c(Int32 A_0)
at Devart.Data.Oracle.cs.a(Int32 A_0, cc A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Quest.Toad.Db.ConnectionProxy.ProxyConnectionCommand.ActualExecuteReader(Object[] parameters)
12:02:57 PM Thread (23) Export Finished [5/4/2017 12:02:57 PM]

Thanks in advance!

Gabe

2

I can’t see in the automation log file how the bind var was defined. it should be defined as a number. In the editor we determine the type of bind and set this for you when we prompt for value. We do not do this in automation.

You could also use a literal value. To do this you can change to use #bindvar# symbols.

WHERE (a.JCN = #TOP_CLAIMS_SQL.JCN#)

It neither of thesw work, please post full automation script and full automation log file and we will look closer

3

Hi Debbie, Thank you for your reply!

before I saw your response, I used

WHERE(replace(a.JCN,’ ‘,’’) = replace(TOP_CLAIMS_SQL.JCN,’ ‘,’’))

and that seemed to do the trick, so I think you are correct in that I didn’t explicitly define the bind var as a number.

I’ll also try your method as well, and see if I can find more info on setting bind variables.

Thanks again

Gabriel

4

Hi Debbie,

I tried to use your example (WHERE A.JCN = #TOP_CLAIMS_SQL.JCN#) but it gives me the same invalid number error. I looked through the documentation on binding variables, but couldn’t find any examples where those are done in conjunction with “Loop Dataset” and “Select to File”. The closest thing I could find was from another thread on this forum (http://www.toadworld.com/products/toad-data-point/f/36/t/22288).

I tried to modify the example .tas provided in that thread, but couldn’t get it to work. I’ve attached my modfiied .tas file

I think I am not fully understanding Setting or Defining variables.

Again, if I use WHERE (replace(a.JCN,’ ‘,’’) = replace(:TOP_CLAIMS_SQL.JCN,’ ‘,’’) without defining any variables explicitly, it seems to work.
Example_mod.tas (11.6 KB)

5

What is the data type of a.JCN. It would make sense that it is a string. When you take out a comma that seems like a string that is formatted like a number but is not stored in the database as a number. Your solution works fine or you could join by formatted string. IE: Where a.JCN = “4,999”. But if you define the value “4,999” as a number you will get the Oracle error you listed.

6

Hi Debbie,

JCN in the original tables is listed as a VARCHAR2(50), although in practice it is a long number. My “replace” command was stripping away spaces, not commas, but it’s probably a similar effect. I was just initially confused since in the editor this wasn’t a problem.

7

Yes, i would have expected the editor to behave the same as automation. But looks like you are good for now.