Union with Parameters

[kit2002]

How can I create a UNION between 2 tables AND use the Parameters in the Where clause. I cannot use VIEWS, Crystal Queries, Temp Tables or Stored Procedures as this report is run within GoldMine and GM will only accept straight SQL.
Here is what I want to accomplish;

Select A.id, A.Name, A.State, A.Status, A.Zip from Table A
INNER JOIN Country on A.countryID = country.ID where
A.State = state.Parameter and
A.Zip = zip.Parameter and
A.Status = status.Parameter
UNION
Select B.id, B.Name, B.State, B.Status, B.Zip from Table B
INNER JOIN Country on B.countryID = country.ID wherewhere
B.State = state.Parameter and
B.Zip = zip.Parameter and
B.Status = status.Parameter

I am using CR 8.5

 

[IdoMillet]

Can you clarify "straight SQL"? Wouldn't Goldmine accept a UNION query?

What version of Crystal? With CR 9 you can use a Command.

Note: Looks like you should be using UNION ALL instead of UNION. This would speed up things considerably!

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Since you're using CR 8.5, create the report using just the A table.

Go into the Database->Show SQL Query and copy the SQL

Type the word Union and paste in the SQL, then modify it so that the second section uses the B table.

Now you can add in the Report->Edit Selection Formula->Record:

{table.state} = {?stateparm}
and
{table.zip} = {?zipparm}
and
{table.status} = {?Statusparm}

Unfortunately you won't get the parms passed in the SQL, but it should work.

-k

 

[kit2002]

Thanks everyone for your inputs. My hurdle is trying to get the second part of the UNION to use the params. Crystal Reports will automatically stick in the WHERE clause with the RECORD SELECTION criteria, but I am trying to get Crystal Reports to generate a similar WHERE clause in the second Select Statement too.

 

[synapsevampire]

Not likely...

And remember it's not that you can't use Views, etc., it's that you've elected not to.

I've had the distinct displeasure to work with Goldmine before, and I've gone so far as to link Goldmine tables to an Access database so I could get some work done (this was in the Xbase days).

Crystal isn't going to pass the SQL to the database with manually edited SQL.

-k

 

[kit2002]

Actually I did.
We have Goldmine Sales & Marketing with GoldSync Ver 6.0
We use an ODBC connection to a SQL Server for the intranet users and Dbase file structure for Remote users. How this all sync's and ties in is out of my league and I leave it to the GM Admin to handle that.
Anyway, I created one report using a View (ODBC & SQL Server) and when I tried to run it from within Goldmine it gave me this error 'An unsupported table is used in the report' same when I used Stored Procedure. I even tried on a long short using an ADO connection... same result.