AnsweredAssumed Answered

Help w/NSQL Query for Multi-Valued Lookup

Question asked by hcook on Jul 8, 2016
Latest reply on Jul 15, 2016 by Chris_Hackett

Looking through the forums, I've gotten VERY close to getting this to work, but still have something slightly off. I'm trying to get the field benefittype to display in the portlet, but am getting this error (for anyone who can't see the graphic, it's ERRORNPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete):

duplicatedimdata.GIF

I would really love for it to display in the portlet with a comma between the items if more than one has been selected (if that can be done).

 

Here's my code. I turned the applicable lines blue so you could see them easier. Any help MUCH appreciated!

 

Heidi

:

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:BENEFITS:b.code:code@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.id:id@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.approvaldate:approvaldate@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.costperhour:costperhour@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.fmcbenefitsdescript:fmcbenefitsdescripte@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.hoursneeded:hoursneeded@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.name:name@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.newhoursneeded:newhoursneeded@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.nextreviewdate:nextreviewdate@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.fmcbenefitnotes:fmcbenefitnotes@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.occurrence:occurrence@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.timevalue:timevalue@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:projectmanager:projectmanager@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:fmcprojectname:fmcprojectname@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:lkp_fmcbenefitvaluecomp:fmcbenefitvaluecomp@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costperdaycalc,0),'9,999,990.00'):costperdaycalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costperweekcalc,0),'9,999,990.00'):costperweekcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costpermonthcalc,0),'9,999,990.00'):costpermonthcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costperyearcalc,0),'9,999,990.00'):costperyearcalc@,      

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostperdaycalc,0),'9,999,990.00'):newnewcostperdaycalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostperweekcalc,0),'9,999,990.00'):newnewcostperweekcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostpermonthcalc,0),'9,999,990.00'):newcostpermonthcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostperyearcalc,0),'9,999,990.00'):newcostperyearcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.costperyearcalc - b.newcostperyearcalc),0),'9,999,990.00'):firstyearprodsavings@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrprodsavings * (b.costperyearcalc - b.newcostperyearcalc)),0),'9,999,990.00'):subsequentyrprodsavings@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:benriskdesc:benriskdesc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:benriskimpact:benriskimpact@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:costriskocc:costriskocc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:probofriskocc:probofriskocc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:numberwithin1yr:numberwithin1yr@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:numberwithin5yr:numberwithin5yr@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:numberwithin10yr:numberwithin10yr@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:probofriskoccscore:probofriskoccscore@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.dirpatientaff:dirpatientaff@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore)),0),'9,999,990.00'):firstyearrisksavings@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrrisksavings * (((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore))),0),'9,999,990.00'):subsequentyrrisksavings@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:dirpatientaffscore:dirpatientaffscore@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.costreducdesc:costreducdesc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.plndcostdec,0),'9,999,990.00'):plndcostdec@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.timevaluecostred:timevaluecostred@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredperdaycalc,0),'9,999,990.00'):costredperdaycalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredperweekcalc,0),'9,999,990.00'):costredperweekcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredpermonthcalc,0),'9,999,990.00'):costredpermonthcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredperyearcalc,0),'9,999,990.00'):costredperyearcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrcostred * costredperyearcalc),0),'9,999,990.00'):subsequentyrcostredcalc@,

 

 

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.revincdesc:revincdesc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincrease,0),'9,999,990.00'):revincrease@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.timevalrevinc:timevalrevinc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincperdaycalc,0),'9,999,990.00'):revincperdaycalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincperweekcalc,0),'9,999,990.00'):revincperweekcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincpermonthcalc,0),'9,999,990.00'):revincpermonthcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincperyearcalc,0),'9,999,990.00'):revincperyearcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrrevinc * revincperyearcalc),0),'9,999,990.00'):subsequentyrrevcalc@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:(b.costriskocc * b.probofriskoccscore)+(b.costredperyearcalc)+(b.revincperyearcalc)+((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore):allfirstyearsavings@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:((b.costriskocc * b.probofriskoccscore)+(b.costredperyearcalc)+(b.revincperyearcalc)+((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore))*b.subsequentyrcode:allsubsequentyearsavings@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.benefittype:benefittype@

           FROM

          (

          select

b.code code,

b.id,

b.approvaldate approvaldate,

b.costperhour costperhour,

b.fmcbenefitsdescript fmcbenefitsdescript,

b.hoursneeded hoursneeded,

b.name name,

b.newhoursneeded newhoursneeded,

b.nextreviewdate nextreviewdate,

b.fmcbenefitnotes fmcbenefitnotes,

b.occurrence occurrence,

b.timevalue timevalue,

case lkp_timevalue.name

     when 'Day' then (b.hoursneeded * b.costperhour)

     when 'Week' then ((b.hoursneeded * b.costperhour)/7)

     when 'Month' then ((b.hoursneeded * b.costperhour)/30)

     when 'Year' then ((b.hoursneeded * b.costperhour)/365)

     else 0

end costperdaycalc,

case lkp_timevalue.name

     when 'Day' then ((b.hoursneeded * b.costperhour)*7)

     when 'Week' then (b.hoursneeded * b.costperhour)

     when 'Month' then ((b.hoursneeded * b.costperhour)/4)

     when 'Year' then ((b.hoursneeded * b.costperhour)/52)

     else 0

end costperweekcalc,

case lkp_timevalue.name

     when 'Day' then ((b.hoursneeded * b.costperhour)*30)

     when 'Week' then ((b.hoursneeded * b.costperhour)*4)

     when 'Month' then (b.hoursneeded * b.costperhour)

     when 'Year' then ((b.hoursneeded * b.costperhour)/12)

     else 0

end costpermonthcalc,

case lkp_timevalue.name

     when 'Day' then ((b.hoursneeded * b.costperhour)*365)

     when 'Week' then ((b.hoursneeded * b.costperhour)*52)

     when 'Month' then ((b.hoursneeded * b.costperhour)*12)

     when 'Year' then (b.hoursneeded * b.costperhour)

     else 0

end costperyearcalc,

lkp_fmcbenefitvaluecomp.name lkp_fmcbenefitvaluecomp,

case lkp_timevalue.name

     when 'Day' then (b.newhoursneeded * b.costperhour)

     when 'Week' then ((b.newhoursneeded * b.costperhour)/7)

     when 'Month' then ((b.newhoursneeded * b.costperhour)/30)

     when 'Year' then ((b.newhoursneeded * b.costperhour)/365)

     else 0

end newcostperdaycalc,

case lkp_timevalue.name

     when 'Day' then ((b.newhoursneeded * b.costperhour)*7)

     when 'Week' then (b.newhoursneeded * b.costperhour)

     when 'Month' then ((b.newhoursneeded * b.costperhour)/4)

     when 'Year' then ((b.newhoursneeded * b.costperhour)/52)

     else 0

end newcostperweekcalc,

case lkp_timevalue.name

     when 'Day' then ((b.newhoursneeded * b.costperhour)*30)

     when 'Week' then ((b.newhoursneeded * b.costperhour)*4)

     when 'Month' then (b.newhoursneeded * b.costperhour)

     when 'Year' then ((b.newhoursneeded * b.costperhour)/12)

     else 0

end newcostpermonthcalc,

case lkp_timevalue.name

     when 'Day' then ((b.newhoursneeded * b.costperhour)*365)

     when 'Week' then ((b.newhoursneeded * b.costperhour)*52)

     when 'Month' then ((b.newhoursneeded * b.costperhour)*12)

     when 'Year' then (b.newhoursneeded * b.costperhour)

     else 0

end newcostperyearcalc,

srm.first_name || ' ' || srm.last_name projectmanager,

inv.name fmcprojectname,

case lkp_occurence.name

when 'Sustainable' then 1

else 0

end subsequentyrprodsavings,

b.benriskdesc benriskdesc,

b.benriskimpact benriskimpact,

b.costriskocc costriskocc,

b.probofriskocc probofriskocc,

b.numberwithin1yr numberwithin1yr,

b.numberwithin5yr numberwithin5yr,

b.numberwithin10yr numberwithin10yr,

 

 

case probofriskocc

when 'Low' then .01

when 'Medium' then .25

when 'High' then .5

else 0

end probofriskoccscore,

 

 

case lkp_occurence.name

when 'Sustainable' then 1

else 0

end subsequentyrrisksavings,

 

 

case b.dirpatientaff

when 4040 then 'No'

when 4039 then 'Yes'

else 'N/A'

end dirpatientaff,

 

 

case b.dirpatientaff

when 4040 then 1

when 4039 then 1.5

else 1

end dirpatientaffscore,

 

 

b.costreducdesc costreducdesc,

b.plndcostdec plndcostdec,

b.timevaluecostred timevaluecostred,

case lkp_timevaluecostred.name

     when 'Day' then (b.plndcostdec)

     when 'Week' then (b.plndcostdec / 7)

     when 'Month' then (b.plndcostdec / 30)

     when 'Year' then (b.plndcostdec / 365)

     else 0

end costredperdaycalc,

case lkp_timevaluecostred.name

     when 'Day' then (b.plndcostdec * 7)

     when 'Week' then (b.plndcostdec)

     when 'Month' then (b.plndcostdec / 4)

     when 'Year' then (b.plndcostdec / 52)

     else 0

end costredperweekcalc,

case lkp_timevaluecostred.name

     when 'Day' then (b.plndcostdec * 30)

     when 'Week' then (b.plndcostdec * 4)

     when 'Month' then (b.plndcostdec)

     when 'Year' then (b.plndcostdec / 12)

     else 0

end costredpermonthcalc,

case lkp_timevaluecostred.name

     when 'Day' then (b.plndcostdec * 365)

     when 'Week' then (b.plndcostdec * 52)

     when 'Month' then (b.plndcostdec * 12)

     when 'Year' then (b.plndcostdec)

     else 0

end costredperyearcalc,

case lkp_occurence.name

when 'Sustainable' then 1

else 0

end subsequentyrcostred,

 

 

 

 

b.revincdesc revincdesc,

b.revincrease revincrease,

b.timevalrevinc timevalrevinc,

case lkp_timevaluerevinc.name

     when 'Day' then (b.revincrease)

     when 'Week' then (b.revincrease / 7)

     when 'Month' then (b.revincrease / 30)

     when 'Year' then (b.revincrease / 365)

     else 0

end revincperdaycalc,

case lkp_timevaluerevinc.name

     when 'Day' then (b.revincrease * 7)

     when 'Week' then (b.revincrease)

     when 'Month' then (b.revincrease / 4)

     when 'Year' then (b.revincrease / 52)

     else 0

end revincperweekcalc,

case lkp_timevaluerevinc.name

     when 'Day' then (b.revincrease * 30)

     when 'Week' then (b.revincrease * 4)

     when 'Month' then (b.revincrease)

     when 'Year' then (b.revincrease / 12)

     else 0

end revincpermonthcalc,

case lkp_timevaluerevinc.name

     when 'Day' then (b.revincrease * 365)

     when 'Week' then (b.revincrease * 52)

     when 'Month' then (b.revincrease * 12)

     when 'Year' then (b.revincrease)

     else 0

end revincperyearcalc,

 

 

case lkp_occurence.name

when 'Sustainable' then 1

else 0

end subsequentyrrevinc,

 

 

case lkp_occurence.name

when 'Sustainable' then 1

else 0

end subsequentyrcode,

clv.name benefittype

 

 

 

 

 

 

from odf_ca_fmc_benefits_mgt b

 

 

LEFT join cmn_lookups_v lkp_timevalue on lkp_timevalue.lookup_code = b.timevalue and lkp_timevalue.lookup_type = 'TIMEVALUE' and lkp_timevalue.language_code = @WHERE:PARAM:LANGUAGE@

 

 

LEFT join cmn_lookups_v lkp_fmcbenefitvaluecomp on lkp_fmcbenefitvaluecomp.lookup_code = b.fmcbenefitvaluecomp and lkp_fmcbenefitvaluecomp.lookup_type = 'VALUECOMPASSPOINT' and lkp_fmcbenefitvaluecomp.language_code = @WHERE:PARAM:LANGUAGE@

 

 

LEFT join cmn_lookups_v lkp_occurence on lkp_occurence.lookup_code = b.occurrence and lkp_occurence.lookup_type = 'OCCURENCE' and lkp_occurence.language_code = @WHERE:PARAM:LANGUAGE@

 

 

LEFT join srm_resources srm ON srm.user_id = b.projectmanager

 

 

LEFT join inv_investments inv ON inv.id = b.fmcprojectname

 

 

LEFT join cmn_lookups_v lkp_timevaluecostred on lkp_timevaluecostred.lookup_code = b.timevaluecostred and lkp_timevaluecostred.lookup_type = 'TIMEVALUE' and lkp_timevaluecostred.language_code = @WHERE:PARAM:LANGUAGE@

 

 

LEFT join cmn_lookups_v lkp_timevaluerevinc on lkp_timevaluerevinc.lookup_code = b.timevalrevinc and lkp_timevaluerevinc.lookup_type = 'TIMEVALUE' and lkp_timevaluerevinc.language_code = @WHERE:PARAM:LANGUAGE@

 

 

LEFT JOIN ODF_MULTI_VALUED_LOOKUPS MUL on b.ID=MUL.PK_ID

LEFT JOIN CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'LKPBENEFITTYPE'

 

 

          ) b

          WHERE    1=1

          AND      @FILTER@

Outcomes