Concat Null Yields Null Woes..

I have a bunch of views that concat fileds, some of which allow nulls
so I am using the isNull function when joining them. I dont want to
have to do this, I want to set the database so that concat null does
not yield null for all views and procedures.
How do I do this this in EM ?
I read and tried a bunch of stuff on this but nothings worked so far.
Please dont reply telling me to use the concat null yields null
setting - I think I know what needs doing - but I dont know "How" to do
it!
Thanks.Thats because QA uses ODBC connection and by default its set CONCAT....NULL
to ON. and it overrides the database setting that you might have given.
you will have to explicitely state it in the connection
SET CONCAT_NULL_YEILDS_NULL OFF.
And your view is just a query given a name. So it will take the connection
setting. There is no point in setting that flag during view creation. You
will have to set it during access.
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||You can set CONCAT_NULL_YIELDS_NULL at the database level with ALTER
DATABASE or at the server level using the 'user options' of sp_configure.
However, it is unlikely that this will provide the behavior you want because
OLE DB and ODBC APIs explicitly SET CONCAT_NULL_YIELDS_NULL ON when
connecting to provide ANSI standard compliance by default. SET
CONCAT_NULL_YIELDS_NULL OFF needs to be explicitly set by each OLEDB/ODBC
client application using the view.
The best approach to ensure consistent results is to specify ISNULL or
COALESCE in the view expression. One can also argue that concatenation is
better handled in the client application rather that the server side.
Note that CONCAT_NULL_YIELDS_NULL ON is required to take advantage of
features like indexes on computed columns and views.
Hope this helps.
Dan Guzman
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150110841.616205.127710@.c74g2000cwc.googlegroups.com...
>I have a bunch of views that concat fileds, some of which allow nulls
> so I am using the isNull function when joining them. I dont want to
> have to do this, I want to set the database so that concat null does
> not yield null for all views and procedures.
> How do I do this this in EM ?
> I read and tried a bunch of stuff on this but nothings worked so far.
> Please dont reply telling me to use the concat null yields null
> setting - I think I know what needs doing - but I dont know "How" to do
> it!
> Thanks.
>|||Thank you both.
Dan Guzman wrote:
> You can set CONCAT_NULL_YIELDS_NULL at the database level with ALTER
> DATABASE or at the server level using the 'user options' of sp_configure.
> However, it is unlikely that this will provide the behavior you want becau
se
> OLE DB and ODBC APIs explicitly SET CONCAT_NULL_YIELDS_NULL ON when
> connecting to provide ANSI standard compliance by default. SET
> CONCAT_NULL_YIELDS_NULL OFF needs to be explicitly set by each OLEDB/ODBC
> client application using the view.
> The best approach to ensure consistent results is to specify ISNULL or
> COALESCE in the view expression. One can also argue that concatenation is
> better handled in the client application rather that the server side.
> Note that CONCAT_NULL_YIELDS_NULL ON is required to take advantage of
> features like indexes on computed columns and views.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1150110841.616205.127710@.c74g2000cwc.googlegroups.com...|||>> I have a bunch of views that concat fields [sic], some of which allow NULLs so
I am using the ISNULL() function when joining them. I dont want to have to
do this, I want to set the database so that concat NULL does not yield NUL
L for all views and pr
ocedures. <<
Oh, you want to write your own language and not bother with SQL! This
behavior is one of many reasons that columns are not anything like
fields and why I jump on newbies to actually read a book about the
language before they start coding.
Nobody should be so irresponsible as to give you that advice. Your
code would not port, would not work properly, etc. what needs doing
is a bit more education on your part instead of looking for kludges to
save yourself some typing. Also, why aren't you using QA or a code
editor instead of EM?
SQL programmers think of the schema as a whole. The first place to
look is the DDL and the Data Dictionary (which you probably do not
have, if you have that many NULLs). Which of these columns really
should be blanks, empty strings or other defaults and not NULL-able at
all? I will bet most of them, based on two decades of cleaning up
SQL.
You will find that most bad DML are kludges made in response to bad
DDL.|||And I thought the iea of newsgrouops was to help ....
Thanks for your advice, but with respect, this isnt the theory of
doing databases this is databases - real ones for real businesses with
real (small) budgets and real tight deadlines and working pretty well
considering ...

> Oh, you want to write your own language and not bother with SQL!
Not particularly, but yeah Im happy to break the rules now and then,
for good reasons of course, its worked so far nayway...
columns are not anything like fields
True, but that doesnt really get in the way of business, we focus on
the things that matter...

>why I jump on newbies to actually read a book about the language before they start
coding
Books suit some learning types, not mine, took the test, I know how I
learn best..
Also, why aren't you using QA or a code editor instead of EM?
EM is easier and faster (for me) to use.

>Data Dictionary (which you probably do not have, if you have that many NULLs).[/col
or]
Yeah I follow agile principles - My code is the documentation!
>Which of these columns really should be blanks, empty strings or other defaults and
not NULL-able at all?
Maybe a few should be empty strings instread

