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.
I 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?
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.
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]........
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"
i 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
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?
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 ===============================================================================================
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.
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 ,
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,
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 )