topic Re: How to get random value from a colum on a table for each row? in Talend Studio

How to get random value from a colum on a table for each row?

Anonymous — Fri, 25 May 2012 17:22:18 GMT

How can you get a random value from a table in a oracle DB?
I used this query with a tOracleInput:
SELECT * FROM table_name
WHERE primary_key IN
(SELECT primary_key FROM
(SELECT primary_key, FROM table_name ORDER BY SYS.DBMS_RANDOM.RANDOM 2)
WHERE rownum <= 10)
AND ROWNUM = 1;
Returns a random row.
With a TMap component I tried to use the output of tOracleInput, I selected "reload at each row", but returns all rows in the tabla_name and combines each row with each of the TMAP main flow.
I want to get one row for each row in the main flow, not a Cartesian product of rows of each flow.
Sorry for my English
regards
Thanks in advance

Re: How to get random value from a colum on a table for each row?

Anonymous — Fri, 25 May 2012 18:05:27 GMT

Hi Rolo,
Welcome to the forum.
Your subquery has an extra comma, so will fail on a syntax error. Did you try putting the tOracleInput to a tLogrow to confirm that it is returning only one row (it should based on ROWNUM = 1)?
In your tMap did you drag a field from the main flow onto the primary key of the lookup row to enforce a match? Also, did you specify the tMap join type to inner join?
Thanks,
Ben

Re: How to get random value from a colum on a table for each row?

Anonymous — Fri, 25 May 2012 21:39:13 GMT

Hi Ben,
Thanks for the welcome and reply.
The query does work, the query of the first post was to show the idea of how I get a random row, this is the exact query:
"SELECT *
FROM "+context.SGAE_Schema+".DEPARTAMENTOS
WHERE "+context.SGAE_Schema+".DEPARTAMENTOS.CODIGO IN (
SELECT "+context.SGAE_Schema+".DEPARTAMENTOS.CODIGO
FROM (SELECT "+context.SGAE_Schema+".DEPARTAMENTOS.CODIGO,
SYS.DBMS_RANDOM.RANDOM
FROM "+context.SGAE_Schema+".DEPARTAMENTOS ORDER BY 2)
WHERE rownum <= 10 )
AND rownum = 1"
I tried it and it works, but when using it in a TMap returns all rows in the table and combines them with the main flow.
I can not join, no attribute in common.
What I want is a random code from the DEPARTMENTOS table to combine with other values of the main flow.
The idea was to use this consultation to function as working with oracle sequences, that by choosing to "reload at Each road" brings the next sequence value for each row, I believed that using this option execute the query once each row as it seemed to a sequence, but this is not so.
I do not know if I'm clear, I will try to put some screenshots if not understood.
Sorry for my English
regards
thanks