?
Solved

Convert values from a table to other table

Posted on 2013-06-11
11
Medium Priority
?
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 500 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 1000 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 500 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

800 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