Solved

Convert values from a table to other table

Posted on 2013-06-11
11
364 Views
Last Modified: 2013-06-12
Hi folks, I have this table:
CREATE TABLE A_HORA_033_TEMP
(
  TAGID      INTEGER,
  TIME       INTEGER,
  VALUE      FLOAT(126),
  STATUS     VARCHAR2(30 CHAR),
  MINTIME    INTEGER,
  MINIMUM    FLOAT(126),
  MINSTATUS  VARCHAR2(30 CHAR),
  MAXTIME    INTEGER,
  MAXIMUM    FLOAT(126),
  MAXSTATUS  VARCHAR2(30 CHAR),
  AVERAGE    FLOAT(126),
  AVGSTATUS  VARCHAR2(30 CHAR)
)

Open in new window

with data:
SET DEFINE OFF;
Insert into A_HORA_033_TEMP
   (TAGID, TIME, VALUE, STATUS, MINTIME, MINIMUM, MINSTATUS, MAXTIME, MAXIMUM, MAXSTATUS, AVERAGE, AVGSTATUS)
 Values
   (6050, 1364504400, 211.995300292969, 'A', 1364506800, 211.760314941406, 'A', 1364505000, 212.016662597656, 'A', 211.897399902344, 'A');
COMMIT;

Open in new window

The columns:
STATUS, MINSTATUS, MAXSTATUS
are VARCHAR2
I need to convert the data in those columns, and then insert the data to other table:
--TARGET TABLE
CREATE TABLE A_HORA_033
(
  UTCTIME      DATE                             NOT NULL,
  POINTNUMBER  INTEGER                          NOT NULL,
  VALOR_INST   FLOAT(126)                       DEFAULT (0.0),
  TLQ_INST     INTEGER                          DEFAULT (32),
  VALOR_PROM   FLOAT(126)                       DEFAULT (0.0),
  TLQ_PROM     INTEGER                          DEFAULT (32),
  VALOR_MAX    FLOAT(126)                       DEFAULT (0.0),
  TLQ_MAX      INTEGER                          DEFAULT (32),
  UTCTIME_MAX  DATE,
  VALOR_MIN    FLOAT(126)                       DEFAULT (0.0),
  TLQ_MIN      INTEGER                          DEFAULT (32),
  UTCTIME_MIN  DATE
)

Open in new window

perhaps like this:
insert into a_hora_033
(select epoch_to_date(a.time), 
       b.pointnumber,
       a.value,
       decode(a.status,'A',2017612633070371840,
                     'O',32,
                     'M',128,
                     '  ', 0,
                     'X', 32) status,
       a.average,
       decode(a.avgstatus,'A',2017612633070371840,
                     'O',32,
                     'M',128,
                     '  ', 0,
                     'X', 32) avgstatus,
       a.maximum,
       decode(a.maxstatus,'A',2017612633070371840,
                     'O',32,
                     'M',128,
                     '  ', 0,
                     'X', 32) maxstatus,
       epoch_to_date(a.maxtime) maxstime,
       a.minimum,
       decode(a.minstatus,'A',2017612633070371840,
                     'O',32,
                     'M',128,
                     '  ', 0,
                     'X', 32) minstatus,
       epoch_to_date(a.mintime)
from a_hora_033_temp a, xpuntos_analog b
where b.pointnumber in (select distinct c.pointnumber 
                        from xpuntos_analog c, a_hora_033_temp d 
                        where c.POINT = d.TAGID)
and a.tagid = b.point);

Open in new window

How you can to see, the columns:
STATUS, MINSTATUS, MAXSTATUS,
now are called:
TLQ_INST, TLQ_PROM, TLQ_MAX, TLQ_MIN
and are INTEGERS.
When i tried the conversion, the values in those columns, looks like this:
UTCTIME|POINTNUMBER|VALOR_INST|TLQ_INST|VALOR_PROM|TLQ_PROM|VALOR_MAX|TLQ_MAX|UTCTIME_MAX|VALOR_MIN|TLQ_MIN|UTCTIME_MIN
28/03/2013 09:00:00 p.m.|330060|211,995300292969|2,01761263307037E18|211,897399902344|2,01761263307037E18|212,016662597656|2,01761263307037E18|28/03/2013 09:10:00 p.m.|211,760314941406|2,01761263307037E18|28/03/2013 09:40:00 p.m.

