Post reply

CASE Statement

  • April 27, 2020 at 8:26 am

    Go to Answer

    #3746542

    Hi All,

    I have a simple case statement that won't work and I can't see where the error is, missing a bracket or something not sure. The syntax error is appearing at the, END as 'Measure'

    Thanks for any help.

    DECLARE @1startdate DATE = '2014-01-01'
           , @2Endate DATE = '2019-09-30'
       ,@3Policy VARCHAR(50) = 'Policy_View' 
       ,@4Channel VARCHAR(6) = 'DIRECT' 
       ,@5FactorFilter VARCHAR(50) = 'Alarm'     
       ,@6FFLevel VARCHAR(100) = 'Alarm test' 
       ,@7Factorby VARCHAR(25) = 'Age_Group' 
       ,@8FFSplit VARCHAR(25) = 'Age_Group test' 
       ,@9BarMeasure VARCHAR(100) 
       ,@10LineMeasure VARCHAR(100)
       ,@11BMeasureName VARCHAR(100) = '[B_Sum_Hail_Incurred]' 
       ,@12CMeasureName VARCHAR(100) = '[C_Sum_Earthquake_Incurred]' 
       ,@13BCMeasureName VARCHAR(100) = '[BC_Earned_Premium]' 



    SELECT @7Factorby 'FactorBy'
          ,@8FFSplit 'FactorSplit'
      ,@3Policy 'Policy'
      ,CASE WHEN @3Policy = 'Policy_View'
           THEN(CASE WHEN @13BCMeasureName = 'BC_Earned_Premium' THEN SUM([BC_Earned_Premium]) 
                 WHEN @13BCMeasureName = 'BC_Earned_Units'   THEN SUM([BC_Earned_Units]) 
    ELSE 0END)
       ELSE 
    CASE WHEN @3Policy = 'Contents_View' 
      THEN (CASE WHEN @12CMeasureName = 'C_Sum_Earned_Premium' THEN SUM([C_Sum_Earned_Premium]) 
                         WHEN @12CMeasureNAme = 'C_Sum_Earned_Units' THEN SUM([C_Sum_Earned_Units])
    ELSE 0   END  )
               ELSE
    CASE WHEN @3Policy = 'Building_View'
     THEN(CASE WHEN @13BCMeasureName = 'B_Sum_Earned_Premium' THEN SUM([B_Sum_Earned_Premium])
                       WHEN @13BCMeasureName = '[B_Sum_Earned_Units]' THEN SUM([B_Sum_Earned_Units])
                  ELSE 0      END) END AS 'Measure'
    --,@1startdate 'startdate'
    --,@2Endate  'enddate'
    --,@5FactorFilter 'FactorFilter'
    --,@6FFLevel 'FFLevel'
    FROM [database].[dbo].
     f
    WHERE startofquarter >= @1startdate AND endofquarter <=@2Endate
    AND channel = @4Channel AND  @5FactorFilter like  '%' + @6FFLevel + @5FactorFilter + '%';

    • This topic was modified 4 years, 6 months ago by  ringovski.
    • This topic was modified 4 years, 6 months ago by  ringovski.

  • April 27, 2020 at 8:52 am

    Answer

    #3746545

    You need a couple more "ends" at the END AS 'MEASURE'

     

    END

    END

    END AS 'Measure'

     

    Your not closing the outer case clauses for the parameter check

  • April 27, 2020 at 9:02 am

    #3746554

    Thanks Anthony, I knew it was something stupid.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply