Solved

Convert values from a table to other table

Posted on 2013-06-11
11
356 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 76

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
 

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 76

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
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: 39239096
Thank you everybody!

Now, I can to display correctly the data.

Regards
0
 
LVL 76

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 76

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.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now