PL/SQL :: Create Table From A Column
Jan 28, 2013
Today I’m trying to make a “table” from a column. The information I’m using looks like:
AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ - K
I need my table have these columns:
Column1 Column2 Column3
AAA B CCC
DDD E FFF
GGG H III
JJJ K
So I began to divide the string into smaller pieces:
SELECT LEVEL reg,
REGEXP_SUBSTR('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K','[^|]+', 1, LEVEL) ferroc
FROM DUAL
CONNECT BY LEVEL <= LENGTH('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K') - LENGTH(REPLACE('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ–K','|')) + 1; What I get is:
>
REG ferroc
1 AAA-B-CCC
2 DDD-E-FFF
3 GGG-H-III
4 JJJ - K
>
After this step I’m lost, I try to do almost the same thing than in the first query but mi information get mixed.
View 4 Replies
ADVERTISEMENT
Feb 15, 2012
I want to create a column which will be having unique values but while selecting data from table i am not willing to display same column in output.
SQL> create table temp
2 (
3 c1 number,
4 c2 number,
5 c3 number
6 );
Table created.
SQL> insert into temp values (1,2,3);
1 row created.
SQL> select * from temp;
C1 C2 C3
---------- ---------- ----------
1 2
I want C3 as a hidden column and while selecting like
select * from temp;
expected output is...
C1 C2
---------- ----------
1 2
My main target is, C3 column should not display in select * ... stm but column with data should be there in table.
View 7 Replies
View Related
Nov 15, 2012
Is it possible to create a table without any single column in it?
View 1 Replies
View Related
Nov 3, 2011
create trigger on certain column for table structure.
SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....
I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .
Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)
Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column
View 3 Replies
View Related
Jan 11, 2011
It shows me error when i create a table with column name as UID.
Is UID a inbuilt function or anything else?
View 2 Replies
View Related
Oct 31, 2006
I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.
View 2 Replies
View Related
May 25, 2013
I am trying to write a script to create a table with the author column default value as "Administrator" I use this execute immediate statement1;
Why does it create the table successfully with ',AUTHOR VARCHAR2(30) DEFAULT 1' and NOT with ',AUTHOR VARCHAR2(30) DEFAULT Administrator' ?
I want to set the COLUMN DEFAULT have to "Administrator" when I create the table.
When I try character I get this error "ORA-00984: column not allowed here"
statement1:='CREATE table TEST_ID2'
||'(TEST_ID NUMBER(6)'
||',test_name VARCHAR2(40)'
||notnull||
',date_created DATE DEFAULT SYSDATE '
[code]....
View 13 Replies
View Related
Nov 3, 2010
the reason behind the below statements:
1) We cant create TABLE PARTITIONED on CLUSTER or INDEX on CLUSTER TABLE.
2) We cant create a partitioned table with the column of LONG or LONGRAW? (But how it could be possible with BLOB, CLOB?
View 3 Replies
View Related
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
Apr 29, 2011
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
View 3 Replies
View Related
Dec 15, 2010
I would like to create an SQL with some of these information.
2 columns -> Month_start, Month_end
Sample data:
1. 4/2010, 4/2010
2. 4/2010, 5/2010
3. 6/2010, 6/2010
4. 6/2010, 7/2010
5. 9/2010, 9/2010
6. 9/2010, 10/2010
....... ......
I would like to create another column called combined_month. The logics are the following:
1. If the Month_start = Month_end, get information from Month_start only. Query will return 1 line.
2. If the Month_start is different from Month_end, get information from Month_start and also, get information from Month_End as well. Query will return 2 lines.
The result will look like the following:
Combined_Month
4/2010 from (record # 1)
4/2010 -> (record # 2)
5/2010 -> (record # 2)
6/2010 -> (record # 3)
6/2010 -> (record # 4)
7/2010 -> (record # 4)
9/2010 -> (record # 5)
9/2010 -> (record # 6)
10/2010 -> (record # 6)
This 6 records will be regenerated into 9 records.
View 8 Replies
View Related
May 15, 2011
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
View 3 Replies
View Related
May 11, 2012
I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
View 7 Replies
View Related
Jun 26, 2012
CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));
Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.
I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12
View 10 Replies
View Related
Jul 26, 2010
I've set up a query that creates 2 columns 'UVLCredit' and 'UVLDebit' and what I'm trying to do is subtract the 'UVLDebit' column from the 'UVLCredit' column and have the resulting value show up in a column called 'UVLTotal.
I'm multiplying 1 column times another to create a new column called UVLCredit, and then doing it again (with a different column) to create a 2nd new column called UVLDebit...the last thing I'm trying to do is to take the results of the 1st new column and subtract the results of the 2nd newly created column to create a 3rd new column called UVLTotal. The error states that the 'UVLDebit' column is an invalid identifier.
See code below....
CODESELECT
T.PO_RELEASE_NBR, T.PO_LINE_STATUS, T.FACILITY,
TI.STATUS_DATE, TI.QTY_ORDERED_UP,
TI.PO_UNIT_PRICE, TI.QTY_REC_TOTAL_UP, TI.QTY_INVOICED_UP,
[b]sum(TI.PO_UNIT_PRICE) * (TI.QTY_REC_TOTAL_UP) as "UVLCredit",
sum(TI.PO_UNIT_PRICE) * (TI.QTY_INVOICED_UP) as "UVLDebit",
[code]........
View 3 Replies
View Related
Dec 24, 2010
I use Database 11g .
I have 2 BLOB columns include pdf files.
How can I create one BLOB column include one one pdf file? merge 2 pdf files into one file.
View 4 Replies
View Related
Mar 17, 2013
can we create rank on a particular column without using rownum and rank function.
View 9 Replies
View Related
Sep 6, 2013
MONTHSTAT_NAMEPERCENTAGE_OF_TOTALApr-08USER_TIME35.43Apr-08SYS_TIME3.74Apr-08BUSY_TIME38.33Sep-13USER_TIME42.92Sep-13BUSY_TIME45.54Apr-08IOWAIT_TIME20.12Aug-13SYS_TIME2.43Aug-13IOWAIT_TIME0.04Aug-13BUSY_TIME45.05Aug-13USER_TIME42.59Sep-13SYS_TIME2.52Sep-13IOWAIT_TIME0.04select null, month, stat_name, Percentage_of_Total from oshistory
I want to create 3d column chart for above chart.
View 3 Replies
View Related
Mar 22, 2013
I am describing a SQL statement to get it's column list:DECLARE
cur NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;
[Code]....
Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?
View 4 Replies
View Related
Jul 6, 2010
user@tank> create table filetable(id number(6), file_content BLOB);
0 rows affected (0.02 seconds)
vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL
I am new to Database field.
- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.
View 1 Replies
View Related
Jul 30, 2012
My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.
Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:
It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01
So i started out my creating like this:
select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union
[Code]....
.and so on till i get 2010-01.
there has to be a better way to do this.
View 7 Replies
View Related
Feb 26, 2013
I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.
My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.
Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.
It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?
I mean something like this:
alter table MESSAGE
add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?
If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?
View 4 Replies
View Related
Aug 30, 2011
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
View 2 Replies
View Related
Aug 13, 2012
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
View 3 Replies
View Related
Jul 14, 2012
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies
View Related
Feb 9, 2011
I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.
View 2 Replies
View Related
Feb 23, 2013
I Have an apex page that display a modal window utilizing jquery. In the modal window I have a classic report with a link column that I want to capture its click event.
I was thinking I could create a dynamic action with selection type=jquery selector. Not for sure if I need to do anything on link column and do not know the syntax jquery selector.
View 5 Replies
View Related
Sep 2, 2010
I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
View 5 Replies
View Related
Jan 25, 2013
can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
View 2 Replies
View Related
Sep 8, 2008
I have to create a table which contain history of a main table. like this:
if the main table is
========================
nametypelengthnot null
Avarchar5Y
Bvarchar5N
Cvarchar5N
Dvarchar5N
========================
[code]....
I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :
if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......
I decided to select column name from the data dictonary using this SQL:
SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';
and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):
BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;
but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.
View 5 Replies
View Related