AnsweredAssumed Answered

How to display dynamic lookup values with Nsql Case statement ?

Question asked by Calemirou on Jun 15, 2016
Latest reply on Jun 15, 2016 by Dave_3.0

Hello, i created a dynamic lookup with case statemement that shows values when the condition is true, it works fine on my page, but when i click on "Lookup Preview" it show nothing...

I need to pick the lookup values for a process condition

This is the lookup preview :

2016-06-15_113950.png

And this is my lookup populated with values in my page:

 

2016-06-15_114229.png

 

This is my query:

 

select 
@SELECT:aa.bbb:date_mep_value@,
@SELECT:aa.aaa:date_mep@

 from(
SELECT   ccn.name,date_mep_pro_reel dater,

case  when odfp.date_mep_pro_reel is null and ccn.NAME='Abondonné' then  'Abondonné'
when odfp.date_mep_pro_reel is  null and ccn.NAME='Terminé' then  'Terminé'
when odfp.date_mep_pro_reel  is null and ccn.NAME='En cours' then  'En cours'
when odfp.date_mep_pro_reel is  null and ccn.NAME='Non débuté' then  'Non débuté'
when odfp.date_mep_pro_reel is  null and ccn.NAME='Suspendu' then  'Suspendu'
when odfp.date_mep_pro_reel is not null and ccn.NAME='En cours' then  'En cours'
when odfp.date_mep_pro_reel is not null and ccn.NAME='Non débuté' then  'Non débuté'
when odfp.date_mep_pro_reel is not null and ccn.NAME='Suspendu' then  'Suspendu'
end as aaa,

case  when odfp.date_mep_pro_reel is null and ccn.NAME='Abondonné' then  5002085
when odfp.date_mep_pro_reel is  null and ccn.NAME='Terminé' then  4043
when odfp.date_mep_pro_reel  is null and ccn.NAME='En cours' then  4041
when odfp.date_mep_pro_reel is  null and ccn.NAME='Non débuté' then  5002084
when odfp.date_mep_pro_reel is  null and ccn.NAME='Suspendu' then  4042
when odfp.date_mep_pro_reel is not null and ccn.NAME='En cours' then  4041
when odfp.date_mep_pro_reel is not null and ccn.NAME='Non débuté' then  5002084
when odfp.date_mep_pro_reel is not null and ccn.NAME='Suspendu' then  4042

end as bbb
   FROM CMN_LOOKUPS CL, CMN_LOOKUP_TYPES CLT, CMN_CAPTIONS_NLS CCN,odf_ca_project odfp,INV_INVESTMENTS inv
 WHERE CL.LOOKUP_TYPE = CLT.LOOKUP_TYPE
 and inv.id = odfp.id
and odfp.ID=@where:param:xml:string:/data/id/@value@
   AND CL.ID = CCN.PK_ID
   AND CCN.LANGUAGE_CODE = 'fr'
   AND upper(CLT.LOOKUP_TYPE) = 'PAC_RPT_PROJSTATUS'
   AND CCN.TABLE_NAME = 'CMN_LOOKUPS'
)aa WHERE aaa is not null and @filter@

 

Any suggestion ??

 

Regards

Outcomes