Hello @ludovic.rougean 

Is the field CF$_UO2F part of the LRN_U_O2_CLV view?

Thanks,

JL

Hi, no it belongs to another logical unit L_r_n_t_g_t_clv where i wish to retrieve the country code based on the data entered in cf$_uo2f.

cf$_uo2f is just a persistent attribute in L_r_n_t_g_t_clv to choose cf$_uo2 from the view LRN_U_O2_CLV. In this same view there is another field, the country code CF$_COUNTRY2, that i wish to retrieve as well.

to summarize: in view A there are 2 fields that i wish to retrieve in view B. if i choose the first one in view B, it will fetch the second one which is on the same row. Both of the view are from custom logical units, and that seems to be a problem. 

@ludovic.rougean 

It would be best to use a field from the LRN_U_O2_CLV you are trying to query or to create a subquery referencing another view of the LRN_U_O2_CLV. 

I hope this help!

Thanks,

JL

I actually noticed from your screenshots above you are trying to create the custom field on the view L_R_N_T_G_T_CLV but your query you are referring to the view LRN_U_O2_VLT in your where clause you are using k.CF$_U02 but the  =:CF$_UO2F => Is CF$_UO2F part of the L_R_N_T_G_T_CLV view.?

If your answer is not .. You need to reference a field from the view L_R_N_T_G_T_CLV.

You will need to use something like this:

SELECT CF$_COUNTRY2 FROM LRN_U_O2_CLT k

WHERE

K.CF$_U02 =: (Here you need to create a subquery reference the view L_R_N_T_G_T_CLV because I believe you are build the custom field on the view L_R_N_T_G_T_CLV) I recommend to use the GET API created when you created the CLU   L_R_N_T_G_T_CLV.

I hope I understood what you are tying to do. 

Thanks,

JL

Hi, Ok I found the reason, so for lesson learnt:

If you create 2 custom logical unit A and B. In LU B, you create a persistent attribute to query the field ‘COUNTRY’ from the LU A, for example, the value ‘ANGOLA’. The value stored in the LU A is not ANGOLA, but the OBJID of the row where ANGOLA is located in the LU A. So if you want to query LU A based on the value stored in LU B, you must query the OBJID. In my case  the statement would be:

Thanks anyway for your support!

Just to complete what I wrote earlier, although it was interesting in terms of understanding how IFS works, the easiest way to achieve the same is simply to choose ‘Reference’ for the custom field, it will give you the data straight away without any headache.