Skip to Content
author's profile photo Former Member
Former Member

Help with SELECT Statement!!

Hi Experts,

I am trying to fetch the YEAR property from Category dimension with Select instruction and using the fetched year to get the TimeIDs for that year from Time dimension via following syntax:

*SELECT(%VARYR%,"[YEAR]","CATEGORY","ID='%CATEGORY_SET%'")
*SELECT(%TIMEIDS%,"[ID]","TIME","CALC='N' AND PERIOD<>'INPUT' AND YEAR IN ('%VARYR%')")

The logic is validating fine but Problem is that 1statement is not working (means not fetching the Year), I have checked by putting "AOP" instead of %CATEGORY_SET% in 1st statement and then logic worked OK for the TimeIds fetched for the AOP category year.

Please let me know what can be the problem with first statement as it is not fetching the YEAR.

Thanks & Regards,

Rohit

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Nov 21, 2011 at 08:54 PM

    Hi Rohit,

    I see 2 issues here.

    You can't use the variable %VARYR% in the second select statement because both *SELECT statements are compiled at the same time and %VARYR% will be empty at the time when the logic is run.

    Try this instead:

    *SELECT(%TIMEIDS%,"[ID]","TIME","[CALC]='N' AND [PERIOD]='INPUT' AND [YEAR] IN (SELECT [YEAR] FROM mbrCATEGORY WHERE ID='%CATEGORY_SET%')")

    Try running the following query in SQL Server Management Studio for debug purposes:

    SELECT [ID] FROM mbrTIME WHERE [CALC]='N' AND [PERIOD]='INPUT' AND [YEAR] IN (SELECT [YEAR] FROM mbrCATEGORY WHERE ID='AOP')

    Also, make sure your DM package that are running this from is referencing the LGF file and not the LGX file. This is due to the fact that the %CATEGORY_SET% variable must be run with an uncompiled logic file (LGF).

    Thanks,

    John

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Rohit,

      I believe you have everything covered.

      As always please perform some end to end integration testing to confirm the logic is working as designed and review the logic logs carefully to confirm your scope (selection) is correct.

      The default logic log file called DebugLogic.log gets created in your tempfiles folder located at:

      ..\Webfolders\<AppsetName>\<ApplicationName>\PrivatePublications\<UserName>\TempFiles\

      Thanks,

      John

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.