Datatype conversion

[roland . skoldblom]

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).

Re: Datatype conversion

[A Joshi]

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 youwant 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_EANNRwhich contains the field IMP_PRIS(= price)It looks like this:121,5011This 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,121,500,11The right prices to be inserted would be121,5011What 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_eannrWHERE PBK.VArukorgeanrel.ean = PBK.impexcel_eannr.EANNUM)WHERE PBK.varukorgeanrel.varukorgid = inVarukorgId;Thanks in advanceRoland-- 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.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard