AnsweredAssumed Answered

NSQL @SELECT Param Help

Question asked by MikeGillespie on May 18, 2010
Latest reply on Jun 2, 2010 by Dave
I am having trouble with an @select parameter and a case statement in NSQL. I have provided the working SQL and my non-working NSQL below. Basically I don't feel I have the syntax proper for the param and am not sure how to get the param to work in the case statement. Any insights are greatly appreciated. Also if what I am doing cannot be accomplished in the select of NSQL I welcome suggestions on how to modify the query to move everything to the From. The idea behind this query is to pull project Forecast and Actual information by transcalss from views and allow the users to select the year and month. The year drives which year information will be displayed for. The month drives what month to start pulling forecasts $ instead of actual $. So if the month entered is May - January through May will have actuals and the rest of the year will have forecast $ represented in the month. The PM are trying to get a better idea on what their actuals for the year will be. The current query stops at Feburary and needs to be built out once working.

Thanks in advance,
Mike


WORKING SQL:
SELECT ac.project_id||ac.transclass unique_id,ac.project_id id,ac.project code,users.last_name manager_name,
inv.manager_id manager_id,inv.name name,tc.description transclass, tc.transclass,
SUM (Actual_yr) Actual_yr,
decode(1,sign(:month-1+1), SUM(JanA), Sum(JanF)) Jan,
decode(1,sign(:month-2+1), SUM(FebA), Sum(FebF)) Feb,
sum(ac.forecast_yr) Forecast_yr,
Case :month
When '0' then (nvl(sum(JanF),0)+nvl(sum(FebF),0))
When '1' then (nvl(sum(JanA),0)+nvl(sum(FebF),0))
When '2' then (nvl(sum(JanA),0)+nvl(sum(FebA),0))
end Forecast_adjusted

FROM (SELECT project_id,project,transclass,actual Actual_yr,NULL JanA,NULL JanF, NULL FebA, Null FebF, Null Forecast_yr
FROM mv_csx_pac_actual_transclass
WHERE period_type = 'ANNUALLY' AND yr_mo = :year
UNION
SELECT project_id,project,transclass,NULL Actual_yr,actual JanA, Null JanF, NULL FebA, Null FebF, NULL Forecast_yr
FROM mv_csx_pac_actual_transclass
WHERE yr_mo = concat(:year,'01')
UNION
SELECT project_id,project,transclass,NULL Actual_yr,NULL JanA, Null JanF, actual FebA, Null FebF, NULL Forecast_yr
FROM mv_csx_pac_actual_transclass
WHERE yr_mo = concat(:year,'02')
UNION
SELECT project_id,project,transclass,NULL Actual_yr, Null JanA, forecast JanF, NULL FebA, Null FebF, NULL Forecast_yr
FROM mv_csx_pac_forecast_transclass
WHERE yr_mo = concat(:year,'01')
UNION
SELECT project_id,project,transclass,NULL Actual_yr,NULL JanA, Null JanF, Null FebA, Forecast FebF, NULL Forecast_yr
FROM mv_csx_pac_forecast_transclass
WHERE yr_mo = concat(:year,'02')
UNION
SELECT project_id,project,transclass,NULL Actual_yr,NULL JanA, Null JanF, Null FebA, NULL FebF, sum(forecast) Forecast_yr
FROM mv_csx_pac_forecast_transclass
WHERE period_type = 'MONTHLY' AND yr_mo like :year||'%'
Group by project_id, project,transclass
) ac
INNER JOIN transclass tc ON ac.transclass = tc.transclass
INNER JOIN inv_investments inv ON inv.id = ac.project_id
INNER JOIN cmn_sec_users users ON users.id = inv.manager_id
--where ac.project_id in (5007499,5007418,5010854)

GROUP BY
ac.project_id||ac.transclass,
project_id,
project,
tc.description,
tc.transclass,
users.last_name,
inv.manager_id,
inv.name

order by ac.project, tc.description

NON-WORKING NSQL - Errors out at the @SELECT:Params highlighted by the ####### signs.

Select
@SELECT:DIM:USER_DEF:IMPLIED:project:ac.project_id||ac.transclass:unique_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:ac.project_id:id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:ac.project:code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:users.last_name:manager_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:inv.manager_id:manager_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:inv.name:name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:tc.description:transclass@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:tc.transclass:tcshort@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:SUM (ac.Actual_yr):Actual_yr@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:decode(1,sign(@select:param:user_def:integer:month:month@-1+1), SUM(JanA), Sum(JanF)):Jan@,
########### @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:decode(1,sign(@select:param:user_def:integer:month:month@-2+1), SUM(FebA), Sum(FebF)):Feb@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:sum(ac.forecast_yr):Forecast_yr@,
########### @SELECT:METRIC:USER_DEF:IMPLIED:Case @select:param:user_def:string:month@
When '0' then (nvl(sum(ac.JanF),0)+nvl(sum(ac.FebF),0))
When '1' then (nvl(sum(ac.JanA),0)+nvl(sum(ac.FebF),0))
When '2' then (nvl(sum(ac.JanA),0)+nvl(sum(ac.FebA),0))
end:Forecast_adjusted@

FROM (SELECT project_id,project,transclass,actual Actual_yr,NULL JanA,NULL JanF, NULL FebA, Null FebF, Null Forecast_yr
FROM mv_csx_pac_actual_transclass
WHERE period_type = 'ANNUALLY' AND yr_mo = @where:param:user_def:string:year@
UNION
SELECT project_id,project,transclass,NULL Actual_yr,actual JanA, Null JanF, NULL FebA, Null FebF, NULL Forecast_yr
FROM mv_csx_pac_actual_transclass
WHERE yr_mo = concat(@where:param:user_def:string:year@,'01')
UNION
SELECT project_id,project,transclass,NULL Actual_yr,NULL JanA, Null JanF, actual FebA, Null FebF, NULL Forecast_yr
FROM mv_csx_pac_actual_transclass
WHERE yr_mo = concat(@where:param:user_def:string:year@,'02')
UNION
SELECT project_id,project,transclass,NULL Actual_yr, Null JanA, forecast JanF, NULL FebA, Null FebF, NULL Forecast_yr
FROM mv_csx_pac_forecast_transclass
WHERE yr_mo = concat(@where:param:user_def:string:year@,'01')
UNION
SELECT project_id,project,transclass,NULL Actual_yr,NULL JanA, Null JanF, Null FebA, Forecast FebF, NULL Forecast_yr
FROM mv_csx_pac_forecast_transclass
WHERE yr_mo = concat(@where:param:user_def:string:year@,'02')
UNION
SELECT project_id,project,transclass,NULL Actual_yr,NULL JanA, Null JanF, Null FebA, NULL FebF, sum(forecast) Forecast_yr
FROM mv_csx_pac_forecast_transclass
WHERE period_type = 'MONTHLY' AND yr_mo like @where:param:user_def:string:year@||'%'
Group by project_id, project,transclass
) ac
INNER JOIN transclass tc ON ac.transclass = tc.transclass
INNER JOIN inv_investments inv ON inv.id = ac.project_id
INNER JOIN cmn_sec_users users ON users.id = inv.manager_id

and @filter@

GROUP BY
ac.project_id||ac.transclass,
project_id,
project,
tc.description,
tc.transclass,
users.last_name,
inv.manager_id,
inv.name

having @having_filter@

Outcomes