union query w/ 7 select statements 1

[hopsing34]

Im trying to create a union query w/ 7 select statements for a report in crd 6 the db is access 97 with a dao connection. the query works in access but not in crystal report the following is the syntax used. <br>
SELECT distinctrow citation_num,lea,toxicologist_name,lab_number,name,name2,add1,add2,city,state,zip from blood_lab,spcl_witnesses where blood_lab.lab_id = spcl_witnesses.lab_id <br>
union <br>
select distinctrow citation_num,lea,null,instrument_num,name,name2,add1,add2,city,state,zip from bac_lab,spcl_witnesses where bac_lab.lab_id = spcl_witnesses.lab_id <br>
union <br>
select distinctrow citation_num,lea,null,batch_number,name,name2,add1,add2,city,state,zip from toxicology_lab,spcl_witnesses where toxicology_lab.lab_id = spcl_witnesses.lab_id <br>
union <br>
select distinctrow citation_num,lea,scientist_name,lab_number,name,name2,add1,add2,city,state,zip from crime_lab,spcl_witnesses where crime_lab.lab_id = spcl_witnesses.lab_id <br>
union <br>
select distinctrow citation_num,lea,scientist_name,lab_number,name,name2,add1,add2,city,state,zip from marijuana_lab,spcl_witnesses where marijuana_lab.lab_id = spcl_witnesses.lab_id <br>
UNION select distinctrow citation_num,lea,null,null,name,null,address,null,cities,state,zip from witness_info <br>
order by citation_num; <br>
<br>
i have tried order by 1 and not using an order clause .I have also tried buiding the query in the sql expert <br>
I just started using crystal reports a month ago. <br>
thanks in advance for the help<br>
<br>

 

[MalcolmW]

The problem likely stems from the fact that Crystal Reports dynamically creates an Order By clause in v7. This Order By clause contains named fields (Order By citation_num), which creates a SQL syntax error if you have a SQL query containing set operators (such as UNION, INTERSECT, EXCEPT). In such circumstances, you must use numbered columns (Order By 1). However, because Crystal is not smart enough to use numbered columns when it creates the Order By clause, you must take another approach.<br>
First of all, build (paste) your SQL statement in the Crystal SQL Designer. The SQL Designer is a distinct program, not some wizard in Crystal Reports, and here you have complete flexibility over the SQL statement, and can make it just the way you want. After you have got it working to your satisfaction, save this as a query file, and exit SQL Designer. Next, using the report expert in Crystal Reports (which is the only way to create reports from query files), select the query file as your data source. Unfortunately, you cannot take your existing report and change the data source from Access or whatever to a query file, so you will have to rebuild the report from scratch. <br>
Note that this problem would not exist if Crystal Reports chose to use numbered columns in the Order By clause, or allowed the user to chose whether numbered columns should be used, or parsed the SQL statement for set operators and made the choice based on that. I would love to see this defect fixed, so please help me by emailing seagate on this. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[hopsing34]

Thanks for the help!! I was trying to create the report using just the report designer component. When I loaded crystal report ver 4.6 it modified the component which allowed me to use the original query. GO FIGURE!! Well I got the report running, now I just have some format <br>
issues to take care of... <br>
thanks again <br>
<br>