Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2417
  • Last Modified:

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

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
Enyimba
Asked:
Enyimba
  • 9
  • 4
  • 2
2 Solutions
 
EnyimbaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tliottaCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
EnyimbaAuthor Commented:
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
 
EnyimbaAuthor Commented:
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
 
EnyimbaAuthor Commented:
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
 
tliottaCommented:
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
 
EnyimbaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
EnyimbaAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
EnyimbaAuthor Commented:
Thank you Guy...appreciate it

Enyimba
0
 
EnyimbaAuthor Commented:
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
 
EnyimbaAuthor Commented:
Thanks guys
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now