Hi ,
instr(pbk.impexcel_eannr.IMP_PRIS,',') returns a zero when ',' does not exist in the field. And that gives wrong result. You do not need to do anything if there is no ','. So you want set PRIS to :
decode(instr(pbk.impexcel_eannr.IMP_PRIS,','),0,pbk.impexcel_eannr.IMP_PRIS, substr(pbk.impexcel_eannr.IMP_PRIS,1,(instr(pbk.impexcel_eannr.IMP_PRIS,',')-1))||'.'|| substr(pbk.impexcel_eannr.IMP_PRIS,(instr(pbk.impexcel_eannr.IMP_PRIS,',')+1)))))
length(pbk.impexcel_eannr.IMP_PRIS is not needed since by default it goes to the end of field.
btw I assume the , are . in the second output.
[EMAIL PROTECTED] wrote:
Hallo,
I have a table, A: IMPEXCEL_EANNR
which contains the field IMP_PRIS(= price)
It looks like this:
12
1,50
11
This is a varchar2 field in this table. I want that field values to be inserted(or rather update the other table ) in other table called VARUKORGEANREL, but field in which the prices are goingto be inserted to is a NUMBER(15,5) field.
I am trying to use this sql but the result I get is:
0,12
1,50
0,11
The right prices to be inserted would be
12
1,50
11
What is wrong with this sql?
UPDATE PBK.VARUKORGEANREL SET (
PRIS) =
(SELECT Distinct to_number ( substr(pbk.impexcel_eannr.IMP_PRIS,1,(instr(pbk.impexcel_eannr.IMP_PRIS,',')-1))||'.'|| substr(pbk.impexcel_eannr.IMP_PRIS,(instr(pbk.impexcel_eannr.IMP_PRIS,',')+1),length(pbk.impexcel_eannr.IMP_PRIS)))
FROM PBK.impexcel_eannr
WHERE PBK.VArukorgeanrel.ean = PBK.impexcel_eannr.EANNUM)
WHERE PBK.varukorgeanrel.varukorgid = inVarukorgId;
Thanks in advance
Roland
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