Open in new window

see the value:
2,01761263307037E18 --> must be 2017612633070371840

Open in new window

Can somebody explain me whats wrong?
Thankyou!
Regards
0
Comment
Question by:carlino70
11 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 125 total points
ID: 39237965
Nothing is wrong.  You are seeing a difference in display.  What tool are you using to select the data?  In SQL*Plus, I know you can set numwidth to get all the digits.  Something like this:

set numwidth 32

That may work in other tools as well.

When all else fails, you can always use TO_CHAR:

to_char(tlq_inst, '99,999,999,999,999,999,999,999,999,999,999')
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39237980
Based on your previous question, I would do the conversion as part of the sql loader step and load the data directly into an INTEGER column instead of a varchar2 column.

In the control file (replace col1 with your columns that are in scientific notation):

 col1 float external "replace(:col1,',','.')"
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 125 total points
ID: 39238025
Nothing wrong:
SQL> set num 19
SQL> select * from a_hora_033
SQL> /

UTCTIME                               POINTNUMBER          VALOR_INST            TLQ_INST          VALOR_PROM            TLQ_PROM
----------------------------- ------------------- ------------------- ------------------- ------------------- -------------------
          VALOR_MAX             TLQ_MAX UTCTIME_MAX                             VALOR_MIN             TLQ_MIN
------------------- ------------------- ----------------------------- ------------------- -------------------
UTCTIME_MIN
-----------------------------
28-Mar-2013 21:00:00                         6050    211.995300292969 2017612633070371840    211.897399902344 2017612633070371840
   212.016662597656 2017612633070371840 28-Mar-2013 21:10:00             211.760314941406 2017612633070371840
28-Mar-2013 21:40:00


SQL>

Open in new window

:p
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:carlino70
ID: 39238718
slightwv,
I tried to do directly the inserts with sqlldr, and now I have the follow errors:
Record 440: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column AVGSTATUS.
ORA-01722: invalid number
Record 1316: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column AVGSTATUS.
ORA-01722: invalid number
Record 1485: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column STATUS.
ORA-01722: invalid number
Record 1486: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column STATUS.
ORA-01722: invalid number
Record 1487: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column STATUS.
ORA-01722: invalid number
Record 1488: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column STATUS.
ORA-01722: invalid number
Record 1489: Rejected - Error on table XAJTDB.A_HORA_033_TEMP, column STATUS.

Open in new window

This ocurred each time the values is 'O' or 'A', than are CHAR in the data source
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39238913
First:  The other Experts appear to be correct on the display issue.  I was thinking you had data in your input file that was in scientific notation and Oracle was treating it as a string.

Looking back at your previous question, it appears to be just a display issue as johnsone and MikeOM_DBA have suggested.


>>This ocurred each time the values is 'O' or 'A', than are CHAR in the data source

It looks like a restriction on External Tables.  If you were using straight sql loader, you should be able to use the same DECODE in the control file.

Per the docs, you cannot do this with External Tables.

I guess I'm just way off on this entire question!  I'll let the other Experts continue assisting so I don't confuse you more.
0
 

Author Comment

by:carlino70
ID: 39239096
Thank you everybody!

Now, I can to display correctly the data.

Regards
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39239105
>>I can to display correctly the data.

If the answers worked for you, why the "B" penalty grade?

What additional information did you need before providing an "A" grade?
0
 

Author Comment

by:carlino70
ID: 39240730
Sorry, I dont knew that the 'B' calification is bad for you.

Really, I dont have the experience with the calification method. I'll try to califica better the next time.

Regards
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39240824
Please take some time to review the Help on the site.

On grading:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-
0
 

Author Comment

by:carlino70
ID: 39240969
OK thanks, I read.

'A grade will multiply the points awarded by 4'

This Is the point,

Can I change the award?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question