Remove Duplicates From Slowly Changing Dimension
Jun 6, 2013
I've got a slowly changing dimension table for products with some duplicate attributes -
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 02-JAN-13 423 MONKEY 5
2 03-JAN-13 04-JAN-13 423 MONKEY 5
3 05-JAN-13 08-JAN-13 423 MONKEY 4
4 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
5 01-JAN-13 08-JAN-13 378 BANANA 2
6 09-JAN-13 10-JAN-13 378 BANANA 3
The natural key should be PRODUCT_ID, ATTRIBUTE_1 and ATTRIBUTE_2. The table should therefore be recreated as follows:
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 04-JAN-13 423 MONKEY 5
2 05-JAN-13 08-JAN-13 423 MONKEY 4
3 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
4 01-JAN-13 08-JAN-13 378 BANANA 2
5 09-JAN-13 10-JAN-13 378 BANANA 3
View 6 Replies
ADVERTISEMENT
Mar 24, 2011
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;
Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
View 4 Replies
View Related
Oct 11, 2013
Removing duplicates from a string that contains years and "-".
Example: 1988-1997-2000-2013-1998-1965-1997-1899
I know this can be done in regular expressions but have no experience in this subject.
select REGEXP_REPLACE(.....) from dual;
View 16 Replies
View Related
Aug 3, 2011
i have two tables
Quote:
Table has following data.
id indicator
---------------
1 A
2 A
3 A
Quote:
Table2 has
id indicator
---------------
1 X
1 X
2 X
3 X
I would like to have following output ( Am running query on Toad)
t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X
View 4 Replies
View Related
Nov 8, 2012
I want to remove duplicates from a column MAIN_TABLE based on TLEVEL Column:
create table UNIQ_TEMP
( TLEVEL NUMBER(10,0),
TABLE_NAME VARCHAR2(30),
MAIN_TABLE VARCHAR2(30)
);
[code]....
My Requirement is:
MAIN_TABLE= MARKETING_OPTIN
EXISTS IN MAX(TLEVEL)
REMOVE DUPLICATE MARKETING_OPTIN FROM OTHER LEVELS
This should apply to all the values in column MAIN_TABLE
View 12 Replies
View Related
May 12, 2011
I have a query like
SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
View 2 Replies
View Related
Jun 3, 2011
I am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
[Code]....
PLAN_TABLE_OUTPUT
Plan hash value: 611392786
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M|
[Code].....
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
View 16 Replies
View Related
Aug 6, 2012
In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join
CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS
[code]...
View 2 Replies
View Related
Apr 24, 2013
Using Application Express 4.2.1.00.08
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
View 3 Replies
View Related
Mar 14, 2011
creating a date dimension with columns like
id month_name date
1 Jan/2011 31/1/2011 (last date of the month)
2 feb/2011 28/2/2011!!
View 9 Replies
View Related
Apr 21, 2012
I have flattened customer dimension table and I would like to query it with other dimension table like address. I write a query, where I join address table twice to get permanent, secondary, and work addresses, but customer and address tables are huge that causing performance issue. Is any other ways to join flatten table with address dimensions than join it twice.
CREATE TABLE CUSTOMER
(
cust_sk NUMBER NOT NULL ,
cust_src_id VARCHAR2(20) NOT NULL ,
rec_eff_dt DATE NOT NULL ,
last_name VARCHAR2(75) NULL ,
first_name VARCHAR2(30) NULL
brth_dt DATE NULL ,
[code]......
View 6 Replies
View Related
Sep 28, 2010
the literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.
My question to put it simply is this: Given:
CREATE DIMENSION location_dim
LEVEL location IS (location.loc_id)
LEVEL city IS (location.city)
LEVEL state IS (location.state)
HIERARCHY geog_rollup (
location CHILD OF
city CHILD OF
state CHILD
)
Can I insert the following rows into the dimension:
loc_id, city, state
1, Epping, NSW
2, Epping, VIC
Please note that the two Eppings are different cities.
Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.
The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?
View 4 Replies
View Related
Nov 20, 2012
I am creating a "time aware" (DAY, WEEK, MONTH, QUARTER, and YEAR) dimension using Analytic Workspace Manager.
Let me give you some background. I'm coming from a traditional "Oracle Express" OLAP background where all our data is stored in cubes and these are defined, populated and operated on using OLAP DML, there is no SQL or traditional relational tables involved.
I now want to pull data from relational tables into some OLAP cubes and am using Analytic Workspace Manager to do this (maybe this is not the best way?)
Let me explain what I'm trying to achieve. In OLAP worksheet I can type the following DML commands:
DEFINE MY_DAY DIMENSION DAY
MAINTAIN MY_DAY ADD TODAY '01JAN2011'
What this will do is create a "day dimension" and will populate it with values for each and every day between 1st Jan 2011 and today. It will be fully "time aware" and thus you can use date functions such as DAYOF to limit the MY_DAY dimension to all the Fridays etc. Similarly if I define a "month dimension" there will be an automatic implicit relationship between these two dimensions, this relationship and time aware cleverness is built into Oracle.
However, a dimension defined using DML commands (and indeed all objects created using DML language) is not visible in Analytic Workspace Manager (as there is no metadata for them?) and for the life of me I cannot work out how to create such a dimension using AWM. If I create a "Time Dimension" then, as far as I can tell, this is not a proper time dimension but merely a text dimension and I, presume, I have to teach it time awareness.
I have no issues creating, and populating cubes from relational tables using Analytic Workspace Manager, the only issue I have is creating a "proper" time aware dimension.
View 1 Replies
View Related
Apr 12, 2013
At moment we use range-hash partitioning of a large dimension table (dimension model warehouse) table with 2 levels - range partitioned on columns only available at bottom level of hierarchy - date and issue_id.
Result is a partition with null value - assume would get a null partition in large fact table if was partitioned with reference to the large dimension.Large fact table similarly partitioned date range-hash local bitmap indexes
Suggested to use would get automatic partition-wise joins if used reference partitioningWould have thought would get that with range-hash on both dimension.
View 3 Replies
View Related
Nov 20, 2011
The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.
CREATE OR REPLACE PROCEDURE populate_product_dimension
AS
v_Count NUMBER := 0;
v_NumRecs NUMBER;
/*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/
[code]....
In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.
View 10 Replies
View Related
Jun 11, 2013
I have a table like below:
tableA
aid des
1 concrete
2 wood
3 straw
4 plastic
5 fiber glass
6 other
tableB
bid material
01 1
02 2
03 3
01 2
01 2
02 3
01 5
The result I need when updating another table with this info is:TableC
edw_id bid Requirement
021 1 concrete, wood, fiber glass
032
025
123
521
I do not want :
concrete, concrete, concrete, wood, wood, fiber glass
SO far I am using the following but since I am dealing with hundreds of column that has the same material, when using listagg() from oracle 11.2g, they column width is too wide to fit into the required column.
update eris_data_work e set E.flex37 =
(select
LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
from CODE_CASING_MATERIAL CM, TBLCASING CA
where CM.code=CA.MATERIAL and CA.well_id=E.owner_oid AND CM.DES IS NOT NULL
GROUP BY CA.well_id)
where E.source='WWIS_ON'
I have even used the regexp_count() to try to eliminate duplicates however I have had no success so far
View 4 Replies
View Related
Jun 17, 2008
I have a table1:
userid, name, town.
Now i want to do a self join like:
select a.name, b.name, a.town
from table1 a
inner join table1 b
on a.town = b.town
where a.first_name <> b.first_name AND
a.last_name <> b.last_name
i added the where clause to limit duplicates i would get but i still get duplicates eg. A B london, B A london etc.
View 9 Replies
View Related
Feb 14, 2012
I have to load 50 Million records in a table. I also need to ensure that no duplicate records enter. Business demand is that It may send 2 Million records today, 1 Million records tomorrow, 3 million records on the next day and so on...
I have loaded 6 Million records into the table, but onwards speed of loading records (with duplicate check) is decreasing.
View 16 Replies
View Related
Jul 19, 2010
I have need, where I need to pick-up the one of the records out of 2, which have all the fields same but one key column? how should I do it?
sample data:
IDnamecreation dateupdate date
121233ATR7/19/2010 15:307/19/2010 15:30
1213344MTR7/19/2010 15:307/19/2010 15:30
For most of the cases, i am picking latest data using update date, but here where dates are same, I am stuck.
View 2 Replies
View Related
Sep 19, 2013
db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.
View 5 Replies
View Related
Aug 17, 2012
I have a requirement with source table has duplicates so i need to select a row based upon the below condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
View 8 Replies
View Related
Oct 9, 2013
I ran an Inner Join but it returned many, many duplicates. One Answer from a different thread:Because you may be having improper Join condition what has a One to many or a Many to many relationship between the tables. We do not have the data, and hence cannot comment over it. What do I look for to identify an improper join?
View 2 Replies
View Related
Aug 17, 2012
I have a requirement with source table has duplicates so i need to select a row based upon the a condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
View 6 Replies
View Related
Feb 12, 2007
I'd like to achieve the following (and YES, I do know that this is not multi-user safe, but that's not the point here):
Before inserting a record, the trigger shall check if there's already a duplicate one. Duplicate means in this case when there is an intersection of the time frame, defined by two numeric timestamps. That's also the cause why I cannot use a simple UNIQUE constraint here (in my opinion).
Okay, that already works (see code below). But now I need colliding records to be written to a temporary table so that those records can be returned and presented to the user for selection.
create or replace
TRIGGER TRIGGER1
BEFORE INSERT ON FLIGHT_TABLE
FOR EACH ROW
BEGIN
FOR fs_entry IN (SELECT * FROM FLIGHT_TABLE)
LOOP
[Code] .........
View 1 Replies
View Related
Mar 29, 2010
I have a table like this basic example:
ID Product Color Time-In
1 Apple Green May
2 Apple Red April
3 Pear Green May
4 Pear Green April
5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May
2 Apple Red April
3 Pear Green May
5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
View 1 Replies
View Related
Mar 30, 2010
I am trying to delete duplicates from table without using rowid.
here is data:-
create table test(col1 number(3),col2 varchar2(20));
insert into test values(100,'rocky');
insert into test values(100,'rocky');
[Code]....
I know i am perfoming dml on view. IT wont allow me to perform DML on view which contain columns with expression. IS there any way to delete duplicates without using rowid?
View 36 Replies
View Related
Aug 30, 2010
I am trying to work with query to get distinct unique records, following is my scenerio:
DocNo |Title| Revs|UpdateTime|OBID
DOC-101|DESCRIPTION1|1|2/28/2004 11:37|6108-9
DOC-101|DESCRIPTION1|1|2/28/2004 11:36|6106-13
DOC-201|DESCRIPTION2|0|4/24/2005 16:44|7899-1
[code]...
From the above duplicates I would like to get only 1 distinct record based on latest update time (when using distinct on a sub-query since OBID is unique again returning all recds)
I am expecting results like:
DocNo|Title| Revs|UpdateTime|OBID
DOC-101|DESCRIPTION1|1|2/28/2004 11:37|6108-9
DOC-201|DESCRIPTION2|0|4/24/2005 16:47|7900-1
DOC-301|DESCRIPTION3|3|2/21/2007 7:26|6869-4
DOC-304|DESCRIPTION4|3|8/22/2007 9:31| 39208-1
View 3 Replies
View Related
May 31, 2012
I am trying to remove duplicates from a table with over 10million records. Below query is working fine but it doesnt contain any COMMIT interval. I have to commit after every 20k or 30k records deletion for which IF loop is necessary.
Query:
delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);
View 9 Replies
View Related
May 28, 2013
I have created a table and i have a column consisting of 1000 records (but where i have duplicates). And now i want to create a primary key for the column.
View 7 Replies
View Related
Aug 21, 2013
How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."
error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000)); TYPE ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1 ln_x_type; gc_stmt varchar2(4000); Begin gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions; EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1; ln_dist_x1:= ln_x1 MULTISET UNION DISTINCT ln_x1; End;
I need ln_dist_x1 to have distinct records from table.
View 12 Replies
View Related
