concatenate nulls in SQL server

I have a problem with a view in MS SQL Server 2000. The View concatenates 3 fields (prefix, partnumber, suffix) using hte "+" operator and one or more of these fields may sometimes be null. The problem is that a null value in any of the three fields causes the concatenation to return null even if there are valid values in on or both of the other fields. I thought I might be able to work around this by creating a view containing CASE statements to render the null values as zero length strings, which concatenate properly (e.g., "SELECT CASE WHEN PREFIX IS NULL THEN '' ELSE PREFIX END 'prefix2' FROM [table_name]"). But SQL server will not let me save a view containing a CASE statement. Anybody know how to resolve this problem?

By the way, I also tried to use UNION views to work around this but SQL server 2000 will not let me save views with UNION sataments even though it runs them properly when views with UNION statements that were created in SQL server 7.0 are imported. What's up with the inability to save a view just because it can't be rendered in the gui pane of the query builder??select isnull(prefix,'')+isnull(partnumber,'')+isnull(suf fix,'') from your table|||I know you can use a function in a view, so perhaps you can write a function that takes as input the three values and internally builds the string using your case logic (or, alternatively, using ISNULL, as in:

SET @.outputstring = ISNULL(@.value1,'') + ISNULL(value2,'') + ISNULL(value3,'')

then just use the function within the view's select statement.|||you can use the function coalesce.

select coalesce(prefix, '') + coalesce(partnumber, '') + coalesce(suffix, '')
from your_table|||Thanks for the help. The isnull concatenation works so my (current) problem is resolved. I still don't know why I can no longer save views with UNION statements in them in SQL 2000...|||Why are you creating queries in the GUI query builder? You mean the one in Enterprise Manager? It generates crappy code. Plus I don't think you can save the resulting queries as views directly anyway. It's not designed for creating objects, but for viewing data. You can paste the code it creates into Query Analyzer to create your Union query, but clean it up first.|||I've often wondered about that, I have done some stuff in views (ordering, is one that leaps to mind) that the enterprise manager screams about, but when I do it through SQL Analyzer, it allows it, and the resulting view SEEMS to work flawlessly...what's UP with that? Is Uncle Billy just trying to save us from ourselves or something?|||You can't ORDER a view unless you include the TOP clause, so it may have screamed about that.

The GUI "designer" in Enterprise Manager really is only for viewing data. And you have to be carefull even then, 'cause under some circumstances you can accidently change data in the resultset and end up modifying the source data. I generally avoid EM's GUI.

composite operators

I was reading some sql server document, I though I see
the "composite operator" somewhere. But now I could not
find it anywhere. Is there such a term in SQL server? or
is there anything so that I can send to multiple
operations when a job succeed?
Thanks.
PeterHaven't tried it but pretty sure you could specify an exchange group as
the email for an operator.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can use SQLDMO for this. Add a project reference to the Microsoft
SQLDMO COM object and invoke the ListAvailableSQLServers method like the
example below.
SQLDMO.Application sqlDmoApplication = new SQLDMO.Application();
SQLDMO.NameList serverList;
serverList = sqlDmoApplication.ListAvailableSQLServers();
foreach(string serverName in serverList)
{
comboBoxServerList.Items.Add(serverName);
}
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%23xfE36xSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> Such a term does not exist in SQL server. When you define an operator,
it
> could be a single mailbox or a distribution list (a group of
mailboxes). You
> can xp_sendmail to either one.
>
> Richard
>
> "Peter" <peter.zhang@.noetix.com> wrote in message
> news:052301c3481f$a58049a0$a401280a@.phx.gbl...
> > I was reading some sql server document, I though I see
> > the "composite operator" somewhere. But now I could not
> > find it anywhere. Is there such a term in SQL server? or
> > is there anything so that I can send to multiple
> > operations when a job succeed?
> >
> > Thanks.
> >
> > Peter
>

Comparison operator in Select list

I want to create a column alias to represent the comparison of two
columns (ie a boolean result of True or False). A simple example is:
Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance

but I get an error 'Incorrect syntax near >'

Books online states that the select_list can contain column_name or
expression

An expression is a column name, constant, function, any combination of
column names, constants and functions connected by an operator

> is a binary operator

So why do I get this error.

Incidentally, if I use an arithmetic operator such as +, there is no
problem.A gremlin put the erroneous > in front of From :-(

The Select statement was intended to be:

Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance

Jim|||There it is again. I can't win on this one. Please disregard the > in
front of From

Jim

Jim Devenish wrote:
> A gremlin put the erroneous > in front of From :-(
> The Select statement was intended to be:
> Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
> >From VehicleFinance
>
> Jim|||Hello, Jim

Try something like this:
SELECT VehicleFinanceID,
CASE WHEN SalePrice > PurchasePrice THEN 1 ELSE 0 END As isProfit
FROM VehicleFinance

SQL Server does not support a boolean data type, so you must use some
convention to represent boolean values (for example: 1=True, 0=False or
'Yes'=True, 'No'=False, etc).

A boolean expression can only be used in an IF statement, a WHILE
statement, a CASE WHEN expression, a WHERE/HAVING clause, etc.

Razvan|||Thanks Razvan. That is what I had done but it seemed cumbersone for
something that is essentially simple.

At least I know what I can and cannot do, now.

Jim

Razvan Socol wrote:
> Hello, Jim
> Try something like this:
> SELECT VehicleFinanceID,
> CASE WHEN SalePrice > PurchasePrice THEN 1 ELSE 0 END As isProfit
> FROM VehicleFinance
> SQL Server does not support a boolean data type, so you must use some
> convention to represent boolean values (for example: 1=True, 0=False or
> 'Yes'=True, 'No'=False, etc).
> A boolean expression can only be used in an IF statement, a WHILE
> statement, a CASE WHEN expression, a WHERE/HAVING clause, etc.
> Razvan|||Jim Devenish (internet.shopping@.foobox.com) writes:
> There it is again. I can't win on this one. Please disregard the > in
> front of From

The reason this happens is because of the mbox format used by old
Unix mailers. This format is also used by some newsreaders for archives
I believe. In this format, a new message always starts with "From ".
Since a preceding message always ends with two or three newlines,
there is some safety precaution in the format, but not waterproof.

For this reason, Unix mailers that uses this format adds a > before
"From" when it appears first on a line. I've noticed that Google news,
that I see that you are using, also does this to be, I guess, a good
net citizen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

comparision filter on a float field

I'm trying to do a comparision filter on a float field and I keep getting the
error
> Expression =Fields!float_cal.Value
> Operator <= > Value =0
I keep getting the error 'check data type returned'. I tried
> Expression =CInt(Fields!float_cal.Value )
but this did not work either.
Any ideasHi Marg,
if it is a float field you should convert it to double, not integer.
Try Expression = Cdbl(Fields!float_cal.Value ).
If that does not work you could even try cdbl(Expression) =Cdbl(Fields!float_cal.Value ).
Best regards,
Stefan
"marg" wrote:
> I'm trying to do a comparision filter on a float field and I keep getting the
> error
> > Expression =Fields!float_cal.Value
> > Operator <=> > Value =0
> I keep getting the error 'check data type returned'. I tried
> > Expression =CInt(Fields!float_cal.Value )
> but this did not work either.
> Any ideas
>