Diff "where" conditons in "UNION" qry

[nijeesh]

Hi All,
I'm designing a Crystal report with a UNION query. The query looks like this,

SELECT x1
FROM x
WHERE <condition1>
UNION
SELECT y1
FROM Y
WHERE <condition2>

say,
<condition1> would be -> x.x1 between p1 and p2
<condition2> would be -> y.y1 between p1 and p2

where p1 and p2 are parameter fields.

How do i realise this in Crystal reports 8.5?
I tried adding <condition1> and <condition2> in &quot;Record selection formula&quot; but they are added with an &quot;AND&quot; condition (like &quot;<condition1> AND <condtion2>&quotwhich is not my requirement.
Any help, would really be appreciated !!

Thanks & Regards,
Nijeesh.

 

[IdoMillet]

Use Crystal SQL Designer to create the query.

That way, you are free to write it exactly as you
want it.

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

http://www.MilletSoftware.com

 

[nijeesh]

Thanks for the quick reply Ido.
I don't have Crystal SQL Designer with me.

Is it possible to do this without Crystal SQL Designer?

Thanks & Regards,
Nijeesh.

 

[synapsevampire]

I don't think that you can use differing parameters in the Union in CR 8.5.

SQL is dynamically constructed, and since the report doesn't recognize anything beyond the Union, you can't do this.

The best bet is to create a View on the database, or take the subreport approach.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[IdoMillet]

It's not clear from your description why you can't simply use a single condition. After the UNION is executed, the result set only has one field (with the SAME name) and you can compare it to your range.

If this doesn't solve it for you, provide us with more detail about how you are creating the query.

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

http://www.MilletSoftware.com

 

[nijeesh]

The requirement is,
I have two tables from which the data is to be selected.
say table1 and table2. It's true that the resultset is the same, but how do i incorporate them in one single condition
after the &quot;UNION&quot; is executed. Any specific settings to be done?
or
Can i just write the query as
SELECT x.x1 &quot;field1&quot;, x.x2 &quot;field2&quot;
FROM x
UNION
SELECT y.y1 &quot;field1&quot;, y.y2 &quot;field2&quot;
FROM y
order by field1, field2

and put in &quot;Edit -> Record Selection Formula&quot;
{field1} in {p1} to {p2}

Plz clarify.

Thanks & Regards,
Nijeesh.

 

[IdoMillet]

Exactly )

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

http://www.MilletSoftware.com

 

[nijeesh]

Hi Ido,
I'm not able to give aliases(like &quot;field1&quot in the main query. The Crystal Reports automatically overwrites the query and removes the aliases. Also, I'm not able to mention the alias name in the Record selection formula. It requires a db column name/formula field/ parameter field. But my db column names are different, so I cannot mention one single column name.

Am I doing something wrong?

Thanks & Regards,
Nijeesh.

 

[IdoMillet]

The 2nd part of the UNION should not have alias names because everything is driven by the names in the 1st part.

In any case, my recommendation is to create the query as a View (Query in mS Access) in the database or to use Crystal SQL Designer. Modifying the query in Crystal itself into a much more complex query is a recipe for frustration...

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

http://www.MilletSoftware.com