SQL & PL/SQL :: Converting Number Column To Date Column

Dec 25, 2012

I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD

CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........

some sample data

SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.

desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.

alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:

alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related

SQL & PL/SQL :: Multipurpose Varchar2 Column Error When Converting To Date

Aug 16, 2011

i have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')

records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)

now when i try to use to_date function i get the error that

" ORA-01858 a non-numeric character was found where a numeric was expected "

in sql i have added a where clause to only pick rows with flag, but even then it gives the error.

using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.

View 8 Replies View Related

SQL & PL/SQL :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Application Express :: Column Link Where Column Number Changes With Query

Feb 11, 2013

We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report

<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012

[Code]....

The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.

Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)

ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.

So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.

View 0 Replies View Related

SQL & PL/SQL :: Row To Column / Column Data Can Be Varied From 1 To Any Number Of Words

Mar 21, 2013

I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.

eg

id description
78664 Pumps Alarm from CAMS RTU154

In the above example this column has 5 word so i want data in 5 rows like below

78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154

This column data can be varied from 1 to any number of words.

View 11 Replies View Related

PL/SQL :: Bitmap Index On Number Column X Char Column?

Jul 23, 2013

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:  

select ass.column20,  ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'    

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO

View 7 Replies View Related

Converting A Column Into Rows

Jul 14, 2012

I have two tables as follows:

TABLE_1
ID ENTRY_DATE VALUE
------- --------------- ----------
1 1-JUN-12 21
1 2-JUN-12 51
1 3-JUN-12 232
1 4-JUN-12 221
1 5-JUN-12 424
.
.
.
.
1 26-JUN-12 52
1 27-JUN-12 0
1 28-JUN-12 247
1 29-JUN-12 528
1 30-JUN-12 489
2 1-JUN-12
2 2-JUN-12
2 3-JUN-12
2 4-JUN-12
2 5-JUN-12
.
.
.
.
2 26-JUN-12
2 27-JUN-12
2 28-JUN-12
2 29-JUN-12
2 30-JUN-12

TABLE_2

ID DAY_1 DAY_2 DAY_3 DAY_4 DATE_5 .......... DAY_26 DAY_27 DAY_28 DAY_29 DAY_30
----- ---------- --------- --------- ---------- ---------- --------- --------- -------- -------- --------
1 21 51 232 221 424 52 0 247 528 489
2

There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.

View 1 Replies View Related

PL/SQL :: Converting Multiple Column In Single?

Apr 9, 2013

need to create a table with single column by using select statement with multiple columns
For Ex- i have 1 row with 10 columns (may be more than 10) like
'A','B','C','D','E','F','G','H',I','J'
i written sql like
select 'A','B','C','D','E','F','G','H','I','J' from dual

result is - 'A','B','C','D','E','F','G','H','I','J' with 10 columns

Now i need output lik this using SQL
Text
------
'A'
'B'
'C'
'D'

[code]...

sort out this problem.

View 6 Replies View Related

SQL & PL/SQL :: Converting Column Into Row - Command Not Properly Ended

Aug 9, 2010

My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.

select * from tmpl_pop_age_range_col_lvl
UNPIVOT
(
quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c',
COUNT_15_TO_19 as 'd',
COUNT_20_TO_24 as 'e',
COUNT_25_TO_29 as 'f',

[code].....

ERROR at line 4: ORA-00933: SQL command not properly ended

View 8 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I am not able to load complete date along with time in the date column. here is my table desc

DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE

my control file is like this

LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)

the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

View 10 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

Server Utilities :: Loading Date Value Into DATE Column?

Oct 18, 2012

I want to load data from a file using sqlldr.I have a table commissions
(
technician_id char(5)
, tech_name char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt number(10,2)
)

my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56
write a ctl file to load this data.

View 6 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

SQL & PL/SQL :: Why Doesn't Number Add Up When Do SUM On Column

Mar 21, 2011

I think I am having a rounding issue, but not sure. If I add all 4 rows below I get 1530.06. But when I use SUM, I get 1529.87

select PAY_EVENTS_AMOUNT
from PAYROLL_PAY_EVENTS_T1 PPET1
where ppet1.regnum = 130402134
and ppet1.pay_period = 2

