Clarity_Wellington

NSQL Suggestion Solicited

Discussion created by Clarity_Wellington on Apr 19, 2013
Latest reply on Apr 19, 2013 by Clarity_Wellington
Hello All,
This is my first post. I have the following NSQL. It was written to use two dimensions - Project and mertic type. I have another report where we are fetching from same data from same set of tables. Now there is a mismatch in the total of these two. So my question is there a way I can write a single dimension query and still display two dimentional data.

Thanks for your help in advance.

SELECT
@select:dim:user_def:implied:project:tbl.code || tbl.name:UniqueID@,
@select:dim_prop:user_def:implied:project:tbl.code:ProjectCode@,
@select:dim_prop:user_def:implied:project:tbl.name:ProjectName@,
@select:dim_prop:user_def:implied:project:tbl.ProjectStart:ProjectStart@,
@select:dim_prop:user_def:implied:project:tbl.ProjectFinish:ProjectFinish@,
@select:dim_prop:user_def:implied:project:getobsleveldesc(tbl.id,3):Obs3@,
@select:dim_prop:user_def:implied:project:getobsleveldesc(tbl.id,4):Obs4@,
@select:dim_prop:user_def:implied:project:getobsleveldesc(tbl.id,5):Obs5@,
@select:dim_prop:user_def:implied:project:ispc_group(tbl.id):ispc_group@,
@select:dim_prop:user_def:implied:project:ispc_group_l2(tbl.id):ispc_group_l2@,
@select:dim_prop:user_def:implied:project:ispc_category(tbl.id):ispc_category@,
@select:dim_prop:user_def:implied:project:tbl.projectrank:ProjectRank@,
@select:dim_prop:user_def:implied:project:tbl.manager:Manager@,
@select:dim_prop:user_def:implied:project:tbl.managercode:ManagerCode@,
@select:dim_prop:user_def:implied:project:tbl.tier:Tier@,
@select:dim_prop:user_def:implied:project:tbl.InvestmentImperative:InvestmentImperative@,
@select:dim_prop:user_def:implied:project:getStatusrptCurrcostExpl(tbl.id):CurrYearCostExpl@,
@select:dim:user_def:implied:type:tbl.metric_type:MetricTyp@,
@select:metric:user_def:implied:sum(tbl.Internal):Internal@,
@select:metric:user_def:implied:sum(tbl.FixedPrice):FixedPrice@,
@select:metric:user_def:implied:sum(tbl.Consultants):Consultants@,
@select:metric:user_def:implied:sum(tbl.Expenses):Expenses@,
@select:metric:user_def:implied:sum(tbl.Total):Total@
FROM
(
SELECT
i.id,
i.code,
i.name,
to_char(trunc(i.schedule_start), 'mm/dd/yyyy') ProjectStart,
to_char(trunc(i.schedule_finish), 'mm/dd/yyyy') ProjectFinish,
o.wmc_project_rank projectrank,
r.full_name manager,
r.unique_name managercode,
getAttributeValue('WMC_TIER', o.wmc_tier) tier,
getlookupattribname('WMC_INV_IMPRTV', o.wmc_inv_imprtv) InvestmentImperative,
' APPROVED' metric_type,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'LIN'), 0)), 0) Internal,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'LFP'), 0)), 0) FixedPrice,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'LTM'), 0)), 0) Consultants,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'EXP'), 0)), 0) Expenses,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'ALL'), 0)), 0) Total
FROM
inv_investments i,
odf_ca_project o,
srm_resources r
WHERE
i.id = o.id
and i.is_active = 1
and reportproject(i.id) = 1
and getobsleveldesc(i.id, 3) <> 'Construction'
and getobsleveldesc(i.id, 3) <> 'Finance Projects1'
and i.manager_id = r.user_id
and trunc (i.schedule_finish) >= trunc (sysdate, 'year')

union

SELECT
i.id,
i.code,
i.name,
to_char(trunc(i.schedule_start), 'mm/dd/yyyy') ProjectStart,
to_char(trunc(i.schedule_finish), 'mm/dd/yyyy') ProjectFinish,
o.wmc_project_rank projectrank,
r.full_name manager,
r.unique_name managercode,
getAttributeValue('WMC_TIER', o.wmc_tier) tier,
getlookupattribname('WMC_INV_IMPRTV', o.wmc_inv_imprtv) InvestmentImperative,
' ACTUAL' metric_type,
nvl (prjActualAnn(i.id, 'LIN'), 0) Internal,
nvl (prjActualAnn(i.id, 'LFP'), 0) FixedPrice,
nvl (prjActualAnn(i.id, 'LTM'), 0) Consultants,
nvl (prjActualAnn(i.id, 'EXP'), 0) Expenses,
nvl (prjActualAnn(i.id, 'ALL'), 0) Total
FROM
inv_investments i,
odf_ca_project o,
srm_resources r
WHERE
i.id = o.id
and i.is_active = 1
and reportproject(i.id) = 1
and i.manager_id = r.user_id
and trunc (i.schedule_finish) >= trunc (sysdate, 'year')
and getobsleveldesc(i.id, 3)
not in ('Construction','Finance Projects1')

