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

SQL & PL/SQL :: How To Create Hidden Column In Table

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

Create A Table Without Any Single Column In It?

Nov 15, 2012

Is it possible to create a table without any single column in it?

View 1 Replies View Related

SQL & PL/SQL :: Create Trigger On Certain Column For Table Structure

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

SQL & PL/SQL :: Create A Table With Column Name As UID But UID Doesn't Work

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

Create A Separate Sort Order Column On Table

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

SQL & PL/SQL :: Create Table With Author Column Default Value As Administrator

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

Performance Tuning :: Create Partitioned Table With Column Of LONG Or LONGRAW?

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

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

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

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

SQL & PL/SQL :: Create Another Column - Combined_Month

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

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

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

Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?

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

SQL & PL/SQL :: Create New Column Based On MIN DATE

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

Error Message When Attempting To Create 3rd Column

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

SQL & PL/SQL :: Create One BLOB Column Including One PDF File?

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

SQL & PL/SQL :: Create Rank On Column Without Using Rownum Function

Mar 17, 2013

can we create rank on a particular column without using rownum and rank function.

View 9 Replies View Related

Application Express :: How To Create 3D Column Chart

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

PL/SQL :: How To Create One-column Collection From Another Multi-column Collection

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

Cannot Create BLOB Data Type For A Column In Tavle

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

PL/SQL :: Create Reporting View Based On Date Column

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

PL/SQL :: How To Create Special Column Which Represents Result Of A Query

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

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

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

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

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

SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?

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

SQL & PL/SQL :: Create Materialized View With Clob Column Based On Varchar2?

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

Application Express :: How To Create A Dynamic Action From Link Column In Classic Report

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

Create Temp Table From Existing Table

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

SQL & PL/SQL :: Create Table By Using Another Table With Specific Condition?

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

Create A Table Which Contain History Of Main Table

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







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