PAY_EVENTS_AMOUNT
=================
-1499.19
2998.38
-30.68
61.36
=======
1530.06 TOTAL

========================================================

select sum(PAY_EVENTS_AMOUNT ) PAY_EVENTS_AMOUNT
from PAYROLL_PAY_EVENTS_T1 PPET1
where ppet1.regnum = 130402134
and ppet1.pay_period = 2

PAY_EVENTS_AMOUNT
=================
1529.87 TOTAL

View 12 Replies View Related

SQL & PL/SQL :: Modifying NUMBER (38) Column

Dec 30, 2005

In chapter 17 of the 'Oracle Database 10g, The Complete Reference' it says "You can increase or decrease the number of decimal places in a NUMBER column at any time". When trying to modify a column from NUMBER(38) to NUMBER(38,3) I get the ORA-01440 error. Is there a way around this when you have the maximum number of digits (precision = 38).

View 2 Replies View Related

SQL & PL/SQL :: Order By In Date Column

May 20, 2013

I am trying group by count on Monthly basis, entered_timestamp Column is of DATE datatype.

select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments
where type = 'BOOK' group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC

Above SQL doesn't gives the output in proper order ,

View 3 Replies View Related

PL/SQL :: Update A DATE Column

Mar 14, 2013

There is a need for me to UPDATE a DATE column from one date to another. So as an example,

we have TABLE A and Table A has a column (DATE_TO) with date field value off

DATE_TO
31-DEC-99
12-APR-15
15-MAR-16

What I want to do is update '31-DEC-99' to '31-MAR-13' and for it to only effect '31-DEC-99' (the others are fine).

View 11 Replies View Related

PL/SQL :: How To Set Primary Key For A Date Column

May 28, 2013

i need to set primary key for a column consists of the datatype date.

View 5 Replies View Related

PL/SQL :: How To Select Max Value Of Date Column

Feb 14, 2013

How to select max value of date column which tables are having date coulmn.

View 1 Replies View Related

PL/SQL :: Substr Of Date As Column Name

Aug 27, 2013

Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this select s.xyz Today,s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'from sales_tab s.

View 9 Replies View Related

PL/SQL :: Index On Date Column

Aug 2, 2012

Is the index suggested on the date column.

Here is the nature of the date column in my case:

1. The table populates with 1000's of records every day with date being always incremental (current date).

2. The search criteria from the weba application (ADF) is based on the date, user gives the range.

3. From ADF I am referring to it as sql.Timestamp when building the query.

Does Index suggested on the date column here and if so what type of index ?

View 4 Replies View Related

SQL & PL/SQL :: Date Dimension With Column

Mar 14, 2011

creating a date dimension with columns like

id month_name date
1 Jan/2011 31/1/2011 (last date of the month)
2 feb/2011 28/2/2011!!

View 9 Replies View Related

Forms :: How To Add Date Column

Nov 12, 2011

i want to add date column.i need that if user enter date like'23-jan-80' or '23-01-1980' system accept it and save in database.

View 3 Replies View Related

Range Partitioning With Number Column

Apr 21, 2011

i have a requirement To partition a Table byRange partition with a Number column. but the issue is the range Must be in a Date datatype ,
For example

partition by range (date_key)
( PARTITION DEF VALUES LESS THAN ('01-SEP-10'))

the date_key column has values of date in number format. like "20101014"

View 2 Replies View Related

SQL & PL/SQL :: Extract Only Number Part Of Column?

Mar 25, 2010

In a table, I have a column in a table with the following values:

targetcol

30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0

-
-
-
-
-
-
-
-
-

where WD is working days and CD Calendar days.

I just need to extract number from above.

View 11 Replies View Related

SQL & PL/SQL :: Update Column To Consecutive Number?

Apr 14, 2011

create table test_table(
rn varchar2(10),
col1 varchar2(10),
col2 varchar2(10),

[code]...

I want update col1 whis is null to max(col1) ++ in a row, order by cr_date like
1,1,20110102
2,2,20110101
3,null,20110105 => 3,5,20110105 because this row is after 20110103
4,3,20110104
5,null,20110103 => 5,4,20110103 because this row is before 20110105

update test_table
set col1 = (select max(col1) from test_table) + rownum
where col1 is null;

this gives ora-00933

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved