Prompting for a value in SQL of Query Subject in Framework Manager

[Texan29]

Within the framework manager model, I'm trying to prompt the user for two values (lower salary cap limits) that change annually without actually displaying salary information to the user.  I've tried the following SQL to build a required prompt into the model, but I'm just beating my head up against the wall.  Can anyone tell me how to properly accomplish this? What I've been trying is below.

select   distinct
   Ninja.EMPLOYEE_ID,
   (CASE
      WHEN SUM(Ninja.ANNUAL_SALARY) > #(prompt(?NAMEOFPROMPT?)#  --First prompt that doesn't work
      THEN 'YES'
      ELSE 'NO'
      END) NHI_Cap,
   (CASE
      WHEN SUM(Ninja.ANNUAL_SALARY) > 200000 --This will also be a prompt
      THEN 'YES'
      ELSE 'NO'
      END) CPRIT_Cap
from
(Select   distinct
   TinyNinja.EMPLOYEE_ID,
   TinyNinja.ANNUAL_SALARY
   FROM
   (Select distinct
   TTB_CURRENT_NBAJOBS_MV.EMPLOYEE_ID,
   TTB_CURRENT_NBAJOBS_MV.CHART_OF_ACCOUNTS_JOBS,
   TTB_CURRENT_NBAJOBS_MV.POSITION_CONTRACT_TYPE,
   TTB_CURRENT_NBAJOBS_MV.ANNUAL_SALARY
From
   [TT_ODS_BUDGET2].TTB_CURRENT_NBAJOBS_MV as TTB_CURRENT_NBAJOBS_MV
Where
   TTB_CURRENT_NBAJOBS_MV.POSITION_CONTRACT_TYPE in ('P','S')
and
   TTB_CURRENT_NBAJOBS_MV.CHART_OF_ACCOUNTS_JOBS in ('H','E')
) as TinyNinja
) as Ninja
GROUP BY
   Ninja.EMPLOYEE_ID

Here's the error I'm getting with this setup: QE-DEF-0260 Parsing error before or near position: 81 of: "select distinct
Ninja.EMPLOYEE_ID,(CASE WHEN SUM(Ninja.ANNUAL_SALARY) > #("QE-DEF-0299 Expanding: #( from position: 78, to position: 81, text fragment: #(.

[MFGF]

Within the framework manager model, I'm trying to prompt the user for two values (lower salary cap limits) that change annually without actually displaying salary information to the user.  I've tried the following SQL to build a required prompt into the model, but I'm just beating my head up against the wall.  Can anyone tell me how to properly accomplish this? What I've been trying is below.

select   distinct
   Ninja.EMPLOYEE_ID,
   (CASE
      WHEN SUM(Ninja.ANNUAL_SALARY) > #(prompt(?NAMEOFPROMPT?)#  --First prompt that doesn't work
      THEN 'YES'
      ELSE 'NO'
      END) NHI_Cap,
   (CASE
      WHEN SUM(Ninja.ANNUAL_SALARY) > 200000 --This will also be a prompt
      THEN 'YES'
      ELSE 'NO'
      END) CPRIT_Cap
from
(Select   distinct
   TinyNinja.EMPLOYEE_ID,
   TinyNinja.ANNUAL_SALARY
   FROM
   (Select distinct
   TTB_CURRENT_NBAJOBS_MV.EMPLOYEE_ID,
   TTB_CURRENT_NBAJOBS_MV.CHART_OF_ACCOUNTS_JOBS,
   TTB_CURRENT_NBAJOBS_MV.POSITION_CONTRACT_TYPE,
   TTB_CURRENT_NBAJOBS_MV.ANNUAL_SALARY
From
   [TT_ODS_BUDGET2].TTB_CURRENT_NBAJOBS_MV as TTB_CURRENT_NBAJOBS_MV
Where
   TTB_CURRENT_NBAJOBS_MV.POSITION_CONTRACT_TYPE in ('P','S')
and
   TTB_CURRENT_NBAJOBS_MV.CHART_OF_ACCOUNTS_JOBS in ('H','E')
) as TinyNinja
) as Ninja
GROUP BY
   Ninja.EMPLOYEE_ID

Here's the error I'm getting with this setup: QE-DEF-0260 Parsing error before or near position: 81 of: "select distinct
Ninja.EMPLOYEE_ID,(CASE WHEN SUM(Ninja.ANNUAL_SALARY) > #("QE-DEF-0299 Expanding: #( from position: 78, to position: 81, text fragment: #(.

Hi,

When you use a prompt() function within a macro, you don't include a parameter in question marks - the first argument of the prompt function (in single quotes) is the name of the parameter it sets. You should probably also provide a data type and a default value too. If you don't provide a data type, the type is assumed to be string (which obviously isn't what you need here).

eg

#prompt('Your Starting Salary Parameter','integer','0')#

Cheers!

MF.