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
- 7424 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 |
Hi all, i am new to this site and new to Informatica too. I have few questions regarding that. 1) When we load flat files into target tables how do we identify duplicates? and where do load the duplicate records for further reference? 2) How do we do chage data capture? Is this Slowly changing Dimension technique? Thanks in Advance
Can you generate reports in Informatcia?
How to differentiate between the active and passive transformations?
what is the logic will you implement to load data into a fact table from n dimension tables?
 Code page compatibly –Where all changes are made to handle the scenario.
How we can confirm all mappings in the repository simultaneously?
What do you understand by a term domain?
what is incremental data aggregation?
in flatfile target how can u create header and footer plese give me the comands how will u write in session properties
Did u used latest transformations of 8.6.0? for what?
Workflow is long running due to long running sql query so when we refer the query plan it tells the issue is due to partition of the db table. How to handle this?
What are batches?
Teradata 
