SOS:Problem with unions!!!

[mazhar]

Hi Malcolm,

I have a query which has 4 selects in it joined with 3 unions. One select fetches buy side values and second one fetches sell side values and 3rd and 4th selects' fetch same buy and sell values but from different tables.
The above query is in Oracle. I need to implement it in CR4.2 with Access. The query in Oracle makes null the fields from sell side when it is selecting buy side and vice versa.

It has become quite painful to solve this. Can you suggest me an approach to solve this.

Thanks

 

[MalcolmW]

I'm kinda confused whether this is a SQL problem in Oracle, a SQL problem with Access, or writing a SQL statement in Crystal with unions. How far have you got with this?

 

[mazhar]

Hi Malcolm,

I need to write a SQL statement in CR.

I will be getting all values for different order numbers.
The problem is:
I wrote buy side columns in the first select at the time of report creation. Then I linked the tables. After previewing the report I opened 'Show SQL Query' and manually wrote union and in the second select I put sell side columns in the same position as buy side columns in the first select.

Is this O.k.? I also need to put sell values along side the buy values.

Sorry for troubling you,

Thanks

 

[MalcolmW]

There are a couple of ways to do this, one of which I'm not sure works in CR 4.2 (I know it doesn't work in v5 and above).
1) Use the Crystal SQL Query tool to build an ODBC SQL query
2) Use an undocumented method that stops working in v5 and above, using the Show SQL Query window.
Method 2 is kinda weird - what you have to do is defeat the attempts by Crystal to control the SQL statement. You start by adding one table to the report, and putting in the fields that need in the report. Because you are using unions, lets say you will need 2 number fields and 1 character field. Pick any three fields in the table that match that criteria and put them in the report. Do not add the other tables.
Go to Show SQL Query, and change the WHERE clause to
WHERE 0 = 1. So this table will not return any rows, all it will do is return the database structure you will use in the report.
Then, in the Show SQL Query window, start adding the unions.
ie

SELECT numeric1, numeric2, text3
FROM anytable 
WHERE 0 = 1
UNION
SELECT SUM(numeric1), numeric2, text3
from anothertable
WHERE whatever
GROUP BY numeric2, text3
UNION
SELECT SUM(numeric1), numeric2, text3
from yetanothertable
WHERE whatever
GROUP BY numeric2, text3
ORDER BY 2 DESC, 3 ASC

Note that the ORDER BY must use numbered columns, not named columns, as with standard SQL syntax for UNIONS.
Crystal will only edit the stuff in the first two lines above when you save the query - normally it controls the SELECT and FROM clauses, but as you can see, this approach effectively circumvents that (which is the benefit).
If you have a complex query, cut and paste it into a text box in the report, and suppress it. That way if you accidently hit the reset query button, you will have something to work with.
I did many dozens of reports with this method - I left the company before they upgraded from v4, so I imagine they had fun dealing with all of those reports that no longer would work. So keep in mind that the second method is an obsolete technique, and will not work with current versions of Crystal.

 

[mazhar]

Thanks very much Malcolm!

I will try and get back to you.

Thanks once again.