SQL & PL/SQL :: Database Tables Column To Row
Feb 13, 2012
I had face the problem regarding to database structure changes..
I had a old database which contain multiple column for 1 empno. which contain multiple subjects and its total marks.
Now the new structure is like all subjects and total marks as a row.
Eg. OLD data Structure
EMPNO SUNBJECT_CODE TOTAL_MARKS
1111 S1 45
1111 S2 21
1111 S3 55
1111 S4 41
NEW DATA STRUCTURE
EMPNO SUB1 MARK1 SUB2 MARK2 SUB3 MARK3 SUB4 MARK4
1111 S1 45 S2 21 S3 55 S4 41
I have more than 1.5 lakhs records in my old data structure..
Is there any Possibilities direct from Sql or i have to do it manually in excel sheet?
View 11 Replies
ADVERTISEMENT
Aug 4, 2011
How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'
View 8 Replies
View Related
Jul 24, 2012
i want to add a column data of datatype number of all the tables at a time in a database..
i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...
View 39 Replies
View Related
Oct 15, 2012
is it possible to update a same column name in two tables.
I have two tables in same schema
(1)table name
pem.igp_parent
column name
igp_no.
igp_type
(2)table name
pem.igp_child
column name
igp_no.
igp_type
i want to update igp_no column in one query, how it would be possible.
View 2 Replies
View Related
May 29, 2011
I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1;
create table t1 (slno number, acc_no number);
drop table t2;
create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3;
create table t3 (acc_no1 number primary key, acc_name1 varchar2(100));
[code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
View 7 Replies
View Related
Mar 29, 2010
I need to identify the dependencies of all the Tables on Packages at column level.
E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T
The resultant query / code should return like this
PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T
Identify the dependencies at column level.
View 5 Replies
View Related
Oct 2, 2012
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies
View Related
Aug 1, 2012
CREATE TABLE ACXIOM_RD.SOLN_STAR_MATRIX_123456_TMP1
(
ATTRIBUTE_ID NUMBER(10),
FIELD VARCHAR2(30 BYTE),
ATTRIBUTE_NAME VARCHAR2(64 BYTE),
TABLE_NAME VARCHAR2(32 BYTE)
)
[Code]....
Just now framed the query as like...
select framedquery from ( SELECT CASE WHEN table_name='MT_MEMBERS_SCOPED'
THEN ' case when 1>0 then nvl (( select value from MT_MEMBERS_SCOPED where source_code=123499 and member_id= soln_test.member_id and attribute_id= '||attribute_id ||''||'),0) else ''00'' end ' || ATTRIBUTE_NAME ||', '
WHEN table_name='MT_MEMBERS'
[Code]....
My requirement is soln_test table contains 3 records ,for each record need to match member_id in the MT_MEMBERS_SCOPED for the four records in the table.. output should be 3 records in soln_test table + 4 records in SOLN_STAR_MATRIX_123456_TMP1 totally 7 records it means column to row...
if the member_id not exists in both tables it should return '0000' value.
View 9 Replies
View Related
Aug 8, 2011
I am getting ORA-39726 error when a dropping a column on a non-compressed table.
SQL> select distinct TABLE_OWNER,COMPRESSION,COMPRESS_FOR from dba_tab_partitions where TABLE_NAME='STM_AE_STMT_HISTORY_DETAIL';
TABLE_OWNER COMPRESSION COMPRESS_FOR
------------------- ---------------------- -------------------
APP_SU DISABLED
SQL> ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR;
ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR
*
ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables
View 2 Replies
View Related
Aug 16, 2011
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
View 13 Replies
View Related
Jul 20, 2009
We are using Oracle Streams for replication.
Column datatypes in some of the tables on Source and Destination are different (Number on Source and Varchar2 on destination).
Do we have to create any rule or dml handler to handle this or Streams will automatically take care of it?We are oracle 10g.
View 1 Replies
View Related
Jan 11, 2012
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
View 7 Replies
View Related
Nov 19, 2010
I have one table in a particular database and will have to create another table in a different database. I will have to join these two tables from two different DB's.
How should I go about doing this? Is this a good practice? or is it always better to use a single DB?
What are the disadvantages of joining two tables from two different DB's?
View 1 Replies
View Related
Feb 9, 2012
The database system they have has around 5000 tables. The majority of these are not used (it is an off the shelf package). What I am trying to do is get a list of all tables in the database BUT only those that contain records (not the empty tables) and then copy this to an excel spreadsheet. We use a tool called aquadata to run sql enquiry statements on.
View 5 Replies
View Related
Jun 8, 2010
I need to find all the tables present in DB or Schema.
View 3 Replies
View Related
May 18, 2010
i work on oracle 8i with around 950 tables in my database. when i export or import it gives me the no of rows exported / imported from each table. is it possible to take a print out of the no of rows in each table through a single query .
select count(*) from each table takes a long time , since there are 950 tables.
View 4 Replies
View Related
Sep 25, 2012
we are looking to use oracle 11g as a log database using partitioned tables.
- The tables will have only a 3-5 columns of ~varchar(50) size
- We are looking at a volumn of ~33million rows (inserts) per day.
1) Will partitioned tables be able to handle this type of volume?
2) if yes, will a Composite Partitioning (using last modified datetime as range) then subparition with range be the best choice?
if this type of volume is too high for 11g, what are some of the alternative products we can use.
View 5 Replies
View Related
Jan 28, 2011
I've recieved a recent request wherein the requirement is to swap values between columns across multiple tables in a database.Following is a visual sample of what needs to be done.
Before Swaping:
Columns Values
TABLE1.IDENTIFIER MRN123
TABLE1.MEDICARENO CHI1234567
TABLE2.MRN MRN123
TABLE3.MRN MRN123
OTHERTABLE.MRN MRN123
After Swapping:
Columns Values
TABLE1.IDENTIFIER CHI1234567
TABLE1.MEDICARENO MRN123
TABLE2.MRN CHI1234567
TABLE3.MRN CHI1234567
OTHERTABLE.MRN CHI1234567
View 12 Replies
View Related
Jun 14, 2001
I am trying to describe all the tables in a database.We use desc or describe tablename; to describe a table, but what is the command to describe all the tables in a database (i don't need the system tables)
Once i log into sqlplus as a say ABC (SID or HostString) as a user then if i do a desc table name i get column name, data type and null not null etc but i i need that for all the tables in that ABC database..
View 8 Replies
View Related
Dec 13, 2010
I have installed oracle 10g software and created Database on RHEL 4. when i run the run.sql its successively done but im not getting tables. whats the prob.
View 17 Replies
View Related
Jun 14, 2013
Was going through this and found that
“AUDIT DELETE ANY TABLE BY <username> BY ACCESS” or “AUDIT UPDATE ANY TABLE<username> BY ACCESS”
enable audit for delete and updates for given username/schema. I want to enable auditing on delete and update on my entire database.
Why? And have we tested it in our any of existing setup? I am thinking of “Trigger after delete” but again this logic gets struck at individual tables. It do not work simply once and all for complete database/all users/all schemas
View 15 Replies
View Related
Aug 4, 2011
i want a query/function/procedure to check recently updated columns/tables in a database...
View -1 Replies
View Related
Jul 1, 2013
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob
7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
View 3 Replies
View Related
May 16, 2012
I have seen the below blog abut database driven menu in java. uRL..... look into the below tables and its data below.I have concerned inserting data in the tables and particularly about
ROLE_DETAILS,ROLE_MASTER and EM_MENUMAS data.
select
level lvl,
ID, NAME,
lpad(' ', level * 14, ' ') || NAME menu,
parent_NAME,nodetype, page_name from em_menumas
[code].....
View 10 Replies
View Related
May 13, 2013
I am trying to attach lifecycle definitions to tables of Oracle EBS R12 database, using the "Refresh table list" option of ILM assitant.
Though its showing the refresh job running to completion, but its not displaying the list all of tables.
Tried it with narrowing the search for a single schema but no luck.
how to get the list of all tables or at least list of tables belonging to a schema in Oracle EBS R12.
View 1 Replies
View Related
Sep 22, 2010
I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).
For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?
Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.
View 1 Replies
View Related
Nov 16, 2010
it's possible to retrieve the names of tables that contain similar information in an oracle database.
View 7 Replies
View Related
Mar 5, 2013
We are test Uniform ODBC drivers. When we run a new Database in Microsoft Access to import tables through the ODBC we get this error
Internal Error – unable to initialise NLS during driver load.
View 2 Replies
View Related
Feb 4, 2011
How to find the tables in the database on which high DMLs are firing.
View 5 Replies
View Related
Jun 17, 2013
i've following code
declare
i INTEGER:=0;
flag boolean:= FALSE;
vcInsert:='INSERT INTO CRM_DELT_IMPORT (';
if length(i_vcColumn1) > 0 then
flag:=TRUE;
[code]....
i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.
if i put this condition
vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
i get the error
to_char must be declare.
if i remove this line
i am getting this error ORA-01843: not a valid month
View 8 Replies
View Related
