write s sql query following table some duplicate present i
want unique one column duplicate another column display?
name id
a 1
a 1
b 2
b 2
c 3
i want the required output like
unique duplicate
name id name id
a 1 a 1
b 2 b 2
c 3
- 4 Answers
- 8142 Views
- Bank Of America, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / ram pothineni
1) In SQ.. override the default query as below..
SELECT NAME,ID,ROW_NUM()OVER(PARTITION BY NAME ORDER BY
NAME)RN FROM TABLE_NAME
2) In router create two groups
UniqueGroup - RN = 1
DuplicateGroup - RN > 1
3) Connect Respective group to respective targets...
-- Ram Pothineni
| Is This Answer Correct ? | 5 Yes | 2 No |
Answer / anto
unique
select distinct name,id from table
duplicate
select name,id from table
group by name,id
having count(name||id) > 1
| Is This Answer Correct ? | 2 Yes | 0 No |
1.Sort the data
2.Expression transformation
a)create a variable1(integer) and var2(string)
b) var2 = concatenate all fields
b) set the value of the variable to 1 if (concat all fields are = var2)
c) create a o/p port = var2
-- output of the expression t/f will be like
NAME ID NEW_COL
a 1 0
a 1 1
b 1 0
b 1 1
c 1 0
3. Use a router transformation and route the data based on the new thirt column.
Happy ETLing :)
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / senthil
select a.nameid as unique_name_id,b.nameid as DUplicate_name_id
from
(select distinct nameid from name) a
left outer join
(select nameid from name group by nameid having count(*) > 1) b
on a.nameid = b.nameid
Output:
---------
unique_name_id Duplicate_name_id
a 1 a 1
b 2 b 2
c 3
| Is This Answer Correct ? | 0 Yes | 0 No |
How i can upload the MainFrame source For Informatica ?
In Lookup transformation a sql override should be done and disable the cache how do you do this procedure?
Suppose we are using a Dynamic Lookup in a Mapping and the commit Interval set for the tgt is 10000. Then how does the data get committed in the lookup if there were only 100 roows read from the src and the dynamic lookup dint have the 100th row in it?
how to create a sample mapping for dynamic lookup transformation
To provide support for Mainframes source data,which files r used as a COBOL files
can we use bulk loading when we use look in mapping
How to eliminate duplicate records in informatica mapping? Explain with an example....
What is the internal processes of integration server in Informatica? How data will be extract and load to the target?
Which transformation should we use to normalise the COBOL and relational sources?
How to eliminate duplicates from flat file..what is the procedure
What are events in workflow manager?
i have 50500 records in my source.if wf run for the first time it will load 1000 records into 1 tgt,if runs second time it will load to another tgt.targets are FF and it is need to be created dynamically.how many tgt will be created and how?
Teradata 
