I have a MySQL input (Table 1) with a field which I am mapping to a unique field.
The input field contains duplicate values for the input, which can not be saved to the table (Table 2) due to MySQL duplicate error message.
What I am trying to do is when an insert fails alter the input value and try the insert again.
For example the output for an input is:
Table 1 input: id name 1 abc 2 xyz 3 abc 4 efg 5 abc 6 abc Table 2 output: id name 1 abc 2 xyz 3 abc#1 4 efg 5 abc#2 6 abc#3
I have looked at tLoop as a while loop but not sure how I use a condition of not inserted based on a MySQL query.
I am using the following query to find the next # value, where <current name> is the value in 'Table 1 name', e.g. abc
select count(*) from table 2 where name regex '<current name>#+$'
Which tells me what the next # value should be, but putting this all together into a job using a tMap has me scratching my head.
Am I trying to do the impossible?
Hi
Can't you remove the key on the name column ?
You should be able to insert duplicate values.
In case that doesn't solve your problem :
Create a routine that checks if the value exists then returns the formatted value (MyRoutine.check(myString) = "myString#X"). Use this routine on the "name" column.
And the "next" value can be stored in a file/db, this should make it easier to get.
Removing the unique key would allow for the insert but I would like to know how to go about solving the problem by using a rountine to update the name column.
How do I go about getting the rountine to run the query above and pass through the <current name>? The <current name> will be based on the current row being mapped, so I can't create a SQLInput for it.
Thanks
package routines; import java.util.HashMap; import java.util.Map; public class SNumeric { private static final Map<String, Integer> seq_Hash = new HashMap<String, Integer>(); private static final Map<String, Map<String,Integer>> key_Hash = new HashMap<String, Map<String,Integer>>(); /** * return the value corresponding to the key or an incremented numeric id * * {talendTypes} int | Integer * * {Category} Numeric * * {param} string("s1") sequence identifier * * {param} string("key") key * * {param} int(1) step * * {example} sequenceKey("s1", "abc", 1) # "abc", "abc#1", "abc#2", ... * * {example} sequenceKey("s1", "abc", 3) # "abc", "abc#3", "abc#6", ... * */ public static String setUniqueId(String seqName, String key, int step) { if (seq_Hash.containsKey(seqName)) { if (key_Hash.get(seqName).containsKey(key)) { return key + "#" + key_Hash.get(seqName).get(key); } else { Integer incValue = seq_Hash.get(seqName) + step; seq_Hash.put(seqName, incValue); key_Hash.get(seqName).put(key, incValue); return key + "#" + incValue; } } else { seq_Hash.put(seqName, startValue); key_Hash.put(seqName, new HashMap<Object, Integer>()); return key; } } }
Here is a routine that should fit your needs.
Instead of mapping
table1.name to
table2.name,
map
SNumeric.getUniqueId("seq", table1.name, 1) to
table2.name.
To get the current value of current name, you should have a table :
sequence label id seq abc 4 seq xyz 3 seq2 abc 1
Then get
all those informations and create an "init" routine that initializes all the sequences.
=== Second idea (think it's a better (easier) one than the first) ===
You also may be able to "group" datas with a tAggregateRow and count rows.
You should have something like :
id name count 1 abc 3 2 xyz 1 3 efg 1
Then you create a schema as below :
| v ----Iteration--->----Iteration--->----row--->----row--->
The tLoop use the "count" column of the tAggregateRow. Then you should be able to treat rows individually and use a tMySQLInput for each.
Sorry if my explanations aren't clear.
I'll try to use printscreens if you don't understand.
I've managed to get it to work a little bit, here's my screenshot.
I wasn't sure how to setup the tLoop though.
What it does at the moment is:
Table 1 input: id name 1 abc 2 xyz 3 abc 4 efg 5 abc 6 abc Table 2 output: id name 1 abc 2 xyz 3 abc#1 4 efg 5 unique key error tried to use the name: abc#1 6 unique key error tried to use the name: abc#1
This seems to be because the group by is on the name, which doesn't allow for abc and abc#1 to be grouped together for the count so that the next value would be abc#2.
The value after the # is the count from the group by.
Thanks for your help so far but think I need some more guidance.
Hi I have to create a job where i take
a Fist_Name and Last_Name from a mssql db
and combine them so that it creates a username
Ex If the First_Name = John and the Last_Name = Perry
it should output it as JPerry. But my problem lies that when I created
that user name it should then check the LDAP AD if such a user name already exists
and if it does my job should then add a 1 after the username to = JPerry1 then it must search
the LDAP AD agian and if theres already a JPerry1 ist should then change to JPerry2 and so on
until theres a unique user name and then it should update the new username in LDAP.
I have already got my job to convert the to username and check if such a name already exists,
my output will then show the usernames that already exists in LDAP AD, now how can I use Talend to add a 1
after the username and loop the check again until a unique username is created.
If someone can help me with that I will appreciate it very much.
