how could i default a value in a multi union query if the query didn't have value to return??

[Tracy]

how could i default a value in a multi-unions query if the query didn't have
value to return??

 

[dbahooker]

uh with a function?

select EmployeeID, FirstName, Lastname
FROm Employees
UNION
Select -1 * CustomerID, NZ(FirstName, 'UNKNOWN'), LastName
FROM Employees

 

[Albert D.Kallal]

Often, if this is a report, then use a nz() expression in the text box on
the report

so for example, we place a text box called myproductpef

in the data source, we place the following expression

=(nz([ProductPref],"no pref")

Note that the text box control on the report must have a different name then
any existing field. However, as you can see, if the result is null, then a
substitute value will be shown..

So, in effect, I am saying don't do it in the query, but in the report
writer.

hopefully, this query is for a report!!

You can also use the nz() function in the query builder and actually create
a new collum...such as....

myprefs: (nz([ProductPref]),"No pref")

 

[dbahooker]

i just prefer using coalesce or isnull or something on the db side of
the equation.. i wish ADP was taken more seriously!!

much better to shape data on the db server; than do it in multiple
places.. i mean-- what's going to happen when you need to rewrite this
as a crystal report?

it would be much BETTER long term to do stuff like this on the db side
instead of on the reporting side. im just curious why u would do it on
the report side??

-aaron