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:')
)
)
)


Comments

  • Justin BibJustin Bib USAMember Posts: 0

    _________ // http://forcoder.org // free ebooks and video tutorials about < Swift, PHP, Java, Visual Basic .NET, PL/SQL, C++, Delphi, Scratch, Go, C, Assembly, R, Objective-C, Ruby, Visual Basic, Perl, JavaScript, MATLAB, Python, C# Kotlin, Rust, FoxPro, Hack, Dart, Ada, Clojure, Transact-SQL, Logo, Lisp, VBScript, Crystal, LabVIEW, Erlang, COBOL, Prolog, ABAP, SAS, Alice, Scala, Lua, Awk, D, Fortran, Julia, F#, Scheme, Apex, ML, Bash /> ___

Sign In or Register to comment.