Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Oracle SQL Union Query

tymwarntymwarn Member Posts: 1
Basically I'd like to have this statement only return where
sum(DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.PREENCUMBRANCE_TOTAL_AMT)-
DATAMART_ADMIN.DW_DETAILED_SPENDING.DOLLAR_AMT is greater than or less than 0. Right now I all record and then I use a Business objects to do the calculation, but I have to scan all records on screen to see which record(s) do not equal 0, if any at all. I also have a join between 3 tables with the same problem. Anybody got an idea how to do a calculation at the of this SQL statement or am I going about this the wrong way..possibly using SQL*plus?



(
SELECT
to_char(DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.BUDGET_FISCAL_YEAR),
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.APPR_ORG_CODE,
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.FUND_CODE,
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.AGENCY_CODE,
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.APPR_UNIT_CODE,
chr(00),
sum(DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.PREENCUMBRANCE_TOTAL_AMT),
0.00
FROM
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY
WHERE
(
to_char(DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.BUDGET_FISCAL_YEAR) = @variable('Enter APPR BFY:')
AND SNAPSHOT_CALENDAR_MONTH_CODE = @variable('Enter Snapshot Calendar Month:')
)
GROUP BY
to_char(DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.BUDGET_FISCAL_YEAR),
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.APPR_ORG_CODE,
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.FUND_CODE,
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.AGENCY_CODE,
DATAMART_ADMIN.DW_BUDGET_APPR_HISTORY.APPR_UNIT_CODE,
chr(00),
0.00
UNION ALL
(
SELECT
to_char(DATAMART_ADMIN.DW_DETAILED_SPENDING.BUDGET_FISCAL_YEAR),
DATAMART_ADMIN.DW_DETAILED_SPENDING.APPR_ORG_CODE,
DATAMART_ADMIN.DW_DETAILED_SPENDING.FUND_CODE,
DATAMART_ADMIN.DW_DETAILED_SPENDING.AGENCY_CODE,
DATAMART_ADMIN.DW_DETAILED_SPENDING.APPR_UNIT_CODE,
DATAMART_ADMIN.DW_DETAILED_SPENDING.OBJECT_CODE,
0.00,
DATAMART_ADMIN.DW_DETAILED_SPENDING.DOLLAR_AMT
FROM
DATAMART_ADMIN.DW_DETAILED_SPENDING
WHERE
(
DATAMART_ADMIN.DW_DETAILED_SPENDING.BUDGET_FISCAL_YEAR = @variable('Enter GENL BFY:')
AND DATAMART_ADMIN.DW_DETAILED_SPENDING.ACCOUNT_TYPE_CODE IN ('20')
AND DATAMART_ADMIN.DW_DETAILED_SPENDING.ACCEPT_DATE <= @variable('Enter Month End DDMMYYYY:')
)
)
)


Sign In or Register to comment.