> You will find that most bad DML are kludges made in response to bad DDL.
You could say the same about requirements and design
You could say the same about feasibiliy and requirements
Sometimes it pays to jump in and build the damn system and deal with
the tweaks afterwards,

>based on two decades of cleaning up SQL.
No amount of experience gives anyone right to post unhelpfull,
self-indulgent replies ...
--CELKO-- wrote:
procedures. <<
> Oh, you want to write your own language and not bother with SQL! This
> behavior is one of many reasons that columns are not anything like
> fields and why I jump on newbies to actually read a book about the
> language before they start coding.
>
> Nobody should be so irresponsible as to give you that advice. Your
> code would not port, would not work properly, etc. what needs doing
> is a bit more education on your part instead of looking for kludges to
> save yourself some typing. Also, why aren't you using QA or a code
> editor instead of EM?
> SQL programmers think of the schema as a whole. The first place to
> look is the DDL and the Data Dictionary (which you probably do not
> have, if you have that many NULLs). Which of these columns really
> should be blanks, empty strings or other defaults and not NULL-able at
> all? I will bet most of them, based on two decades of cleaning up
> SQL.
> You will find that most bad DML are kludges made in response to bad
> DDL.|||>> And I thought the idea of newsgroups was to help ... <<
But there is an assumption that someone wants real help and not just a
kludge.
Wasn' t that what the accountants at Enron said -- this isn't about the
theory of accounting, etc. Why do you think a small budgets means
that you cannot do things right? The guy with the small budget is the
one who can least afford errors.
Correctness and maintainability are far more important than raw coding
speed. Hey, if it does not have to be right, the answer is always 42!
But later on you admit that you do not like to read things, so how do
you know what the rules are to make an informed decision about breaking
them? And somehow, you are always find a "good reason", such as your
dislike of typing in this thread :) And it probably has not worked,
but you do not know it yet.
Yes, it does matter. Do you go to a doctor who does not know the basic
concepts of his trade? Forget the fancy stuff, just the basic
concepts. It takes SIX years to become a Union Journeyman Carpenter in
New York State, but a kid with a few ws of MS Certificate cramming
thinks he is a programmer.
You might want to look at this:
http://www.apa.org/journals/psp/psp7761121.html
It is an article in the Journal of Personality and Social Psychology
entitled "Unskilled and Unaware of It: How Difficulties in Recognizing
One's Own Incompetence Lead to Inflated Self-Assessments"; the premise
is that people tend to hold overly favorable views of their abilities
in many social and intellectual domains. The authors suggest that this
overestimation occurs, in part, because people who are unskilled in
these domains suffer a dual burden: Not only do these people reach
erroneous conclusions and make unfortunate choices, but their
incompetence robs them of the metacognitive ability to realize it.
They tested some of the skills needed for programming.
The idea of learning, say, Normalization by "Trial & Error", looking at
training films or reading & writing a few thousand line of code seems a
bit .. expensive :)
This is the excuse that lazy and incompetent programmers use to cover
the fact that they do not know what they are doing. Does your code
include the external sources that provide data to your system? Do you
have a DFD for the system as a whole? I hope your end users are all SQL
programmers with about 15-20 years experience as well as domain experts
who can read that code when they need to use that system.
Do you know why you need a data dictionary? Do you even know what it
is? This is like an apprenice carpenter saying that "My house wiring
is the blueprint!"
One of my god-children and her husband have a small consulting company
in Atlanta. They are currently bidding on a job where the former
"Agile/XP/Cowboy Coder" decided that documentation is "just sooo anal!"
and was busy too refactoring code to bother with it. After a year of
"agile principles", the project is a failure. In fact, most Agile/XP
projects fail.
Since you do not llike to read, you might not have heard of the C3
(Chrysler Comprehensive Compensation) project. It was where XP began.
It was a payroll system that was to get around Y2K problems. It
started in 1996 and was cancelled in 2000 February. It had 1/3 of the
requirements originally promised and was so unusable that it was
scrapped by the end of 2000 and disappeared by 2002.
Maybe. Let's look at the specs and the data dictionary. Wait, yoyu
don't have those things.
You could say the same about requirements and design <<
We do say that! In fact, we measured the cost of errors in
requirements and design in the 1970's and later. This is one of the
major points of software engineering. In the classic DoD-2167 model
the total addition cost increased by about 10x at each step.
30 years of research disagrees with you. Check out the SEI, DoD, IBM,
any university research project on TCO of software, Barry Boehm and the
aerospace industry, etc. Did you see the piece on PBS on the levies
in New Orleans tonight? The Army Corp of Engineers They also built the
damn system and dealt with the tweaks afterwards.
What part of free speech and open forums confuses you? And when you
get over that hump of invincible igorance, you might find that you got
a lot of good advice instead of a kludge.