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 RepliesI would like to have a column heading as follows in double inverted comma. but sqlplus environment returns column heading length equal to output value.
SQL> select substr('The independence day', 5,12) "Example of substr function" 2 from dual;
Example of s
------------
independence
I know that default column heading length is 30 character long.
but my column heading is less than 30 character long (which is 26)
How can i have column heading as Example of substr function?
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.
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)
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.
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
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.
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"
how can i subtr first name and last name select first_name + lastname from employee for example if i have james michael i what to have JMichael
View 4 Replies View Relatedi would like to know how can i used the OR "|" with REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('500.90 Oracle Parkway, 12-12-2000 78 Redwood Shores, CA','([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})') "REGEXPR_SUBSTR" FROM DUAL;this two condition
([0-9]{1,2}) ([0-9]{1,2}) ([0-9]{4})
or this i did it like this
[code]...
This piece of code returns this below:
RPAD(LPAD(' ',12, ' ') || SUBSTR(adj_second_line,(INSTR(adj_second_line,'~',1,1) + 1),
(INSTR(adj_second_line,'~',1,2) - INSTR(adj_second_line,'~',1,1)
- 1)),80,' ') ADJ_SECOND_LINE
Current Output ( ADJ#1-2M1YE2 TYPE 20100524 20100624 1MO/0DY )
===============================================================================================
1) I would like to alter that code above and substr the back 68 characters only with the 12 spaces padded in front, and the trailing nulls to total 80 characters staying as is. How would I put the below substr into that piece of code above?
Desired Output ( 20100524 20100624 1MO/0DY )
SUBSTR(adj_second_line,LENGTH(adj_second_line)-69)
===============================================================================================
2) I also would like the take whatever else there is preceding the 69th character counting it from the back and calling the column ADJ_FIRST_LINE.
Desired Output2 (ADJ#1-2M1YE2 TYPE)
No leading or trailing nulls and This is not a fixed amount of characters.
===============================================================================================
Result would be:
ADJ_FIRST_LINE
-----------------
ADJ#1-2M1YE2 TYPE
ADJ_SECOND_LINE
---------------
20100524 20100624 1MO/0DY
===============================================================================================
i have following table
CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)
[Code]...
Result is
ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS
I want a query which should return only Sahde # from above data result must be as
7921
C-8762
C-8762
C8676
I have a following table,
CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......
I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z
How do i use Substr and Regular expression here ?
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
I have a table1 with columns serial no and name as below
serial no name
1 john paul
2 john victor
and i have another table1 with columns serialno,firstname and second name as below
serial no firstname secondname
1
2
I want to update my table2 with the name from table1 and divide and insert in first name and second name as below
serialno firstname secondname
1 john paul
2 john victor
i use the query as below
update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)
but the above query not worked.
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
But its proving a bit tricky to nail it.
I am trying to execute the PL/SQL block below:
DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
when I try to run this code
SQL> select
2 regexp_substr('But , soft! What light through yonder window breaks?',
3 'l[[:alpha:]] {4}') AS result
4 from dual;
I get just this ( R
-)
where the result should be like the full sub string which is ( light) not just R.
I am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:
substr(PBBDT,length(PBBDT)-1)
Calling <substr> with <2> parameters, but <3> are expected.
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 ,
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).
i need to set primary key for a column consists of the datatype date.
View 5 Replies View RelatedHow to select max value of date column which tables are having date coulmn.
View 1 Replies View RelatedIs 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 ?
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!!
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 RelatedI am working with Oracle 8i and I have a problem while applying the below query:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
[code].....
The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
Output
ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03
i am fetching one record based on date but not able to fetch the data.
in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.
select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.
I am using this procedure to load oracle data into csv file.
/* Formatted on 2012/05/10 17:18 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE generate_order_csv (
p_dir IN VARCHAR2,
p_file_name IN VARCHAR2,
p_data_file IN VARCHAR2 := NULL
)
[Code]...
-- This part outputs the HEADER
v_fh := UTL_FILE.fopen (UPPER (p_dir), p_file_name, 'w', 32767);
FOR j IN 1 .. col_cnt
LOOP
v_finaltxt :=
LTRIM (v_finaltxt || '|' || LOWER (rec_tab (j).col_name), '|');
[Code]...
.
-- This part outputs the DATA
IF NOT v_samefile
THEN
v_fh := UTL_FILE.fopen (UPPER (p_dir), p_data_file, 'w', 32767);
END IF;
[Code]..
When I execute this procedire I am facing two columns.
1) First column ORDER DATE is not laoding into .csv but when I execute query from SQL, I am able to see the date column values.
2) There is an overlapping of the result string. Last column is coming up into second row.
what changes to be done?