Using SS2000. Here is my query:
select tblTasks.numTaskId, tblFranchise.numFranchiseId AS LicenseNumber,
tblFranchise.numEnterpriseId, tblTasks.numCompanyId,
tblFranchise.varFranchiseName, tblCompany.varCompanyName,
tblTaskDetails.numTaskDetailsId,
tblTaskDetails.numTaskId, tblTaskDetails.dtTaskDate,
tblTaskDetails.numCommunicationType,
tblTaskDetails.numCommunicationTypeResult, tblTaskDetails.numCompanyStatus,
tblTaskDetails.intTStatus,
tblCommunicationType.varCommunicationTypeName,
tblCommunicationTypeResults.varResultName, 'Mailed' AS Item, ''  AS
RedeemDate
from tblTaskDetails
join tblCommunicationTypeResults on
tblTaskDetails.numCommunicationTypeResult=
tblCommunicationTypeResults.numCommunicationTypeResultId
join tblCommunicationType on
tblTaskDetails.numCommunicationType=tblCommunicationType.numCommunicationTypeId
join tblTasks on tblTasks.numTaskId=tblTaskDetails.numTaskId
join tblCompany on tblTasks.numCompanyId=tblCompany.numCompanyId
join tblFranchise on tblTasks.numFranchiseId=tblFranchise.numFranchiseId
where numCommunicationType= 40 and numCommunicationTypeResult in (70, 73,
76, 82, 83, 91)

UNION

select tblTasks.numTaskId, tblFranchise.numFranchiseId AS LicenseNumber,
tblFranchise.numEnterpriseId, tblTasks.numCompanyId,
tblFranchise.varFranchiseName, tblCompany.varCompanyName, 
tblTaskDetails.numTaskDetailsId, tblTaskDetails.numTaskId,
tblTaskDetails.dtTaskDate, tblTaskDetails.numCommunicationType,
tblTaskDetails.numCommunicationTypeResult,
tblTaskDetails.numCompanyStatus, tblTaskDetails.intTStatus,
tblCommunicationType.varCommunicationTypeName,
tblCommunicationTypeResults.varResultName, tblPrograms.varProgramName,
tblRedeem.RedeemDate
from tblTaskDetails
join tblCommunicationTypeResults on
tblTaskDetails.numCommunicationTypeResult=
tblCommunicationTypeResults.numCommunicationTypeResultId
join tblCommunicationType on
tblTaskDetails.numCommunicationType=tblCommunicationType.numCommunicationTypeId
join tblTasks on tblTasks.numTaskId=tblTaskDetails.numTaskId
join tblCompany on tblTasks.numCompanyId=tblCompany.numCompanyId
join tblFranchise on tblTasks.numFranchiseId=tblFranchise.numFranchiseId
join tblPrograms on tblTasks.numProgramId=tblPrograms.numProgramId
join tblRedeem on tblTasks.numCompanyId=tblRedeem.CompanyId
where numCommunicationType= 40 and numCommunicationTypeResult in (70, 73,
76, 82, 83, 91)

The problem is the last column in the select list. In the second part of the
union it is RedeemDate which is a datetime type. The way the query is now in
the first part of the union for RedeemDate '1900-01-01 00:00:00.000' shows
up. In the top part of the union I want this column to show nothing (blank,
null). Is it possible to do this?

Thanks,

Dan D. wrote:
Make sure your datatypes match. Convert RedeemDate in the first SELECT
to a datetime:

....
tblCommunicationTypeResults.varResultName, 'Mailed' AS Item,
CONVERT(DATETIME, NULL)  AS RedeemDate
....

Chris

Blank('') and NULL are distinct.

To have blank value in the resultset, you'd have to cast the datetime value
in the second SELECT statement to a valid character display format using
CONVERT function like:

CONVERT( VARCHAR(30), RedeemDate, 101 )

To have NULL in the resultset, simply set the column expression to NULL
like:

NULL AS "RedeemDate"

They both worked. Thanks to you both.

Show quoteHide quote

NEWSEQUINTIALID()
Slow Stored Procedure Execution
BCP error - "Unexpected EOF encountered" when using native format
Join??????
sp_recompile for user defined functions