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: #(.
Quote from: Texan29 on 03 Sep 2014 02:59:58 PM
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.