Oracle-L: Re: Datatype conversion

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Thu, 06 Nov 2003 09:59:28 -0800
Message-ID: <F001.005D5D94.20031106095928@fatcity.com>

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.

roland.skoldblom_at_ica.se 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: roland.skoldblom_at_ica.se

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: ListGuru_at_fatcity.com (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
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: A Joshi
INET: ajoshi977_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------