SQL & PL/SQL :: Row Level To Column Level Data Transposition?
May 14, 2010
I have a data like,
1) manual_temp_master
auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010
2)manual_temp_detl
auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010
[code]....
I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:
sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010
View 8 Replies
ADVERTISEMENT
Nov 17, 2010
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies
View Related
Feb 9, 2013
The below query returns level and other selected columns. I need to get the max(level) 2nd column value in the below example.How to modify the query?
Ex
Level max(level) id
1 5 101
1 5 102
1 5 103
2 5 104
2 5 105
3 5 107
4 5 120
5 5 134
5 5 280
SELECT DISTINCT level lvl
,form_frms.emp_id
,form_frms.ing_emp_id
,form_frms.prve_id
,CASE
WHEN (select div_dn
[code].......
View 5 Replies
View Related
Oct 22, 2012
Can you take an incremental backup level 1 or level 0 without archivelogs?
syntax would bebackup as compressed backupset cummulative level 1 database.
The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.
View 20 Replies
View Related
Jul 4, 2012
i am using 10.2.0.1.0 version of oracle.
I have gathered frequency histogram manually on one of my column of a table to provide more information to optimizer for better calculation of cardinality.
Now i have my weekend job runs for gathering stats on schema level with method_opt as 'For all column size repeat'. But i don't want the stats of above column to be overridden by the stats job. I don't want to lock the statistics of whole table, but i just want to lock the column level stats for this table.
View 7 Replies
View Related
Apr 11, 2013
between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.
which will execute first on update DML event ?
View 2 Replies
View Related
Dec 24, 2010
through Difference between table Level, column Level ,row level constraints.
View 8 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
May 4, 2011
--here's my set up
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;
CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;
[Code] .....
my desired outcome for user 1
user1> select * from schemaowner.user_data;
user_id column1
1 USER1
my desired outcome for user2 are as follow:
user1> select * from schemaowner.user_data;
user_id column2
2 TESTER 2
the nearest solution is from with reference to [URL]
Quote:
Adding Policies for Column-Level VPD
....
SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;
the database returns a subset of rows as follows:
ENAME DNAME JOB SAL COMM
-------------- -------------- ------------ ------------ -------------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950
so how do I implement roll level and column level simultaneously?
View 3 Replies
View Related
May 1, 2013
I have a requirement where i have data spread in rows which i have to pivot kind off operation to find the time taken using a Log table.
below is the existing data is present.
col0 col1 col2 col3 col4
30003695/1/2013 8:41Batch Startedproc_unalloc1345
30003705/1/2013 8:41blah proc_unalloc1345
30003715/1/2013 8:46blah proc_unalloc1345
30003725/1/2013 8:46blah proc_unalloc1345
30003735/1/2013 8:56blah proc_unalloc1345
30003745/1/2013 8:57Unalloc Endedproc_unalloc1345
[code].....
Below is what I am trying to find from the above data.
col1Unalloc Alloc Metric
13450.16 1.07 1.3
the result is Hours.Minutes.
I am trying to minus the two dates and find the time taken for a particular section( eg, unalloc ).
I tried using three union queries .
Select block,timestamp From (
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Batch Started' And col3 = 'proc_unalloc' And
Parms Like '1345'
Union
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Unalloc Ended' And col3 = 'proc_unalloc' And
[code].....
I will minus the start and end time for unalloc to get time taken for un alloc. Then i will minus end time of unalloc and end time for alloc to get time taken for alloc... like wise for metric.
View 6 Replies
View Related
Feb 17, 2010
Is it possible that we restrict user at data level? For Example 'A' user can only query employeess of deptno 10 only. He can not query employees of others dept.
View 6 Replies
View Related
Jun 29, 2011
whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?
View 3 Replies
View Related
Apr 12, 2011
We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?
View 2 Replies
View Related
Apr 26, 2013
Our client is having requirements that data should be encrypted through-out the process (from transaction level to backup level).
Requirements are as below -
1) Data should be encrypted and can be access only through application (through front end).
2) Even if pl/sql, sql installed, developer user cannot able to fetch data from database.
3) Even DBA, should not have access to fetch the data from the database.
4) Only assigned DBA, should fetch details from database (provided he/she should have details of database as well as application's user / password).
5) It should be restricted by ip and user details (such as ip of application server with user/password of user).
6) Changes should be purely done from database level only.
View 5 Replies
View Related
Nov 14, 2013
How can find the number of standby databases configured for primary database from os level
View 9 Replies
View Related
Aug 27, 2010
See the attachment file here.
Attached File(s)
query_request.txt ( 2.37K )
Number of downloads: 3
View 1 Replies
View Related
Mar 14, 2011
I have a transaction table with some custom properties and two status columns. There are 2 different applications(.Net and Pl/SQL Procedure) using the table. Both the process run parallel and fetch records one by one, perform some calculation and update the status column.
-.Net updates - Extraction_status
-Pl/SQL updates - Ingestion_status
There are likely more chances that both applications will fetch the sane record and try to update the same row. This will cause a lock. Can i use row level lock before update by each application? Or is there any other methods/process in which this can be handled. ?
View 11 Replies
View Related
Jun 3, 2013
I have about 200 tables, and each table has two columns : ( table_name_ID, local_id ).
example :
COST ( cost_id, local_id )
RATES ( rates_id, local_id )
SALARY ( salary_id, local_id )
I want the column local_id to be set to same value as table_id. ( local_id = cost_id for COST table; local_id = SALARY_ID in SALARY table etc.)
The code for trigger on these table will be generic and can be automtically generated.
But we are talking about 200 tables - means huge amount of testing with existing triggers on all these tables.
Is there any simpler method.
BTW - I purused following thread, and it talks about using AUDITING.
Re: Schema level Database triggers
But I wonder how AUDITING can be used in place of trigger to modify data ?
View 9 Replies
View Related
Jan 27, 2009
I'm trying to get different level using hierarchical query in sql.my table is
item_id child_item_id
------------------------------
p21 p25
p21 p22
p22 p23
p22 p24
p25 p27
p25 p26
p27 p28
p27 p29
p30 p31
p30 p32
I want to display result with respective levels.
for example p21 ,p30 are coming under first level .
p22,p25 ,P31,P32are 2nd level.
p23,p24,p26,p27 are 3rd level
p28,p29 are FOURTH level item_id's.
Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.
View 2 Replies
View Related
Jun 4, 2013
How to get only the last level in Oracle SQL Hierarchy Query?
View 2 Replies
View Related
Aug 27, 2013
1.) Whatever statics/data should we gather to compare the database performance before and after enable the audit on database level?
2.) How can we enable audit on database level?
3.)what is pros and crons of enable audit database level?
View 1 Replies
View Related
Feb 16, 2011
can a table level check constraints have conditional checking (if else clause or case conditional structures) and checks which are limited through something like a where clause which inside the table level check constraints.And can a table level check constraints refer to a column in another table column which should have a the same value.
View 1 Replies
View Related
Feb 24, 2010
I am unable to understand why row level triggers cant be used in mutating tables.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
View 1 Replies
View Related
Apr 27, 2012
We have recently upgraded our DR environment from 9i to 11g. We have auditing turned on for 3 tables.
On 9i, AUD$ table size is 11G for 12 months and the upgraded 11g environment has 9G in 2 days....
Below is the sql statement we used to turn on auditing on these tables.
audit select,update,delete on audit_Test2 by session;
Opened SR with Oracle, but no proper response from them...
what changes we need to do in order to reduce the amount of audit data on these tables in 11g?
View 8 Replies
View Related
Dec 13, 2011
For the list of userid's, how to find the list of OS/Restricted Shell ID's at the database level ?
View 7 Replies
View Related
Jun 1, 2010
I have a result set with three columns as 'Product Category', 'Product' & 'QtySales' and 10 rows, sorted in the order Product Category, Product. This means, a product category will have one or more products under it.
Now i want to add a fourth column to my result set, which should display a incremental number sequence from starting from 1, 2, 3.. for each row. Also when the value of the Product Category (1st column) changes, this sequence should be restarted again from 1.
Col1 Col2 Col3 Col4
PC1 P1 10 1
PC1 P2 20 2
PC2 P3 30 1
PC2 P4 10 2
PC2 P5 15 3
PC3 P6 25 1
View 1 Replies
View Related
Jul 18, 2013
I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.
CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
[code]......
Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';
At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.
SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;
I want to suppress such duplicates. So I added DISTINCT
SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;
BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.
View 11 Replies
View Related
Jun 24, 2010
I have created one master detail block..Master having form layout only one record and detail block is of tabular type having 10 record set
I need to do that .as master block enters entry and switch to detail block at the detail block 1st record should come with some default values which property need to use..
View 3 Replies
View Related
Oct 21, 2011
Below is the code that i tried to perform the sum operation.
CREATE TABLE TEST11
(
FISCAL_TIME_ID NUMBER,
data_id number,
M_VALUE NUMBER,
Y_VALUE NUMBER
);
[code].....
The result I got is
SQL> SELECT FISCAL_TIME_ID, DATA_ID, M_VALUE,
2 SUM(m_value) OVER (PARTITION BY fiscal_time_id, data_id
3 ORDER BY FISCAL_TIME_ID) AS YTD_VALUE
4 from test11;
[code].....
But what I am actually want to get is.
FISCAL_TIME_ID DATA_ID M_VALUE YTD_VALUE
-------------- ---------- ---------- ----------
20110500 3 2 2
20110700 3 50 52
20110800 3 52
20111000 3 250 352
20111100 3 300 652
That is, the YTD_Value column is nothing but sum of M_VALUE column + previous fiscal_month_id's ytd_value column.
Test case:
The YTD_VALUE for fiscal_time_id 20110700 is obtained as current M_VALUE + previous fiscal_time_id's ytd_value => 50 + 2
I tried with the SQL but i could not get the result.
View 5 Replies
View Related
Jun 20, 2012
I have written trigger to satisfy the requirement, when i write row level it comes out with mutating error on table which is very obvious, i have change it to statement level which is fine but it doesn't satisfy the requirement.
I want to capture the id column , which rows got inserted or updated .
Is there any way to get the newly inserted or updated rows as in case of row level i can get it form :new.id.
View 9 Replies
View Related
