Union join Noob needs help

[krpurcel]

Hi, I am trying to create a union query in Crystal for the first time. I’ve read numerous previous posts on union queries so I am dangerous now! They helped, but raised as many new questions for me as they answered due to my lack of knowledge.
I am using CR X1 R2. I have an Oracle DB that started tracking ticket data in June of this year. I have an excel spreadsheet with ticket data from Jan 1, 2009 to June when the Oracle DB came online. I need to do reporting on these tickets, rolling up the data for the entire year from these two data sources.
I have a working Crystal Report that pulls the data from the Oracle DB. The SQL for the report is:
Oracle DB SQL Statement


SELECT "PROBSUMMARYM1"."ASSIGNMENT", "PROBSUMMARYM1"."OPEN_TIME",
"PROBSUMMARYM1"."CLOSE_TIME", "PROBSUMMARYM1"."NUMBERPRGN",
"PROBSUMMARYM1"."CLOSED_BY", "PROBSUMMARYM1"."FLAG",
"PROBSUMMARYM1"."CATEGORY", "PROBSUMMARYM1"."CAUSE_CODE",
"PROBSUMMARYM1"."RESOLUTION_CODE"

FROM "SC"."PROBSUMMARYM1" "PROBSUMMARYM1"

WHERE ("PROBSUMMARYM1"."ASSIGNMENT"='EXECUTIVE SUPPORT MAC' OR
"PROBSUMMARYM1"."ASSIGNMENT"='HELP POINT MAC/PROJECT' OR
"PROBSUMMARYM1"."ASSIGNMENT"='SIMI VALLEY MAC' OR
"PROBSUMMARYM1"."ASSIGNMENT"='SOFTWARE DISTRIBUTION') AND
"PROBSUMMARYM1"."FLAG"='f' AND ("PROBSUMMARYM1"."CLOSE_TIME">=TO_DATE
('01-01-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"PROBSUMMARYM1"."CLOSE_TIME"<TO_DATE ('27-07-2009 00:00:00', 'DD-MM-YYYY
HH24:MI:SS'))

ORDER BY "PROBSUMMARYM1"."ASSIGNMENT", "PROBSUMMARYM1"."CLOSED_BY",
"PROBSUMMARYM1"."CLOSE_TIME"



The spreadsheet columns contain the same headers that are being pulled from the Oracle DB. I then created a Crystal Report that mirrors the Oracle DB report. The SQL for that report is:


Excel Spreadsheet SQL

SELECT `Sheet1_`.`ASSIGNMENT`, `Sheet1_`.`OPEN_TIME`,
`Sheet1_`.`CLOSE_TIME`,`Sheet1_`. `NUMBERPRGN`, `Sheet1_`.`CLOSED_BY`,
`Sheet1_`.`FLAG`, `Sheet1_`.`CATEGORY`, `Sheet1_`.`CAUSE_CODE`,
`Sheet1_`.`RESOLUTION_CODE`

FROM `Sheet1$` `Sheet1_`

WHERE (`Sheet1_`.`ASSIGNMENT`='EXECUTIVE SUPPORT MAC' OR
`Sheet1_`.`ASSIGNMENT`='HELP POINT MAC/PROJECT' OR
`Sheet1_`.`ASSIGNMENT`='SIMI VALLEY MAC' OR
`Sheet1_`.`ASSIGNMENT`='SOFTWARE DISTRIBUTION') AND `Sheet1_`.`FLAG`='f'
AND (`Sheet1_`.`CLOSE_TIME`>=#2009-01-01 00:00:00# AND
`Sheet1_`.`CLOSE_TIME`<#2009-07-27 00:00:00#)

ORDER BY `Sheet1_`.`ASSIGNMENT`, `Sheet1_`.`CLOSED_BY`,
`Sheet1_`.`CLOSE_TIME`





My understanding is I need to put these together using the UNION command, i.e.
SQL Query 1
UNION
SQL Query 2
Once combined, they are to be pasted in the ADD COMMAND section of the database expert. I have not been able to make this work, and am confused about the proper way to do this.
I can take either of the SQL Queries listed above, plug them into the ADD COMMAND section of their relevant Crystal Report connection and they are accepted . The command is created and shows up in the right hand window. It also shows up under links.
As soon as I add UNION and the second SQL query I get errors. If I paste the combined union query to the ADD COMMAND section of the Oracle DB I get an error saying : “ORA-00911: invalid character” followed by a second error message saying “Failed to execute SQL Statement. OCI Call: OCIStmtExecute[Databse Vendor Code: 911]
If I paste the combined union query to the ADD COMMAND section of the Excel spreadsheet connection I get an error saying “Datacde Connector Error: ‘DAO Error Code: 0xd7a. Syntax error in query. Incomplete query clause.
Please clarify the following for me.
1. Is the following syntax correct in the add command section? No parantheses around the SQL queries?
SQL Query 1
UNION
SQL Query 2

2. The posts I have read refer to pasting the union query into the ADD COMMAND section of the Database Expert. However in the database expert there are separate connections for the db and the Excel spreadsheet. Do I paste the query to the ADD COMMAND section for BOTH connections? Do I paste the connection appropriate SQL query to the connection appropriate ADD COMMAND box? If so, where do I put the word UNION? And if the SQL only gets copied to one section, what determines which one it goes to?

3. Once the SQL query is added to the ADD COMMAND section, do I clear out the data in the record selection of the Crystal Report? I was under the impression from other posts that the ADD COMMAND query will take preference, and the record selection criteria normally used is ignored.

Sorry for the length of the post. If I have omitted any crucial info let me know and I will add it. Thanks for taking the time to read and try and help.

 

[krpurcel]

Sorry about this post. Not sure why it runs off the page and part is not visible.

 

[krpurcel]

Ok, I continue to read other things. Now I get the impresssion that in order to do a union join using the ADD COMMAND section of the Database Expert, the tables to be joined must exist within the same data connection. Can someone confirm that?

If that is the case I cannot use ADD COMMAND to tie the spreadsheet & the database table together because they are in different connections. Is there another way to do this using Crystal?

Thanks!

 

[Turkbear]

Hi,
That is correct - Unless you have your DBA ( or you, if you can) create a database link to the Excel data using Oracle's heterogeneous connection ability..If that is done, the datasource would be the Oracle instance and a view within that instance would link to the Excel data.

Here is the link to Oracle's docs on that topic ( it is for v9 but still applies, I imagine)

http://download.oracle.com/docs/cd/B10501_01/server.920/a96544/toc.htm

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[krpurcel]

Thanks Turkbear! Not going to be able to get a link between the Oracle DB and Excel, so will have to rethink this.

Thanks for the confirmation and the link!