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
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 |
Enlist the tasks for which source qualifier transformation is used.
Enlist the advantages of informatica.
The structure of source file is as below: Source structure(two fields) Name, Card NUmber A, 111111111(SSN) A, 01010101(Creditcard number) A, 34343434(Debit card number) B, 55555555(Creditcard number) C, 77777777(Debit card number) Target Structure(4 fields) Name,Credit card,SSN,Debit card A,01010101,111111111, 34343434 B,55555555,, C,,,77777777 Corresponding to one name there can be maximum 3 rows and minimum zero rows. Given that I do not know which record might have a particular type of number. How can I handle above requirement with informatica transformations?
Explain the informatica workflow?
I have 1200 records how can u send it in aggregator with good performance issues?
What is difference macros and prompts?
write a sql query following source? subject mark maths 30 science 20 social 80 requird output maths science social 30 20 80
Target increases every day even though no new record was inserted
Can we call a stored procedure from a unix script which is run using command task
If you done any modifications for a table in back end does it reflect in informatca warehouse or maping?
how to calculate the optimum cache size in aggregator transformation?
Load data to multiple targets according date. When First time session runs it should send to 1st target,second time session runs then send to 2nd target and goes on how to achieve it