UPDATE of Type DEC with Error
Ralf Hackmann got syntax error 201 on UPDATE with BEWPRS=73,62 on a DEC(15,2) column, although the string '73,62' worked. Ian Goddard suggested 73.62. Jonathan Leffler explained SQL literals must use a decimal point, because commas separate list items, while quoted strings are converted using locale (DBMONEY). Ralf's DBMONEY question went unanswered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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
> 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
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>