[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Oracle Union Statement

I was trying to get some financial data to relate to eachother and after many tries, I thought I would try a union. I've never done one, so I"m a little iffy on how to write it for Oracle. My first query contains 10 fields that I need to have "dummy" fields in the 2nd query and 3 fields in the second query that need to have "dummy" records in the first query. Is using null fields and effective way to do this? Can someone help. Thank you

1st Query:

SELECT  "ACACTIVITY"."ACTIVITY_GRP",
              "ACACTIVITY"."ACTIVITY",
              "ACACTIVITY"."BALANCE_AMOUNT"
              "ACACTGRP"."DESCRIPTION",
              "ACACTIVITY"."DESCRIPTION",
              "ACACCTCAT"."DESCRIPTION",
              "ACACCTCAT"."ACCT_CATEGORY",
              "ACBUDDTL"."BALANCE",
      
Fields that need to be replicated to 2nd query:      
        "ACTRANS"."ACCOUNT", (Number)
        "ACTRANS"."ACCT_UNIT",(String)
        "ACTRANS"."SUB_ACCOUNT", (Number)
        "ACTRANS"."POSTING_DATE", (DateTime)
        "ACTRANS"."R_SYSTEM", (String)
        "ACTRANS"."REFERENCE", (String)
        "ACTRANS"."DESCRIPTION", (String)
        "ACTRANS"."TRAN_AMOUNT", (Number)
        "ACTRANS"."UNITS_AMOUNT", (Number)
        "ACTRANS"."ACT_CURRENCY"(String)

Second Query:

SELECT  "ACACTIVITY"."ACTIVITY_GRP",
      "ACACTIVITY"."ACTIVITY",
      "ACACTIVITY"."BALANCE_AMOUNT"
      "ACACTGRP"."DESCRIPTION",
      "ACACTIVITY"."DESCRIPTION",
      "ACACCTCAT"."DESCRIPTION",
      "ACACCTCAT"."ACCT_CATEGORY",
      "ACBUDDTL"."BALANCE",

  Fields that need to be replicated to 1st query:
      "ACCOMMITX"."ACTIVITY", (String)
      "ACCOMMITX"."ACCT_CATEGORY",(String)
      "ACCOMMITX"."TRAN_AMOUNT", (Number)
0
metalteck
Asked:
metalteck
  • 9
  • 7
  • 4
  • +1
1 Solution
 
awking00Commented:
See attached.
query.txt
0
 
slightwv (䄆 Netminder) Commented:
Not sure I understand exactly what you are thinking a union will do for you but in Oracle to set the 'dummy' columns is pretty easy. Just give them the an alias in the first select so i has a column name and you're good to go.

select 'Hello' col1, null dummy_col from dual
union
select 'Hello', 'Fred' from dual
/
0
 
slightwv (䄆 Netminder) Commented:
caught typing...
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
awking00Commented:
That's usually my problem :-)
0
 
metalteckAuthor Commented:
Guys, I was able to run the sql query succesfully, but it is only pulling information from the first query...
Union-sql.txt
0
 
awking00Commented:
What does the query after the union operator return by itself?
0
 
metalteckAuthor Commented:
The only thing that is displayed are everything in the first query up to col13.
col1-col10 don't show up and neither do any of the fields such as ACCOMMITX.TRAN_AMOUNT.
0
 
awking00Commented:
I just wanted to see what the second query (the one after the union operator) returns by itself, without the first query. Also, can you post the precise query that you submitted?
0
 
metalteckAuthor Commented:
Attached is a screenshot of the 2nd query with the data I'm expecting.
The full query I'm using is in the .txt file.
Subquery.docx
Activity-Union.txt
0
 
metalteckAuthor Commented:
I"m sorry, disregard the attached .txt file.
Here is the code:

SELECT  "ACACTIVITY"."ACTIVITY_GRP",
        "ACACTIVITY"."ACTIVITY",
            "ACACTIVITY"."BALANCE_AMOUNT",
        "ACACTGRP"."DESCRIPTION",
        "ACACTIVITY"."DESCRIPTION",
        "ACACCTCAT"."DESCRIPTION",
        "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",       
        "ACTRANS"."ACCOUNT" AS ACTRANSACCT,
        "ACTRANS"."ACCT_UNIT" AS ACTRANSACCTUNIT,
        "ACTRANS"."SUB_ACCOUNT" AS ACTRANSSUBACT,
        "ACTRANS"."POSTING_DATE" AS ACTRANSPOSTDT,
        "ACTRANS"."R_SYSTEM" as ACTRANSYS,
        "ACTRANS"."REFERENCE" as ACTRANSREF,
        "ACTRANS"."DESCRIPTION" as ACTRANSDESC,
        "ACTRANS"."TRAN_AMOUNT" as ACTRANSAMT,
        "ACTRANS"."UNITS_AMOUNT" as ACTRANSUNIT,
        "ACTRANS"."ACT_CURRENCY" as ACTRANCURR,
        null as col11,
        null as col12,
        null as col13
                 
 FROM   ((("PROD"."ACACTIVITY" "ACACTIVITY" INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP")
      INNER JOIN "PROD"."ACTRANS" "ACTRANS" ON "ACACTIVITY"."ACTIVITY"="ACTRANS"."ACTIVITY")
      INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP"))
      INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACTRANS"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"
Union
SELECT "ACACTIVITY"."ACTIVITY_GRP",
            "ACACTIVITY"."ACTIVITY",
            "ACACTIVITY"."BALANCE_AMOUNT",
            "ACACTGRP"."DESCRIPTION",
            "ACACTIVITY"."DESCRIPTION",
            "ACACCTCAT"."DESCRIPTION",
            "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",
        null as col1,
        null as col2,
        null as col3,
        null as col4,
        null as col5,
        null as col6,
        null as col7,
        null as col8,
        null as col9,
        null as col10,
       "ACCOMMITX"."ACTIVITY",
           "ACCOMMITX"."ACCT_CATEGORY",
           "ACCOMMITX"."TRAN_AMOUNT" AS ACCOMMITXTRANAMT
       
 FROM   ((("PROD"."ACACTIVITY" "ACACTIVITY" LEFT OUTER JOIN "PROD"."ACCOMMITX" "ACCOMMITX" ON "ACACTIVITY"."ACTIVITY"="ACCOMMITX"."ACTIVITY")
      INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP")
      INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP"))
      INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACCOMMITX"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"
0
 
slightwv (䄆 Netminder) Commented:
I believe what awking00 is asking is:  Does the second query, when run by itself, actually return data?
SELECT "ACACTIVITY"."ACTIVITY_GRP",
            "ACACTIVITY"."ACTIVITY", 
            "ACACTIVITY"."BALANCE_AMOUNT",
            "ACACTGRP"."DESCRIPTION", 
            "ACACTIVITY"."DESCRIPTION",
            "ACACCTCAT"."DESCRIPTION", 
            "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",
        null as col1, 
        null as col2,
        null as col3, 
        null as col4, 
        null as col5, 
        null as col6, 
        null as col7,
        null as col8, 
        null as col9,
        null as col10,
       "ACCOMMITX"."ACTIVITY", 
           "ACCOMMITX"."ACCT_CATEGORY", 
           "ACCOMMITX"."TRAN_AMOUNT" AS ACCOMMITXTRANAMT
        
 FROM   ((("PROD"."ACACTIVITY" "ACACTIVITY" LEFT OUTER JOIN "PROD"."ACCOMMITX" "ACCOMMITX" ON "ACACTIVITY"."ACTIVITY"="ACCOMMITX"."ACTIVITY") 
      INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP") 
      INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP")) 
      INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACCOMMITX"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"

Open in new window

0
 
metalteckAuthor Commented:
Yes, if I run the second query by itself, it returns data. I attached the screenshot of the second query and the results.
The problem is that none of the fields from the second query show up when I run the entire query.
0
 
slightwv (䄆 Netminder) Commented:
I can't open the DOCX at home so that will have to wait until tomorrow.

If the top query returns rows and the bottom query returns rows, the union HAS TO return rows.

I think I see what might be causing this.

You only assign column aliases for the TOP query.  These will become the column names.  setting them in the bottom query serves no purpose.


SELECT  "ACACTIVITY"."ACTIVITY_GRP",
        "ACACTIVITY"."ACTIVITY",
            "ACACTIVITY"."BALANCE_AMOUNT",
        "ACACTGRP"."DESCRIPTION",
        "ACACTIVITY"."DESCRIPTION",
        "ACACCTCAT"."DESCRIPTION",
        "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",       
        "ACTRANS"."ACCOUNT" AS ACTRANSACCT,
        "ACTRANS"."ACCT_UNIT" AS ACTRANSACCTUNIT,
        "ACTRANS"."SUB_ACCOUNT" AS ACTRANSSUBACT,
        "ACTRANS"."POSTING_DATE" AS ACTRANSPOSTDT,
        "ACTRANS"."R_SYSTEM" as ACTRANSYS,
        "ACTRANS"."REFERENCE" as ACTRANSREF,
        "ACTRANS"."DESCRIPTION" as ACTRANSDESC,
        "ACTRANS"."TRAN_AMOUNT" as ACTRANSAMT,
        "ACTRANS"."UNITS_AMOUNT" as ACTRANSUNIT,
        "ACTRANS"."ACT_CURRENCY" as ACTRANCURR,
        null as col19,
        null as col20,
        null as col21
 FROM   ((("PROD"."ACACTIVITY" "ACACTIVITY" INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP")
      INNER JOIN "PROD"."ACTRANS" "ACTRANS" ON "ACACTIVITY"."ACTIVITY"="ACTRANS"."ACTIVITY")
      INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP"))
      INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACTRANS"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"
Union
SELECT "ACACTIVITY"."ACTIVITY_GRP",
            "ACACTIVITY"."ACTIVITY",
            "ACACTIVITY"."BALANCE_AMOUNT",
            "ACACTGRP"."DESCRIPTION",
            "ACACTIVITY"."DESCRIPTION",
            "ACACCTCAT"."DESCRIPTION",
            "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
       "ACCOMMITX"."ACTIVITY",
           "ACCOMMITX"."ACCT_CATEGORY",
           "ACCOMMITX"."TRAN_AMOUNT"
 FROM   ((("PROD"."ACACTIVITY" "ACACTIVITY" LEFT OUTER JOIN "PROD"."ACCOMMITX" "ACCOMMITX" ON "ACACTIVITY"."ACTIVITY"="ACCOMMITX"."ACTIVITY")
      INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP")
      INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP"))
      INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACCOMMITX"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"

Open in new window

0
 
metalteckAuthor Commented:
I'm a newbie to unions and think I need to call the fields in the top query, but I don't know how to do that.
0
 
slightwv (䄆 Netminder) Commented:
>> I'm a newbie to unions

We were all newbs once at everything.  Unions aren't that difficult.  The good news is they are basically the same across all database platforms so any documentation that explains it should fit.

Check out:
http://en.wikipedia.org/wiki/Set_operations_(SQL)#UNION_operator

Basically, all the queries in the union (or union all -- no sorting needed) must have the same number of columns and those columns must be the same data type.

The names of the columns in the final resultset are derived from the columns and/or aliases provided in the very first query of the union.

>>I need to call the fields in the top query

I don't know what that means.

0
 
metalteckAuthor Commented:
I"ve been reading on the forums lots of examples of naming each subquery, ex: table1, and then at the begining of the sql statement call the required field; table1.field1.
Don't know if this will resolve my problem or not, but I'm willing to try everything.
0
 
slightwv (䄆 Netminder) Commented:
You only need to fully qualify the table.column combinations when the columns might come from different tables.

When learning, keep it simple:

Given the following:
select 'Hello' topCol1, 'World' topCol2 from dual
union
select 'Goodbye' botCol1, 'World' botCol2 from dual
/


Look at the column names in sql*plus.  They will be called TOPCOL1 and TOPCOL2.

The double quotes you are using force Oracle to be case specific.  For what it's worth I would suggest you not use them.
0
 
metalteckAuthor Commented:
Thanks, I eliminated all the double quotes and have name a field or two in the 2nd subquery. Unfortunately I'm still only pulling information pertaining to the first subquery when I run the union.
Please, when you have a chance, take a look and let me know if you come across any additional ideas I can try.

Thanks for all your help.
0
 
slightwv (䄆 Netminder) Commented:
I don't know how else we can help.  If the queries return results individually they have to do so when "union"ed.

It's the rules.

I can dummy up some tables, insert some sample data and show you that it works as described but I don't think that will help.

Unless I can reproduce your exact tables and data so I can reproduce the problem, there's nothing I can do.

The only think I can think of that might be causing issues is the tool you are using to run the query.

Please try from sql*plus.

Below is a simple test that shows what I think you're trying to do and produce the following output in my sqplplus:
--------------------------
Table dropped.


Table created.


1 row created.


Table dropped.


Table created.


1 row created.


Commit complete.


C C C C C C
- - - - - -
1 2   3 4 5
a b c

2 rows selected.
drop table tab1 purge;
create table tab1 (col1 char(1), col2 char(1), col3 char(1));
insert into tab1 values('a','b','c');

drop table tab2 purge;
create table tab2 (col1 char(1), col2 char(1), col4 char(1),col5 char(1), col6 char(1));
insert into tab2 values('1','2','3','4','5');
commit;

select col1, col2, col3, null col4, null col5, null col6 from tab1
union
select col1, col2, null col3, col4, col5, col6 from tab2
/

Open in new window

0
 
yuchingCommented:
Hi metalteck:
It seem like you just want to display fields from table ACCOMMITX in the query.
Instead of using UNION, you should add in left outer join for the 1st query will do.

SELECT  "ACACTIVITY"."ACTIVITY_GRP", 
        "ACACTIVITY"."ACTIVITY", 
            "ACACTIVITY"."BALANCE_AMOUNT",
        "ACACTGRP"."DESCRIPTION", 
        "ACACTIVITY"."DESCRIPTION", 
        "ACACCTCAT"."DESCRIPTION", 
        "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",       
        "ACTRANS"."ACCOUNT" AS ACTRANSACCT, 
        "ACTRANS"."ACCT_UNIT" AS ACTRANSACCTUNIT, 
        "ACTRANS"."SUB_ACCOUNT" AS ACTRANSSUBACT, 
        "ACTRANS"."POSTING_DATE" AS ACTRANSPOSTDT, 
        "ACTRANS"."R_SYSTEM" as ACTRANSYS, 
        "ACTRANS"."REFERENCE" as ACTRANSREF, 
        "ACTRANS"."DESCRIPTION" as ACTRANSDESC, 
        "ACTRANS"."TRAN_AMOUNT" as ACTRANSAMT, 
        "ACTRANS"."UNITS_AMOUNT" as ACTRANSUNIT, 
        "ACTRANS"."ACT_CURRENCY" as ACTRANCURR, 
        "ACCOMMITX"."ACTIVITY", 
        "ACCOMMITX"."ACCT_CATEGORY", 
        "ACCOMMITX"."TRAN_AMOUNT" AS ACCOMMITXTRANAMT
 FROM   "PROD"."ACACTIVITY" "ACACTIVITY" 
 INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP"
 INNER JOIN "PROD"."ACTRANS" "ACTRANS" ON "ACACTIVITY"."ACTIVITY"="ACTRANS"."ACTIVITY"
 INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP") 
 INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACTRANS"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"
 LEFT OUTER JOIN "PROD"."ACCOMMITX" "ACCOMMITX" ON "ACACTIVITY"."ACTIVITY"="ACCOMMITX"."ACTIVITY"

Open in new window

0
 
metalteckAuthor Commented:
I've tried adding the accommitx table to the first query, but all it does is cause duplication on the report. That's why I was trying the union.
0
 
yuchingCommented:
ok, please try this. It will select the max(acct_category) and sum(Tran_amount) from ACCOMMITX table before left join to the first query.
SELECT  "ACACTIVITY"."ACTIVITY_GRP", 
        "ACACTIVITY"."ACTIVITY", 
            "ACACTIVITY"."BALANCE_AMOUNT",
        "ACACTGRP"."DESCRIPTION", 
        "ACACTIVITY"."DESCRIPTION", 
        "ACACCTCAT"."DESCRIPTION", 
        "ACACCTCAT"."ACCT_CATEGORY",
            "ACBUDDTL"."BALANCE",       
        "ACTRANS"."ACCOUNT" AS ACTRANSACCT, 
        "ACTRANS"."ACCT_UNIT" AS ACTRANSACCTUNIT, 
        "ACTRANS"."SUB_ACCOUNT" AS ACTRANSSUBACT, 
        "ACTRANS"."POSTING_DATE" AS ACTRANSPOSTDT, 
        "ACTRANS"."R_SYSTEM" as ACTRANSYS, 
        "ACTRANS"."REFERENCE" as ACTRANSREF, 
        "ACTRANS"."DESCRIPTION" as ACTRANSDESC, 
        "ACTRANS"."TRAN_AMOUNT" as ACTRANSAMT, 
        "ACTRANS"."UNITS_AMOUNT" as ACTRANSUNIT, 
        "ACTRANS"."ACT_CURRENCY" as ACTRANCURR, 
        "ACCOMMITX"."ACTIVITY", 
        "ACCOMMITX"."ACCT_CATEGORY", 
        "ACCOMMITX"."TRAN_AMOUNT" AS ACCOMMITXTRANAMT
 FROM   "PROD"."ACACTIVITY" "ACACTIVITY" 
 INNER JOIN "PROD"."ACACTGRP" "ACACTGRP" ON "ACACTIVITY"."ACTIVITY_GRP"="ACACTGRP"."ACTIVITY_GRP"
 INNER JOIN "PROD"."ACTRANS" "ACTRANS" ON "ACACTIVITY"."ACTIVITY"="ACTRANS"."ACTIVITY"
 INNER JOIN "PROD"."ACBUDDTL" "ACBUDDTL" ON ("ACACTIVITY"."ACTIVITY"="ACBUDDTL"."ACTIVITY") AND ("ACACTIVITY"."ACTIVITY_GRP"="ACBUDDTL"."ACTIVITY_GRP") 
 INNER JOIN "PROD"."ACACCTCAT" "ACACCTCAT" ON "ACTRANS"."ACCT_CATEGORY"="ACACCTCAT"."ACCT_CATEGORY"
 LEFT OUTER JOIN (
 	Select "ACCOMMITX"."ACTIVITY", 
        Max("ACCOMMITX"."ACCT_CATEGORY") As "ACCT_CATEGORY", 
        Sum("ACCOMMITX"."TRAN_AMOUNT") As "TRAN_AMOUNT"
	From "PROD"."ACCOMMITX" 
	Group By "ACCOMMITX"."ACTIVITY"
 )"ACCOMMITX" ON "ACACTIVITY"."ACTIVITY"="ACCOMMITX"."ACTIVITY"

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now