Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  XQE-V5-0017 V5 syntax error

    Posted Sun October 15, 2023 10:20 AM

    Dear all,

    I am getting the below error while adding expressions in the report could you please help me to fix this? 

    Error- XQE-V5-0017 V5 syntax error found for data item 'Hostedin' of query 'Query2', invalid token "else" found after "case when ([FNMP-DW-DS].[Virtual Machine].[Located in Service Provider Datacenter]=0) then ('On-premises' ".
    CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2023-10-13-18:47:57.561-#12322

    Expression defination:

    case when ([FNMP-DW-DS].[Virtual Machine].[Located in Service Provider Datacenter]=0) then ('On-premises' else 'Cloud')
    when ([FNMP-DW-DS].[Computer-General].[Computer Type] in ('computer', 'VM host')) then ('On-premises' else ' ') end

    Thanks,

    Milind



    ------------------------------
    Milind Teli
    ------------------------------



  • 2.  RE: XQE-V5-0017 V5 syntax error

    Posted Mon October 16, 2023 03:34 AM

    the format of a simple case statement is

    CASE
      WHEN <condition> THEN value
      WHEN <condition> THEN value
      ELSE value
    END

    your case statement has ELSE in it in the wrong place. Your case statement looks like

    CASE
       WHEN condition THEN value ELSE value
       WHEN condition THEN value ELSE value 
    END

      



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message


  • 3.  RE: XQE-V5-0017 V5 syntax error

    Posted Fri October 20, 2023 01:46 AM

    Hi Marc,

    I tried to like that because both the sources have a different value. for the below statement, there are other computer types that I cannot mark as cloud hence kept blank.

    when [FNMP-DW-DS].[Computer-General].[Computer Type] in ('computer', 'VM host') then 'On-premises'

    else ' '

    Thanks,

    Milind



    ------------------------------
    Milind Teli
    ------------------------------

    Original Message


  • 4.  RE: XQE-V5-0017 V5 syntax error

    Posted Mon October 16, 2023 06:29 AM
    Edited by Chris Turner Mon October 16, 2023 06:29 AM

    I think your CASE statement should be something like this:

    case

    when [FNMP-DW-DS].[Virtual Machine].[Located in Service Provider Datacenter]=0 then 'On-premises'

    when [FNMP-DW-DS].[Virtual Machine].[Located in Service Provider Datacenter] <>0 then 'Cloud'

    when [FNMP-DW-DS].[Computer-General].[Computer Type] in ('computer', 'VM host') then 'On-premises'

    else ' '

    end



    ------------------------------
    Chris Turner
    ------------------------------

    Original Message


  • 5.  RE: XQE-V5-0017 V5 syntax error

    Posted Fri October 20, 2023 01:42 AM

    Hi Chris,

    I tried as above but it is not giving the expected output for 3rd statement. could you please help?

    Thanks,

    Milind



    ------------------------------
    Milind Teli
    ------------------------------

    Original Message


  • 6.  RE: XQE-V5-0017 V5 syntax error

    Posted Fri October 20, 2023 03:56 AM

    Hi Milind,

    That's likely to be a combination of your data, and the way that CASE statements work. When a case statement gets to a branch that evaluates to true, it executes that branch then exits the statement. I don't know what your data is like, or how you want to evalute it, but you might need to play around with the sequence of the branches. for example:


    case

      when [FNMP-DW-DS].[Virtual Machine].[Located in Service Provider Datacenter]=0 then 'On-premises'

      when [FNMP-DW-DS].[Computer-General].[Computer Type] in ('computer', 'VM host') then 'On-premises'

      when [FNMP-DW-DS].[Virtual Machine].[Located in Service Provider Datacenter] <>0 then 'Cloud'

    else ' '

    end



    ------------------------------
    Chris Turner
    ------------------------------

    Original Message