Union or Union All?

[bluraz]

I am using Crystal XI and am trying to redo a report by writing a SQL statement and using the Command feature. I am fairly new to doing this. The biggest problem I am having is when I am trying to re-work reports that have several sub-reports within the main. The reason I have so many sub-reports included is because I am trying to pull data from the same database, but the various calculations I am doing require the data to be pulled from different date fields or different type of transactions within the database. As I was scanning this site, I found several suggestions to use "Union" or "Union All". I tried this but continue to get error messages. "Syntax error or access violation"

Any advice would be greatly appreciated.

Here is the code I am currently working with...

  SELECT 
	WMTRANSACTIONDETAIL.QUANTITYACTUAL, 
	WMTRANSACTIONDP.PROFILECODE, 
	WMTRANSACTIONHEADER.STATUS, 
	WMTRANSACTIONHEADER.ACTUALRECEIVEDSHIPPEDDATETIME, 
	WMTRANSACTIONHEADER.STAGE, 
	WMTRANSACTIONHEADER.TRANSACTIONTYPE, 
	WMTRANSACTIONHEADER.CREATIONDATETIME, 
	WMTRANSACTIONHEADER.TRANSACTIONNUMBER,
	WMTRANSACTIONDETAIL.NETWEIGHTQUANTITY,
	WMTRANSACTIONHEADER.DELIVERYPICKUPDEPARTDATETIME,
	IDMASTER_WAREHOUSE.IDENTITYID,
	IMLOCATION.LOCATIONCODE

 FROM   (((WMTRANSACTIONHEADER WITH (NOLOCK)
	LEFT OUTER JOIN IMLOCATION ON 
	WMTRANSACTIONHEADER.P_TRANSPORTATIONLOCATION=IMLOCATION.KEYSEQUENCE)
	LEFT OUTER JOIN WMTRANSACTIONDETAIL WITH (NOLOCK) ON 
	WMTRANSACTIONHEADER.KEYSEQUENCE=WMTRANSACTIONDETAIL.P_TRANSACTIONHEADER) 
	INNER JOIN WMTRANSACTIONDP WITH (NOLOCK) ON 
	WMTRANSACTIONHEADER.P_PROFILE=WMTRANSACTIONDP.KEYSEQUENCE) 
	INNER JOIN IDMASTER IDMASTER_WAREHOUSE WITH (NOLOCK) 
	ON WMTRANSACTIONHEADER.P_TRANSPORTATIONWAREHOUSE=IDMASTER_WAREHOUSE.KEYSEQUENCE
 WHERE  
	(WMTRANSACTIONHEADER.ACTUALRECEIVEDSHIPPEDDATETIME 
	between {?1 -Start_Date} and {?2 -End_Date}) and
	{WMTRANSACTIONHEADER.STAGE} = 'C' and
	{WMTRANSACTIONHEADER.STATUS} = 'L' and
	{WMTRANSACTIONHEADER.TRANSACTIONTYPE} = 'S' and
	{IDMASTER_WAREHOUSE.IDENTITYID} = 'BE'

 UNION ALL

 SELECT 
	WMTRANSACTIONHEADER.TRANSACTIONNUMBER, 
	WMTRANSACTIONHEADER.TRANSACTIONTYPE, 
	WMTRANSACTIONHEADER.CREATIONDATETIME, 
	IDMASTER_WAREHOUSE.IDENTITYID

 FROM   WMTRANSACTIONHEADER WITH (NOLOCK)
 	INNER JOIN IDMASTER IDMASTER_WAREHOUSE WITH (NOLOCK) ON 
	WMTRANSACTIONHEADER.P_TRANSPORTATIONWAREHOUSE=IDMASTER_WAREHOUSE.KEYSEQUENCE
 WHERE  
	 (WMTRANSACTIONHEADER.CREATIONDATETIME 
	between {?1 -Start_Date} and {?2 -End_Date})
 	AND WMTRANSACTIONHEADER.TRANSACTIONTYPE = 'R'
 	AND IDMASTER_WAREHOUSE.IDENTITYID = 'BE'

 

[IanWaterman]

When you use Union queries they must both return the same number of columns. Also columns in the two queries must be the same data type

Union
Will bring back distinct data, ie any duplicated datasets will be eliminated.

Union All
brings both datasets back irrespective of duplications.

Ian

 

[IanWaterman]

Also you can not use crystal parameters inside the SQL statement. You will have to use them in the select statement as usual.

Ian

 

[bluraz]

So, from what you are saying Union is not what I need here? When I tried to add the second Select statement / query as a subreport and then linked them by the Start and End date parameters, the report made me input the date range twice, once for the main report and once for the subreport.

I have not had problems with setting up the date parameters within my sql statement with just a single report. Is this why I am getting the two different date prompts when trying to link the subreport?

 

[IanWaterman]

You may be able to get around problem by linking the parameters from main report to subreport.

In linking window, add parameters from main report into right hand window.

In bottom left drop down, find corresponding parameter from sub report and link.

Ian

 

[lbass]

Ian,

You can use parameters within a union query within the command screen.

-LB

 

[IanWaterman]

LB

Thanks- learn something new every day. I have been adding them to select statement, I assume embedding parameters within the Command should speed them up.

Will have to do some tests. Do not use commands that much as I usuallly have full access to database and and build views.

Ian

 

[lbass]

Yes, creating them within the command will result in a speedier command. If you add them in the main report in the record selection area, the selection criteria are processed locally instead of on the server.

-LB