Loop in DWC? syntax for correct execution

[karenave]

Hello

I'm having a problem, trying to do a SQL Script, Table Function in DataWareHouse Cloud

1 . I have a query that returns 2 records as seen in the image belo

Now what I want is to go through this query record by record, I am doing it using a For loop, as seen in the image

What I want to obtain is that for each iteration, a query is made to a table using as a condition the id of the records of the first column, but in the end I only get one record, regardless of whether I add the return output inside or outside the loop.

Try to do the same but using a cursor and I get the same result.

I am trying to make the end with this code. is to iterate to bring you a top 1 of each repeated code.

I appreciate any collaboration.

[karenave]

xavierpolo sven.knpfler4

[Jörg_Brandeis]

Hi Karen,

there is no need for a LOOP. You are thinking too complicated. Try an INNER JOIN instead. This should look like this:

DO BEGIN
 lt_codigo = 
	SELECT cod_cliente as codigo
	FROM rt_tt_clientestmp
	WHERE status = 'Activo'
	GROUP BY cod_cliente
	HAVING COUNT(*) > 1;
	
 SELECT cod_cliente, 
        cod_canal, 
		txt_canal, 
		cod_subcanal, 
		fila
   FROM rt_tt_clientestmp AS t1
   INNER JOIN :lt_codigo AS t2
   ON t1.cod_cliente = t2.codigo;

END;

With best regards,
Jörg

[karenave]

Hi Jörg, I am trying to make the end with this code. is to iterate to bring you a top 1 of each repeated code. I am trying to find the correctness of the data since in the DWC tables there is no upsert or overwrite behavior that ends up complicating things a bit.