UPDATE of Type DEC with Error

Posted in 2010

Ralf Hackmann — Wed, 27 Jan 2010 23:50:58 -0800 (PST) — source: Usenet: comp.databases.informix

Hello, if I start the following SQL script with dbaccess I get an error message. UPDATE fyar2sta Set BEWPRS=73,62 Where Sa=0 and Artikel ='00010381-0100 '; 201: A syntax error has occured. Error in line 1Near character position 31 BUT UPDATE fyar2sta Set BEWPRS=73,62 Where Sa=0 and Artikel ='00010381-0100 '; 1 row(s) updated fyar2sta.ddl ..... FIELD AR2_BEWPRS DEC(15,2) CISAM=DEC(15,2) COBOL=COMP3(15,2) # Bewertungspreis .... what ist the reason for that? Greetings Ralf

↪ replying to Ralf Hackmann

Ian Goddard — Thu, 28 Jan 2010 10:06:47 +0000 — source: Usenet: comp.databases.informix

Ralf Hackmann wrote: > Hello, > > if I start the following SQL script with dbaccess I get an error > message. > > UPDATE fyar2sta Set BEWPRS=73,62 Where Sa=0 and Artikel => '00010381-0100 '; > > 201: A syntax error has occured. > Error in line 1> Near character position 31 > > BUT > > UPDATE fyar2sta Set BEWPRS=73,62 Where Sa=0 and Artikel => '00010381-0100 '; > > 1 row(s) updated > > > fyar2sta.ddl > ..... > FIELD AR2_BEWPRS DEC(15,2) CISAM=DEC(15,2) COBOL=COMP3(15,2) > # Bewertungspreis > .... > > what ist the reason for that? > > Greetings > > Ralf > > Try UPDATE fyar2sta Set BEWPRS=73.62 Where Sa=0 and Artikel ='00010381-0100 '; -- Ian The Hotmail address is my spam-bin. Real mail address is iang at austonley org uk

↪ replying to Ian Goddard

Ralf Hackmann — Thu, 28 Jan 2010 02:23:56 -0800 (PST) — source: Usenet: comp.databases.informix

> Try > > UPDATE fyar2sta Set BEWPRS=73.62 Where Sa=0 and Artikel => '00010381-0100 '; > I tried it befor with the same error. If I try UPDATE fyar2sta Set BEWPRS='73,62' Where Sa=0 and Artikel ='00010381-0100 '; it runs ( 1 row(s) updated ) If I look in my applikation (ERP-Sytem) I can see 73,62 in the Field BEWPRS of the Artikle 00010381-0100 Can I be shure that it is DEC(15,2) and not String. > different environments. Have a look on environment variable DBMONEY. I Where is DBMONEY set and where can I change it? Ralf

↪ replying to Ian Goddard

Jonathan Leffler — Fri, 29 Jan 2010 11:43:26 -0800 (PST) — source: Usenet: comp.databases.informix

On Jan 28, 2:06 am, Ian Goddard <godda...@hotmail.co.uk> wrote: > Ralf Hackmann wrote: > > Hello, > > > if I start the following SQL script with dbaccess I get an error > > message. > > > UPDATE fyar2sta Set BEWPRS=73,62 Where Sa=0 and Artikel => > '00010381-0100 '; > > > 201: A syntax error has occured. > > Error in line 1> > Near character position 31 > > > BUT > > > UPDATE fyar2sta Set BEWPRS=73,62 Where Sa=0 and Artikel => > '00010381-0100 '; > > > 1 row(s) updated > > > fyar2sta.ddl > > ..... > > FIELD AR2_BEWPRS DEC(15,2) CISAM=DEC(15,2) COBOL=COMP3(15,2) > > # Bewertungspreis > > .... > > > what ist the reason for that? > > Try > > UPDATE fyar2sta Set BEWPRS=73.62 Where Sa=0 and Artikel => '00010381-0100 '; When you write the number in SQL, you must use the SQL standard notation for literal numbers, which means 73.62 with a full stop (period, dot): SET BEWPRS = 73.62 The comma means something different in SQL - think about it: if you have an IN clause, then: WHERE BEWPRS IN (71.23, 23.12, 41.23) The interpretation of that must be independent of the locale - so the dots mean decimal points. If the commas were also used as decimal points, how would you write the list? All hell breaks loose if you try to juggle that. If you use a string, then the string is converted to a number when the statement is executed, and the conversion pays attention to locale. SET BEWPRS = "73,62" So, this is the source of the trouble. Yours, Jonathan Leffler <jonathan.leffler@gmail.com>

Glossary

dbaccess