none
using CASE statement in a prediction query RRS feed

  • Question

  • Hi,

    Can i use a CASE statement in a prediction query.
    the following query is throwing me an error

    SELECT
      CASE [Sales Forecast Time Series].[City Code]
      when 'LA' then 'Los Angeles'
      WHEN 'CA' THEN 'California'
      ELSE 'OTHERS'
      END,
      PredictTimeSeries([Sales Forecast Time Series].[Sales Value],5)
    From
      [Sales Forecast Time Series]

    ERROR:
    Parser: The statement dialect could not be resolved due to ambiguity.

    Also

    Is it possible to discretize the Sales Value column using a the CASE statement, the output column of PredictTimeSeries function.

    Is there a link that can give me a comprehensive info on what can be achieved and what cant be using  DMX queries


    Saturday, August 5, 2006 7:13 AM

Answers

  • DMX does not support (yet) the CASE statement, although we consider supporting it in future versions.

    Fore a DMX reference, you could try: http://msdn2.microsoft.com/en-us/library/ms132025.aspx (Data Mining Extensions (DMX) Statement Reference).

    Both problems can be solved with some dedicated UDF (user defined functions) written in C# and deployed on the server as an assembly. The query would then look like:

    SELECT MyAssemblyID.MyNamespace.StateFromArea([Sales Forecast Time Series].[City Code]),

    (SELECT MyAssemblyID.MyNamespace.DiscretizePrediction([Sales Value]) FROM PredictTimeSeries([Sales Value],5)

    FROM

     [Sales Forecast Time Series]

     

    Note the subselect to apply the discretization to the forecast values. The reason for the subselect is that UDFs cannot handle nested tables as parameters.

    Let us know if you need some help with the UDFs.

    Hope this helps

    Sunday, August 6, 2006 1:35 AM
    Moderator

All replies

  • DMX does not support (yet) the CASE statement, although we consider supporting it in future versions.

    Fore a DMX reference, you could try: http://msdn2.microsoft.com/en-us/library/ms132025.aspx (Data Mining Extensions (DMX) Statement Reference).

    Both problems can be solved with some dedicated UDF (user defined functions) written in C# and deployed on the server as an assembly. The query would then look like:

    SELECT MyAssemblyID.MyNamespace.StateFromArea([Sales Forecast Time Series].[City Code]),

    (SELECT MyAssemblyID.MyNamespace.DiscretizePrediction([Sales Value]) FROM PredictTimeSeries([Sales Value],5)

    FROM

     [Sales Forecast Time Series]

     

    Note the subselect to apply the discretization to the forecast values. The reason for the subselect is that UDFs cannot handle nested tables as parameters.

    Let us know if you need some help with the UDFs.

    Hope this helps

    Sunday, August 6, 2006 1:35 AM
    Moderator
  • Thanks
    Monday, August 7, 2006 3:09 AM