Forum Discussion

dsandip's avatar
dsandip
Frequent Visitor
3 years ago

IF else is not working

Hi,

I have a sql query condition like below:

CASE
WHEN Right(OPS_CNDCT_REF_PROFIT_CENTRE_NM,8)='Casualty' And Left(INSURED_NM,10)='Knighthood' THEN 'Y'
WHEN (Left(POLICY_NO,4)='0067' Or Left(POLICY_NO,4)='0065') and OPS_CNDCT_REF_PROFIT_CENTRE_NM like '%OCEAN MARINE%' THEN 'Y' ELSE 'N' END
AS "AIG_controlled_Indicator"

 

I am converting them to this below statement:

Column 3 =
IF(
    Right(TDIM_OPS_CNDCT_REF_PROFIT_CENTRE_PBI[OPS_CNDCT_REF_PROFIT_CENTRE_NM],8)="Casualty" && Left(TDIM_POLICY_PBI[INSURED_NM],10)="Knighthood", "Y", if(
            (Left(TDIM_POLICY_PBI[POLICY_NO],4)="0067" || Left(TDIM_POLICY_PBI[POLICY_NO],4)="0065") &&
            CONTAINSSTRING(TDIM_OPS_CNDCT_REF_PROFIT_CENTRE_PBI[OPS_CNDCT_REF_PROFIT_CENTRE_NM], "OCEAN MARINE")
            == TRUE() , "Y")            

)
 
But it gives me the below error:
A single value for column 'POLICY_NO' in table 'TDIM_POLICY_PBI' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
what is wrong here?
 
Thanks,
Sandip
Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    dsandip In measures, you need to wrap column references with an aggregator like MAX, MIN, SUM, AVERAGE, etc.

    • dsandip's avatar
      dsandip
      Frequent Visitor
      3 years ago

      Hi,

      I am not using Measure, rather I am using Calculated column here.

      So, what I have to do now.

       

      Thanks,

      Sandip