Solved

How can I solve this SQL error: A numeric argument of a built-in string function is out of range.. SQLCODE=-138, SQLSTATE=22011

Posted on 2015-01-18
15
1,182 Views
Last Modified: 2015-02-17
Hi all,
I have tested the SQL code below and it is throwin an error as shown below. Below that is the data that I loaded into the test_table  and the column name I am interogating is named TEXT.

select      insert(TEXT, LOCATE('.', TEXT, POSITION('PAT', TEXT, CODEUNITS32), CODEUNITS32) - 2, 0, '.')
from      DPAT.test_table
where      POSITION('PAT', TEXT, CODEUNITS32) <> 0


Error:
A numeric argument of a built-in string function is out of range.. SQLCODE=-138, SQLSTATE=22011, DRIVER=3.68.6

Can anyone please show me how to resolve the above error?

Thanks

Enyimba



INSERT INTO DB.TEST_TABLE VALUES
                                                                     -------Column Name=TEXT----
(3769910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3769911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3769911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769912,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3769913,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769914,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3769915,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769916,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT1025,UPC85162600203'),
(3769917,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769918,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3769919,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT17156,UPC3151'),
(3769900,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3769901,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769800,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT152736,UPC85162600203'),
(3719911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3729910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3739911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3749910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3759911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3779911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3789910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3799911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3709910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3719911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3729910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3739911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3749910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3759911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3769910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3779911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151'),
(3789910,20141220,2210,20141219,215800,38,300,604,2,'KEY412227,QTY1, PAT173,UPC85162600203'),
(3799911,20141220,2210,20141219,215600,232,300,155,2,'KEY412227,QTY1, PAT52,UPC3151');
0
Comment
Question by:Enyimba
  • 9
  • 4
  • 2
15 Comments
 

Author Comment

by:Enyimba
ID: 40557091
Hi all,

While testing the sql code presented in this thread, I observed that if I change the decimal point  '.' literal in the code to a comma ',', the above sql runs to completion without error. When the comma ' ,' is replaced with a decimal '.' point, the sql gives the  error described below.

Why is that?  Please help me with the reasonaleit the solution. Here is the example when the comma literal is used and the sql completes successfully: Please observe that now there is a comma inserted between WGT and numbers. The decimal position will always remain 0.00 much like currency designation.

Thanks

        TEXT (column)
 ------------------------
 KEY1742888,WGT,99,QTY1
 KEY1742888,WGT3,46,QTY1
 KEY1742888,WGT3,44,QTY1
 KEY1742888,WGT4,24,QTY1
 KEY1742888,WGT,66,QTY1
 KEY1742888,WGT1,00,QTY1
 KEY1742888,WGT1,00,QTY1
 KEY1742888,WGT1,00,QTY1
 KEY1742888,WGT,81,QTY1
 KEY1742888,WG,T1,QTY2
 KEY1742888,WGT,94,QTY1
 KEY1742889,WG,T3,QTY1
 KEY1742889,WGT1,10,QTY1
 KEY1742889,WGT1,04,QTY1
 KEY1742889,WGT1,01,QTY1
 KEY1742889,WGT2,29,QTY1
 KEY1742889,WGT2,32,QTY1
 KEY1742889,WGT2,28,QTY1
 KEY1742889,WGT,80,QTY1
 KEY1742889,WGT,70,QTY1
 KEY1742889,WGT3,50,QTY1
 KEY1742889,WGT2,80,QTY1
 KEY1742889,WGT2,90,QTY1
 KEY1742889,WGT1,96,QTY1
 KEY1742889,WGT3,28,QTY1
 KEY1742889,WGT2,35,QTY1
 KEY1742889,WGT2,71,QTY1
 KEY1742889,WGT,39,QTY1
 KEY1742889,WGT,98,QTY1
 KEY1742889,WGT1,41,QTY1
 KEY1742889,WG,T2,QTY1
 KEY1742889,WGT2,06,QTY1
 KEY1742889,WG,T2,QTY1
 KEY1742889,WGT,64,QTY1
 KEY1742889,WGT1,66,QTY1
 KEY1742889,WGT1,53,QTY1
 KEY1742889,WGT1,48,QTY1
 KEY1742889,WGT1,32,QTY1
 KEY1742889,WGT1,33,QTY1
 KEY1742889,WGT1,69,QTY1
 KEY1742889,WGT1,01,QTY1
 KEY1742889,WGT1,79,QTY1
 KEY1742889,WGT,57,QTY1
 KEY1742889,WG,T6,QTY1
 KEY1742889,WGT1,72,QTY1
 KEY1742889,WGT2,59,QTY1
 KEY1742889,WGT1,58,QTY1
 KEY1742889,WGT1,17,QTY1
 KEY1742889,WGT1,54,QTY1
 KEY1742889,WG,T4,QTY1
 KEY1742889,WG,T1,QTY1
 KEY1742889,WGT1,30,QTY1
 KEY1742889,WGT1,63,QTY1
 KEY1742889,WGT,88,QTY1
 KEY1742889,WGT3,16,QTY1
 KEY1742889,WGT1,64,QTY1
 KEY1742889,WGT1,00,QTY1
 KEY1742889,WGT1,00,QTY1
 KEY1742889,WGT,87,QTY1
 KEY1742889,WGT,87,QTY1
 KEY1742889,WGT2,30,QTY1
 KEY1742889,WGT1,14,QTY1
 KEY1742889,WG,T1,QTY2
 KEY1742889,WGT1,00,QTY1
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40557099
you have this part of the code, that, if the "." is not in the text, it will return 0  (or NULL)
LOCATE('.', ... )

you then apply -2 and use that as starting position for INSERT function (I presume that is a type, and you mean INSTR ?!)
in any case, the -2 (or NULL) value is the one giving you the error you got. you must change the code to provide always a valid starting point for the instr function
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40557707
where      POSITION('PAT', TEXT, CODEUNITS32) <> 0

Your WHERE clause selects all rows. But none of the rows in your first set of sample data has a period (".") and you are trying to LOCATE() a period. So, your code tries to modify a position in TEXT that doesn't exist. Every time it tries to change a row that doesn't have a period, it will get an error that says you have an invalid position.

Change your WHERE clause to add a check for a period in TEST. The WHERE clause should look for both 'PAT' and for '.'.

Tom
0
 

Author Comment

by:Enyimba
ID: 40558116
Guy Hengel,

Thanks for your post. Frankly, Could you please amplify your answer.  The column substring that I need to change will not have any decimal point. The decimal point is what I need to insert  between numbers when numbers are present. The column substring that I am interested in always begin with something like this: --------->> 'KEY412227,QTY1, PAT52,UPC3151'), and the substrng of interest begins with PAT. WHEN present (because sometimes, it may not be present) will always begin with 'PATn, PATnn, PATnnn, PATnnnn, PATnnnnn, or PATnnnnnn.When this substring is present, I want to insert/update the number part of the substring with decimal point 0.00. Please see the examples shown in this thread.

How do I do that? Please provide some example if you can.
0
 

Author Comment

by:Enyimba
ID: 40558127
Hi Tom,

No, I am not trying to locate a decimal point but rather, to locate a comma, if exist. There is no decimal to be found in the MEMO/TEXT column, only comma, hence I want to use that as starting point from where I can do a -2 to position my "cursor" for a decimal point insert.
0
 

Author Comment

by:Enyimba
ID: 40558152
Hi all again,
I now have the code doing what I want it to do, I think but I now want to have this same code update the db2 table tha it is reading the source data from. In short, I am hoing to get the code to update the same record/row e.g; PAT123, becomes PAT1.23, just update the field and moveon to another row. I've taken a stab at this but I am getting this error:

update periscope.product_trans_log t0
 set t0.memo = (
select      insert(MEMO, LOCATE(',', MEMO, POSITION('WGT', MEMO, CODEUNITS32), CODEUNITS32) - 2, 0, '.')
from      periscope.product_trans_log  t1
where      POSITION('WGT', MEMO, CODEUNITS32) <> 0 t0.id = t1.id and t1.tran_type = 'SALE')

First, its taking way too long 10 minutes to process 1.1 million records.
Second, I cannot tell while it is executing if the update is occuring.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40562147
No, I am not trying to locate a decimal point but rather, to locate a comma, if exist.

Sorry, your original question has a period in the LOCATE() function. I assumed you wanted a period. It makes more sense for a comma.

If I understand now, your TEXT column contains a "weight" and a "quantity" value. The "weight" value starts with 'WGT' and it's effectively ended with ',QTY'. (There is a comma before 'QTY'.)

You want to replace the comma that appears between those two end-points. Correct? If so, does every row have those values?

Also, you show these rows in your sample data"
 KEY1742889,WG,T4,QTY1
 KEY1742889,WG,T1,QTY1

Open in new window

How should they be handled?

And is the comma that you want to replace always two positions before ',QTY'?

Tom
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Enyimba
ID: 40562755
Hello Tom,

Ok, let me answer your questions this:

If I understand now, your TEXT column contains a "weight" and a "quantity" value. The "weight" value starts with 'WGT' and it's effectively ended with ',QTY'. (There is a comma before 'QTY'.)

Yes
You want to replace the comma that appears between those two end-points. Correct? If so, does every row have those values?


No, I do not want to replace the comma, comma should stay. What needs to be chhanged is the weight (WGTnnnnnn) substring.The total number of characters is 3, and total "posible" number of numerals is up to 6 numbers. For example, if we have "WGT1", it should be changed to "WGT0.01", WGT12 should become WGT0.12, WGT123=WGT1.23,WGT1234=WGT12.34, and so on. The comma remains if it present and if it not present do nothing just as well do nothing if there is no WGT.

Also, you show these rows in your sample data"

 KEY1742889,WG,T4,QTY1
 KEY1742889,WG,T1,QTY1

Those are leftover from bad test done earlier with this same code...

So here we go, hope you come up with something
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40563690
I see now. based on the pattern (  PATnnn,  )  , you find the non-decimal (unitary) value which represents like euro-cents, and you want to make it a decimal representation ( PATn.nn, )
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40563698
I don't have a DB2 db to play with (even not with sqlfiddle page), so I will not be able to help with a definitive answer for your platform.
still I see that in you original code you are looking for a "." in the locate, and it should be looking for the "," to know where the pattern is ending.
the other issue (your main error) is however that if the pattern is PATn, (so only 1 numerical) the logic would make a PA.Tn, from it.
the best I would think of would be to create a function that receives the full pattern value "PATnn,"  ; and which then does the split on "PAT"   "nn"   and ";", and returns the reformatted value "PATn.nn;"    

divide ad conquer
0
 

Author Comment

by:Enyimba
ID: 40600965
To the moderator,

Yes the question has been out in this thread for sometime and I do not have a solution yet.  I believe point are awarded when a soltion is reached?

Correct me if I got this wrong.

Thanks

Enyimba
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40602487
Enyimba,
 
  to ask a "moderator" you would normally need to use the "request attention" button of your question.
 
 however, I can answer your question: without a solution you can delete the question, I have no objection here

A3
zone advisor
0
 

Author Comment

by:Enyimba
ID: 40602698
Thank you Guy...appreciate it

Enyimba
0
 

Author Comment

by:Enyimba
ID: 40609291
I have already awarded points for this thread but this website continue to keep it open...watch me award the points againnd watch EX website keep it open...
0
 

Author Closing Comment

by:Enyimba
ID: 40615647
Thanks guys
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 48
Path of Workbook 3 45
Hide vba in gp 7 49
DB2 - LOG FILES. 4 16
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now