Loop on sql query

[Ayoub]

Hey community, 

 I have a strange Issue and  i can not found the prob, Here's the detail . 

i Extracted all Data into first table called it Data with 2000 rows (2000 code client). 

I have already 2 filter created st_0 & st_1 each one contains 1000 ID or as i called it Code_Client.  

So i have to Extract data from different table where code client in st_0 & st_1, but in the end of the script i have only the details of the first itération (St_0)..

the code below is the extraction i want to do.  

______________________________________________________________________

Data:
LOAD
CLICODECLI as Code_client_Orc,
date(CLIDJJETAT&'/'&CLIDMMETAT&'/'&CLIDAAETAT) as DATE_ETAT,
CLINUMETEL as N°_TÉL
;

SQl Select
CLICODECLI,
CLIDJJETAT,
CLIDMMETAT,
CLIDAAETAT,
CLINUMETEL
From REFTIERMAW.REFCLIPP00 where CLIDMMETAT=2 and CLIDAAETAT=2018 ;

for b=0 to 1 step 1

/* Tiers PP*/
left join(Data)
LOAD
CLPCODECLI                              as Code_client_Orc ,
CLPNOMCLIP                             as NOM_CLIENT,
CLPPRECLIP                               as PRENOM_CLIENT;

SQl Select

CLPCODECLI,
CLPNOMCLIP,
CLPPRECLIP

From REFTIERMAW.REFCLPPP00 where CLPCODECLI in $(st_$(b)) ;

/*Tiers PM*/
left join(Data)
LOAD
CLMCODECLI                              as Code_client_Orc,  
CLMNBRSALR                             as CAPITAL;

SQl select
CLMCODECLI,  
CLMNBRSALR

From REFTIERMAW.REFCLMPP00 where CLMCODECLI in $(st_$(b)) ;

/* Adresse */
left join(Data)
LOAD

ADCCODECLI                                as Code_client_Orc,
ADCLIBLADC                                as LIBELLE_ADRESSE,
ADCCODEVIL                                as CODE_VILLE,
ADCCODEPAY                               as CODE_PAYS;

SQL  Select
ADCCODECLI,
ADCLIBLADC,
ADCCODEVIL,
ADCCODEPAY

From REFTIERMAW.REFADCPP00 where ADCCODECLI in $(st_$(b));

NEXT

[marcus_sommer]

Usually you couldn't join within a loop because your added fields from the first iteration will be considered within the next iterations ...

- Marcus

[Ayoub]

Hi @marcus_sommer Marcus,once again thank you for your help  😄 

So you say there's no solution for that problem ? otherwise can u suggest me something to do ..  

thanks again  

[marcus_sommer · Accepted solution]

You may load it seperately like: first load + joins then second load + joins and then concatenate the first and the second part. Without knowing your data and requirements it's difficult to say more but somehow your tried approach looked rather unusual.

- Marcus

[Ayoub · Accepted solution]

Huge thanks to  you @marcus_sommer  .  

Worked like magic .. i stored every iteration in deferent table and dropped the table and filters from the application and it's working 😄 Thank you again