My need is to build columns for each month in table and find changes between monthes. My first try works fine, but next step should be performed using wider period.
with t2 as (
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 840 as currencyid, 3649509 as dealid, 7.5 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 12 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 12 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 13.5 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 13 as avalue from dual union all
[code]....
As you can see, I've got virtual table named jan2008, feb2008, mar2008, apr2008 left-joined to tt1 table. But what should I do with much more wider period, for example 5 years? Can I do it without creating virtual tables for each month?
I read that few privilegs can not be granted to roles like 'UNLIMITED TABLESPACE ' and they need to granted to user directly.
1. Is there any reason behind that few privileges cant be granted to roles or is it just oracle design. 2. what kind of privileges cant be assigned to roles.
It seems certain queries search by the number of days to ship(number of days between the order and shipping dates). What kind of index would improve the performance of these queries?
what kind of exception can raise a select statement excluding NO_DATA_FOUND; For example i try to run the following: select * from departments where departments_id=11; In a situation like that what kind of error oracle can raise?I'm asking this because i have some procedure that just do a select statment and i want to know if there is a valid reason to put the exception others at the end of the procedure.
I have a requirement to transpose the column to rows like the one below, i am not getting the logic on how to do it, If any of you have come across this logic,
I need to transpose this table to below one. But since my rows in 1 table is dynamic and it will increment each week and month I couldn't get a correct result from unpivot.
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production versionI have following table - drop table t2;
create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select 5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;
select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using - SELECT * FROM T2;
I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS --------------------------------------- SEPTEMBER 200917790017790 OCTOBER 2009183831278818347 NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009 -------------------------------------------------------------- RMS 17790 18383 17790 RMS_OCC 0 12788 14605 TTL_RMS 17790 18347 17762
I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
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]......
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
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.
Insert into rent(customer_id) values (1); Insert into rent(customer_id) values (2); Insert into rent(customer_id) values (3); Insert into rent(customer_id) values (4); [code]...
Now my requirement is i need to assign doc_num from doc_id table to 4 customers in rent table randomly. I mean update doc_num in rent table from doc_id table randomly. how to write update statement.
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE The new table SQL> desc "TESTINVENTORY"."TTRANSACTION" Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- TRANSACTIONIDNOT NULL CHAR(26) BRANCHCODE NOT NULL CHAR(3) EXTERNALSYSTEM NOT NULL CHAR(3) EXTRACTSYSTEM NOT NULL CHAR(3) OWNERBRANCHCODE NOT NULL CHAR(3) TRADEREFERENCE NOT NULL CHAR(20) [code]...