union

SELECT
i.id,
i.code,
i.name,
to_char(trunc(i.schedule_start), 'mm/dd/yyyy') ProjectStart,
to_char(trunc(i.schedule_finish), 'mm/dd/yyyy') ProjectFinish,
o.wmc_project_rank projectrank,
r.full_name manager,
r.unique_name managercode,
getAttributeValue('WMC_TIER', o.wmc_tier) tier,
getlookupattribname('WMC_INV_IMPRTV', o.wmc_inv_imprtv) InvestmentImperative,
' ETC' metric_type,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LIN')), 0) Internal,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LFP')), 0) FixedPrice,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LTM')), 0) Consultants,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'EXP')), 0) Expenses,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'ALL')), 0) Total
FROM
inv_investments i,
odf_ca_project o,
srm_resources r
WHERE
i.id = o.id
and i.is_active = 1
and reportproject(i.id) = 1
and i.manager_id = r.user_id
and trunc (i.schedule_finish) >= trunc (sysdate, 'year')
and getobsleveldesc(i.id, 3) <> 'Construction'
and getobsleveldesc(i.id, 3) <> 'Finance Projects1'

union

SELECT
i.id,
i.code,
i.name,
to_char(trunc(i.schedule_start), 'mm/dd/yyyy') ProjectStart,
to_char(trunc(i.schedule_finish), 'mm/dd/yyyy') ProjectFinish,
o.wmc_project_rank projectrank,
r.full_name manager,
r.unique_name managercode,
getAttributeValue('WMC_TIER', o.wmc_tier) tier,
getlookupattribname('WMC_INV_IMPRTV', o.wmc_inv_imprtv) InvestmentImperative,
' EAC' metric_type,
nvl (prjactualann (i.id, 'LIN'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LIN')), 0) Internal,
nvl (prjactualann (i.id, 'LFP'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LFP')), 0) FixedPrice,
nvl (prjactualann (i.id, 'LTM'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LTM')), 0) Consultants,
nvl (prjactualann (i.id, 'EXP'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'EXP')), 0) Expenses,
nvl (prjactualann (i.id, 'ALL'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'ALL')), 0) Total
FROM
inv_investments i,
odf_ca_project o,
srm_resources r
WHERE
i.id = o.id
and i.is_active = 1
and reportproject(i.id) = 1
and i.manager_id = r.user_id
and trunc (i.schedule_finish) >= trunc (sysdate, 'year')
and getobsleveldesc(i.id, 3) <> 'Construction'
and getobsleveldesc(i.id, 3) <> 'Finance Projects1'

union

SELECT
i.id,
i.code,
i.name,
to_char(trunc(i.schedule_start), 'mm/dd/yyyy') ProjectStart,
to_char(trunc(i.schedule_finish), 'mm/dd/yyyy') ProjectFinish,
o.wmc_project_rank projectrank,
r.full_name manager,
r.unique_name managercode,
getAttributeValue('WMC_TIER', o.wmc_tier) tier,
getlookupattribname('WMC_INV_IMPRTV', o.wmc_inv_imprtv) InvestmentImperative,
' VARIANCE' metric_type,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'LIN'), 0)), 0) -
(nvl (prjactualann (i.id, 'LIN'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LIN')), 0)) Internal,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'LFP'), 0)), 0) -
(nvl (prjactualann (i.id, 'LFP'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LFP')), 0)) FixedPrice,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'LTM'), 0)), 0) -
(nvl (prjactualann (i.id, 'LTM'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'LTM')), 0)) Consultants,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'EXP'), 0)), 0) -
(nvl (prjactualann (i.id, 'EXP'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'EXP')), 0)) Expenses,
nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'), nvl (prjapprovedann (i.id, 'ALL'), 0)), 0) -
(nvl (prjactualann (i.id, 'ALL'), 0) + nvl (currencyexchange (i.currency_code, 'USD', trunc (sysdate, 'year'),prjetcann (i.id, 'ALL')), 0)) Total
FROM
inv_investments i,
odf_ca_project o,
srm_resources r
WHERE
i.id = o.id
and i.is_active = 1
and reportproject(i.id) = 1
and i.manager_id = r.user_id
and trunc (i.schedule_finish) >= trunc (sysdate, 'year')
and getobsleveldesc(i.id, 3) <> 'Construction'
and getobsleveldesc(i.id, 3) <> 'Finance Projects1'

) tbl
where @FILTER@
group by
tbl.code || tbl.name,
tbl.code,
tbl.name,
tbl.ProjectStart,
tbl.ProjectFinish,
getobsleveldesc(tbl.id,3),
getobsleveldesc(tbl.id,4),
getobsleveldesc(tbl.id,5),
ispc_group(tbl.id),
ispc_group_l2(tbl.id),
ispc_category(tbl.id),
tbl.projectrank,
tbl.manager,
tbl.managercode,
tbl.tier,
tbl.InvestmentImperative,
getStatusrptCurrcostExpl(tbl.id),
tbl.metric_type
having @having_filter@

Regards,
Abhishek.

Outcomes