Concatenate problem.

Using SQL Server 2000 and trying to generate sql:
sp_password null, 'password', 'name'
selecting from sysxlogins but cannot get past error msg 'Invalid operator
for data type. Operator equals add, type equals nvarchar. I have tried
convert and cast and am aware of precedence order but to no avail. This
should be simple I thought.
Any help would be appreciated.
--
Tim - DBAHi
You example is exactly the same as the example in BOL. Conversion from
varchar to a sysname should be implicit. Therefore it is probably something
else that is giving the error message such as an unescaped apostrophy.
You could try adding an exec in front of each procedure call and printing
out the statements you are executing so that you can run them in Query
Analyser.
John
"Tim" wrote:
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Are you going to show us the query you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:49B44438-7868-4B95-8B6C-54D9B6FE6AE5@.microsoft.com...
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Here is basic query minus quotes and spaces etc.
select password + name from sysxlogins
Tim - DBA
"John Bell" wrote:
> Hi
> You example is exactly the same as the example in BOL. Conversion from
> varchar to a sysname should be implicit. Therefore it is probably something
> else that is giving the error message such as an unescaped apostrophy.
> You could try adding an exec in front of each procedure call and printing
> out the statements you are executing so that you can run them in Query
> Analyser.
> John
>
> "Tim" wrote:
> > Using SQL Server 2000 and trying to generate sql:
> > sp_password null, 'password', 'name'
> > selecting from sysxlogins but cannot get past error msg 'Invalid operator
> > for data type. Operator equals add, type equals nvarchar. I have tried
> > convert and cast and am aware of precedence order but to no avail. This
> > should be simple I thought.
> > Any help would be appreciated.
> >
> > --
> > Tim - DBA|||Hi
Try the following (untested)
DECLARE @.cmd nvarchar(200), @.ParmDefinition nvarchar(200)
DECLARE @.password sysname, @.newpassword sysname, @.name sysname
SET @.ParmDefinition = N'@.pwd sysname, @.newpwd sysname, @.loginnm sysname'
SET @.cmd = 'sp_password @.old = @.pwd, @.new = @.newpwd, @.loginname = @.loginnm'
SET @.newpassword = 'unsecure'
SELECT @.password = password, @.name = name
FROM sysxlogins
WHERE name = 'mylogin'
EXEC sp_executesql @.stmt = @.cmd , @.params = @.ParmDefinition, @.pwd =@.password, @.newpwd = @.newpassword, @.loginnm = @.name
If you want to do multiple rows from sysclogins you will need to use a
cursor and call sp_executesql for each iteration.
John
"Tim" wrote:
> Here is basic query minus quotes and spaces etc.
> select password + name from sysxlogins
>
> --
> Tim - DBA
>
> "John Bell" wrote:
> > Hi
> >
> > You example is exactly the same as the example in BOL. Conversion from
> > varchar to a sysname should be implicit. Therefore it is probably something
> > else that is giving the error message such as an unescaped apostrophy.
> >
> > You could try adding an exec in front of each procedure call and printing
> > out the statements you are executing so that you can run them in Query
> > Analyser.
> >
> > John
> >
> >
> >
> > "Tim" wrote:
> >
> > > Using SQL Server 2000 and trying to generate sql:
> > > sp_password null, 'password', 'name'
> > > selecting from sysxlogins but cannot get past error msg 'Invalid operator
> > > for data type. Operator equals add, type equals nvarchar. I have tried
> > > convert and cast and am aware of precedence order but to no avail. This
> > > should be simple I thought.
> > > Any help would be appreciated.
> > >
> > > --
> > > Tim - DBA

Concatenate problem.

Using SQL Server 2000 and trying to generate sql:
sp_password null, 'password', 'name'
selecting from sysxlogins but cannot get past error msg 'Invalid operator
for data type. Operator equals add, type equals nvarchar. I have tried
convert and cast and am aware of precedence order but to no avail. This
should be simple I thought.
Any help would be appreciated.
Tim - DBAAre you going to show us the query you were using?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:49B44438-7868-4B95-8B6C-54D9B6FE6AE5@.microsoft.com...
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Hi
You example is exactly the same as the example in BOL. Conversion from
varchar to a sysname should be implicit. Therefore it is probably something
else that is giving the error message such as an unescaped apostrophy.
You could try adding an exec in front of each procedure call and printing
out the statements you are executing so that you can run them in Query
Analyser.
John
"Tim" wrote:

> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Here is basic query minus quotes and spaces etc.
select password + name from sysxlogins
Tim - DBA
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You example is exactly the same as the example in BOL. Conversion from
> varchar to a sysname should be implicit. Therefore it is probably somethin
g
> else that is giving the error message such as an unescaped apostrophy.
> You could try adding an exec in front of each procedure call and printing
> out the statements you are executing so that you can run them in Query
> Analyser.
> John
>
> "Tim" wrote:
>|||Hi
Try the following (untested)
DECLARE @.cmd nvarchar(200), @.ParmDefinition nvarchar(200)
DECLARE @.password sysname, @.newpassword sysname, @.name sysname
SET @.ParmDefinition = N'@.pwd sysname, @.newpwd sysname, @.loginnm sysname'
SET @.cmd = 'sp_password @.old = @.pwd, @.new = @.newpwd, @.loginname = @.loginnm'
SET @.newpassword = 'unsecure'
SELECT @.password = password, @.name = name
FROM sysxlogins
WHERE name = 'mylogin'
EXEC sp_executesql @.stmt = @.cmd , @.params = @.ParmDefinition, @.pwd =
@.password, @.newpwd = @.newpassword, @.loginnm = @.name
If you want to do multiple rows from sysclogins you will need to use a
cursor and call sp_executesql for each iteration.
John
"Tim" wrote:
[vbcol=seagreen]
> Here is basic query minus quotes and spaces etc.
> select password + name from sysxlogins
>
> --
> Tim - DBA
>
> "John Bell" wrote:
>sqlsql

concatenate and NULL

I use the command:
exec sp_dboption 'SAP_SAS','concat null yields null','false'
to set the result of a concat (+) to the string and not to null. It works in
the Query Analyzer, but not in the enterprise manager. SQL Server and Agent
are restarted.
Any idea ?
Joia,
You can also SET CONCAT_NULL_YIELDS_NULL ON/OFF which means that each
connection can control its own behavior. EM (& QA, too) issue several SET
commands to make the environment into the one that they want. So, I suspect
that EM is setting this on for you.
If you want to see the list if settings (which I do not remember off the top
of my head) fire up sql profiler to trace TSQL, then connect with EM and see
the list of settings.
The standard OLE DB / ODBC connection also has several settings hard-coded
into it.
Russell Fields
"joia" <joia@.discussions.microsoft.com> wrote in message
news:B732E540-DD1A-45B5-A8FB-A28CE67F1A4D@.microsoft.com...
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works
in
> the Query Analyzer, but not in the enterprise manager. SQL Server and
Agent
> are restarted.
> Any idea ?
|||joia,
This is session setting, and is set at runtime using SET
CONCAT_NULL_YIELDS_NULL {ON|OFF}. sp_dboption, 'dbname','concat null
yields null' is only provided as a default if the client connection does
not specify it. Both QA and EM, set this and override the default value
when you connect.
So, if you use QA, it will only work if you go to
Tools->Options->Connection Properties. Then untick Set
concat_null_yields_null. Then, every connection you open (regardless of
the default database setting, this option will be off.
I don't think you can set this in EM, I couldn't find it anyway, so I
might be wrong. By default it seems that EM sets this option ON when you
create a new connection to a server from EM as shown in this Profiler trace:
Audit Login
-- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
joia wrote:
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works in
> the Query Analyzer, but not in the enterprise manager. SQL Server and Agent
> are restarted.
> Any idea ?
|||Basically, setting this option through sp_dboption is pretty much worthless,
since it is almost always overridden by the tool when they open a new
connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"joia" <joia@.discussions.microsoft.com> wrote in message
news:B732E540-DD1A-45B5-A8FB-A28CE67F1A4D@.microsoft.com...
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works
in
> the Query Analyzer, but not in the enterprise manager. SQL Server and
Agent
> are restarted.
> Any idea ?

concatenate a string within a loop from a temp table

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

An easy way (but slow if you have a big table) to do this is to use a cursor. Bellow is a fully functional example.

Hope it helps!

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

DECLARE @.MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @.PK Int

DECLARE @.MyString Varchar(4000)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT PK FROM @.MyTable GROUP BY PK

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @.PK

WHILE @.@.FETCH_STATUS=0

BEGIN

SET @.MyString = ''

SELECT @.MyString = @.MyString + pocket FROM @.MyTable WHERE PK = @.PK

INSERT INTO @.MyResult(PK, SumOfPockets) VALUES (@.PK, @.MyString)

FETCH NEXT FROM cMyTable INTO @.PK

END

CLOSE cMyTable

DEALLOCATE cMyTable

SELECT * FROM @.MyResult

|||

Doru,

This looks like a great solution although i have a very huge table and i ran out of resources when i tried to implement your solution. I was hopping i can do it with some loops and temp tables. do you have any other ideas.

Emad

|||i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.|||

Emadkb wrote:

i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.

If you're running on SQL Server 2005, you can use VARCHAR(MAX) and NVARCHAR(MAX) variables in your string concatenation.

|||we are still on SQL 2000|||

Hi Emad,

If Varchar(8000) is not big enough then you'll have to use Text datatype in your destination table. This will slow down the whole thing even more :-(.

A solution that should work, but which is very slow, is:

---

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

CREATE TABLE #MyResult (PK Int, SumOfPockets Text)
DECLARE @.PK Int, @.pocket Varchar(10), @.prev_PK Int
DECLARE @.ptrColText Varbinary(16)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR
SELECT PK, pocket FROM @.MyTable

OPEN cMyTable
FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.PK = @.prev_PK
UPDATETEXT #MyResult.SumOfPockets @.ptrColText NULL 0 @.pocket
ELSE
BEGIN
INSERT INTO #MyResult(PK, SumOfPockets)
VALUES (@.PK, @.pocket)

SELECT @.ptrColText = TEXTPTR(SumOfPockets)
FROM #MyResult WHERE PK = @.PK

SET @.prev_PK = @.PK
END

FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

END
CLOSE cMyTable
DEALLOCATE cMyTable

SELECT * FROM #MyResult

---

|||

This looks exactly like i want. Excellent work. I will try it and let you know how it goes.

Emad

Concatenate

I'm trying to concatenate First name, Middle Initial and Last Name. Many
records do not have a middle initial (middle initial is NULL) therefore
causing the concatenation to yield a Null result. Is there an easy trick /
technique to fix this? thanks
You can use COALESCE or ISNULL to replace the NULLs to spaces like:
SELECT first_name + COALESCE( middle_initial, '' ) +
COALESCE( last_name, '' ) AS "full name"
FROM ...
Anith
|||Thank You!
"Anith Sen" wrote:

> You can use COALESCE or ISNULL to replace the NULLs to spaces like:
> SELECT first_name + COALESCE( middle_initial, '' ) +
> COALESCE( last_name, '' ) AS "full name"
> FROM ...
> --
> Anith
>
>

Concatenate

I'm trying to concatenate First name, Middle Initial and Last Name. Many
records do not have a middle initial (middle initial is NULL) therefore
causing the concatenation to yield a Null result. Is there an easy trick /
technique to fix this? thanksYou can use COALESCE or ISNULL to replace the NULLs to spaces like:
SELECT first_name + COALESCE( middle_initial, '' ) +
COALESCE( last_name, '' ) AS "full name"
FROM ...
--
Anith|||Thank You!
"Anith Sen" wrote:
> You can use COALESCE or ISNULL to replace the NULLs to spaces like:
> SELECT first_name + COALESCE( middle_initial, '' ) +
> COALESCE( last_name, '' ) AS "full name"
> FROM ...
> --
> Anith
>
>

Concatenate

I'm trying to concatenate First name, Middle Initial and Last Name. Many
records do not have a middle initial (middle initial is NULL) therefore
causing the concatenation to yield a Null result. Is there an easy trick /
technique to fix this? thanksYou can use COALESCE or ISNULL to replace the NULLs to spaces like:
SELECT first_name + COALESCE( middle_initial, '' ) +
COALESCE( last_name, '' ) AS "full name"
FROM ...
Anith|||Thank You!
"Anith Sen" wrote:

> You can use COALESCE or ISNULL to replace the NULLs to spaces like:
> SELECT first_name + COALESCE( middle_initial, '' ) +
> COALESCE( last_name, '' ) AS "full name"
> FROM ...
> --
> Anith
>
>

Concatenatation with NULL

I am changing the setting od my database to put concatenate null yields null to off.....the following are the statements i run....

exec sp_dboption 'Solumina','concat null yields null','false'

SELECT 'abc' + NULL

I expect 'abc' to be returned after this....But not so..I get NULL

Can any one tell me this setting has to be changed at the connection level. if so, why has this been provided as a db option.

the following works....

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'abc' + NULL

abc

-

ODBC and SQL Query Analyzer will turn this ON by default so you need to explicitly turn the behavior OFF if you are using either of these connection mechanisms

Run this: select databaseproperty(''Solumina', 'IsNullConcat')

is the result 0? then it's set to OFF, however you have to change it from QA to make it wok in a query window

go to Tools-->options-->Connection Properties and uncheck set concat_null_yields_null

Denis the SQL Menace

http://sqlservercode.blogspot.com/

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.

Computed columns

Hi,

Consider the following example

create table sample

(col1 int,

col2 int ,

col3 AS col1 + col2) PERSISTED NOT NULL)

basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column

the value does not exist by default

when you select a record and you included the calculated column

the server reevaluates everything which

is one of the great disadvantage of the computed column.

computation is being done all over again when you select from this column

|||I have marked it PERSISTED ...meaning that the column is saved in the database. I think what you are talking about is the computed column without using the PERSISTED key word.|||

sorry i wasn't aware of that new feature

any way

Their values are updated when any columns that are part of their calculation change

|||I would say magic :) Seriously, it would be done at a physical implementation level below what we have access to, much like values in an index get maintained. I think if you thought of it sort of like a row-level trigger it wouldn't be "wrong," but it is not through any mechanism that we have direct access to for sure.|||the reason why i posted the question is : If i use a lot of computed columns in my database, will it cause any kind of performance problems. so far what ever i have read about computed columns, no where it is mentioned that using computed columns may cause performance problems. Please let me know if you have any information.|||I think the question is more about how you need the data. There will be a performance hit, whether you persist them, or not. The difference will be based on whether you modify data more, or read it more. If it is a frequently used column (or might be) try persisting it. If it doesn't slow you down too much then that would be the best idea. Unless your formula is tremendously complex, I doubt you will even notice.|||

one good thing in answering in this forum is that you

got to learn new things. anyway here's what BOL has to say

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

In terms of performance therefore persisted columns, performs better than non-persisted. Except of course when we hard talking of harddisk consumption. persisted column may even outrun a column-trigger solution and the first is easier to maintain over the later.

|||Thanks for all your replies.

Compute sum of count(*) with group by

Hi,
Given the following table and test data:
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (5,1,'2005-01-01 15:15','2005-01-01 15:20')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
the following query lists only the spid's with non-unique spid's and
their respective counts:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
I'm new to SQL and am having difficulty with a couple of things:
1. Modify the above query to compute the grand total for the Count, or
indeed a separate SQL statement to return just the grand total (= 6 in
this example).
2. This is the big challenge :). Taking the grouping returned by the
above query, write a query/stored procedure which looks for records
with identical spId's and the endtime of one spid equal to the
startTime of another. With the above test data, recordIds 3, 5, and 2,
4, 6 match this criteria.
Thanks very much for any help with this.1. Use a derived table construct:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total ) ;
2. Not sure if your requirements are clear since . Something like:
SELECT recordId, spId, ...
( SELECT MIN( t2.startTime )
FROM tbl t2 WHERE t2.spId = t1.spId
AND t2.startTime >= t1.endtime )
FROM tbl t1
ORDER BY t1.spid, startTime ;
If, not please post the sample resultset for the dataset you posted.
Anith|||Anith Sen wrote:
> 1. Use a derived table construct:
> SELECT SUM( total )
> FROM ( SELECT spid, COUNT(*)
> FROM tbl
> GROUP BY spid
> HAVING COUNT(*) > 1 ) D ( spid, total ) ;
>
Thanks. What does the 'D' mean above?

> 2. Not sure if your requirements are clear since . Something like:
> SELECT recordId, spId, ...
> ( SELECT MIN( t2.startTime )
> FROM tbl t2 WHERE t2.spId = t1.spId
> AND t2.startTime >= t1.endtime )
> FROM tbl t1
> ORDER BY t1.spid, startTime ;
> If, not please post the sample resultset for the dataset you posted.
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (5,1,'2005-01-01 14:33','2005-01-01 15:20')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
(Sorry, no wonder it wasn't clear as there was mistake in my original
test data. I've corrected the data above and put records with the same
spId together to make the grouping more obvious.)
So, from the above test data the expected results contain 2 sets of
matching data:
1. recordIds 3 and 5 because they have the same spId (1) and the
endTime of recordId 3 is the same as the startTime of recordId 5.
2. recordIds 2, 4 and 6 because they have the same spId (2) and the
endTime of recordId 2 is the same as the startTime of recordId 4; the
endTime of 4 is the same as the startTime of 6.
I hope that makes sense now. cheers,|||On 11 Nov 2005 09:30:25 -0800, "J Williams"
<johnwilliams_esquire@.hotmail.com> wrote:
>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
If that does the job, great, otherwise you can always store the
results of the first query in a table an do further summations against
it.
J.|||>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
Thanks, but that doesn't give the expected result. The basic SELECT:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
returns:
spid Count
1 3
2 3
The grand total of Count in the above resultset is 6 and the SQL posted
earlier by Anith Sen gives this result:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total )|||>> What does the 'D' mean above?
D in the query stands for an alias for the derived table ( some folks
explicitly use AS keyword before the alias as well. )
Can you post the sample resultset here ( as you'd want to see on the QA
results pane ).
Anith|||Anith Sen wrote:

> Can you post the sample resultset here ( as you'd want to see on the QA
> results pane ).
First recordId, Second recordId, spId, endTime, startTime
3 5 1 2005-01-01 14:33 2005-01-01 14:33
2 4 2 2005-01-01 14:00 2005-01-01 14:00
4 6 2 2005-01-01 15:15 2005-01-01 15:15
The resultset shows pairs of 'matching' records, which is slightly
different (and better) to how I first envisioned it.
Thanks.|||This is one way of getting it:
SELECT MAX( t1.recordid ),
t2.recordid, t1.spid, t1.endtime
FROM test t1
INNER JOIN test t2
ON t1.spId = t2.spId
AND t1.endTime = t2.starttime
GROUP BY t1.spid, t2.recordid, t1.endtime ;
Anith|||That's excellent, thanks.

composite indexes - a subtle question

I have a table like so
CREATE TABLE [dbo].[account] (
[pty_id] [int] NOT NULL ,
[sort_code] [int] NOT NULL ,
[account_no] [int] NOT NULL ,
[account_open_dt] [datetime] NULL ,
[account_close_dt] [datetime] NULL ,
[account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[category_code] [smallint] NULL ,
[prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
) ON [PRIMARY]
as you can see there's a primary key clustered index on account_no and
sort_code
I can link it up to any other table efficiently which also has sort_code and
account_no as part of a single index (primary or otherwise)
ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
etc...
and that will work fine...
Here's my question
if account_no is part or the sort_code/Account_no composite key (and
therefore index)
could I link another table to only the account_no column and still get some
indexing benefit
i.e can you join on a single column of a multiple column index and still get
some help from that composite index or does that composite index only work a
s
a single entity and in fact you need to create an extra nonclustered index
for that column on top of the other index it's participating in to get the
benefit of indexing on that column...
phew
I hope this makes sense
any help would be great appreciated
Regards and thanks in advance,
CharlesACharles,
When joining to the first column of a multi-column index, some
of the same optimizations are available--for example, a merge
join (if the other table also has a supporting index) or nested loop
joins with index ss. Depending on the exact query, the two-column
index may be just as useful as a separate one-column index or a
little less useful (typically because the two-column index takes up
more data pages because of duplications in the first column and the
presence of the second column even when duplicates are few), but it
should still help in most cases. The only real way to be sure is
to create the additional index and measure the performance.
Steve Kass
Drew University
CharlesA wrote:

>I have a table like so
>CREATE TABLE [dbo].[account] (
> [pty_id] [int] NOT NULL ,
> [sort_code] [int] NOT NULL ,
> [account_no] [int] NOT NULL ,
> [account_open_dt] [datetime] NULL ,
> [account_close_dt] [datetime] NULL ,
> [account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [category_code] [smallint] NULL ,
> [prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
> CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
> ) ON [PRIMARY]
>
>
>as you can see there's a primary key clustered index on account_no and
>sort_code
>I can link it up to any other table efficiently which also has sort_code an
d
>account_no as part of a single index (primary or otherwise)
>ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
>etc...
>and that will work fine...
>Here's my question
>if account_no is part or the sort_code/Account_no composite key (and
>therefore index)
>could I link another table to only the account_no column and still get some
>indexing benefit
>i.e can you join on a single column of a multiple column index and still ge
t
>some help from that composite index or does that composite index only work
as
>a single entity and in fact you need to create an extra nonclustered index
>for that column on top of the other index it's participating in to get the
>benefit of indexing on that column...
>phew
>I hope this makes sense
>any help would be great appreciated
>Regards and thanks in advance,
>CharlesA
>|||Charles,
Can you recreate your PK as (account_no, sort_code)? What are the
cardinalities of these 2 columns?|||Charles,
I know you'd like an immediate answer, but it might really help to
watch Kimberly Tripp's (SQL Server MVP) presentation at the following
website. I'm in the process of designing indexes and it was incredibly
helpful to watch the presentation. She keeps it simple but I finally
got it...
http://www.microsoft.com/uk/technet...aspx?videoid=29
Other links:
http://msevents.microsoft.com/CUI/E...e=en-U
S
http://www.sqlskills.com/blogs/kimb...
3-a58ba7b1265e
(The presentation took me a while to download and it is an hour long
but totally worth it.)|||Hi,
Whether you get performance benifit or not depends on the location of the
column in the order of the index key.
You want to join Account_no from tbl1 with Account no in tbl2 with
sort_code/Account_no as an index.
If the left most column in the index is sort_code i.e
Index(sort_code,Account_no) , you don't find any use of the index, it will
still go for an idex scan.
But if your index had been Index(Account_no,sort_code)
then this index is sufficient for joining Account_no alone or Account_no and
sort_code.
Hope I made sense :)|||Thanks everyone for your answers, much appreciated...
I will download that presentation when I'm at home, I've been dying to
understand indexing properly for ages now
as I say, thanks All
Regards,
CharlesA

complicated update (for me)

hello everyone,
just having trouble with an update statement. so i started off with a
table like so:
create table foo (
id uniqueidentifier not null primary key nonclustered,
name varchar(50) not null,
type int not null,
datecreated datetime not null default getdate(),
constraint foo1 unique (name)
)
go
then i added a new column, like so:
alter table foo add column rank int null
go
and here's where the tricky update comes in. my goal is to populate
this new "rank" column with incremental values for each distinct "type"
value.
for example, i the original table looks like so:
select name, type from foo
go
name type
-- --
john 1
jane 1
jim 1
jake 2
jeff 2
kyle 2
keli 2
kim 2
i would want the update statement to populate the table so that AFTER
the update, it looks like so:
select name, type, rank from foo
name type rank
-- -- --
john 1 1
jane 1 2
jim 1 3
jake 2 1
jeff 2 2
kyle 2 3
keli 2 4
kim 2 5
it's important to note that the original "rank" really doesn't matter,
it is just something that needs to be kept track of moving forward, so
i don't even particularly have to rank them alphabetically, or any
other way. just need an update statement that can put in incremental
values, but that sort of "resets" the incrementation for each changing
value of another field.
if that's possible.
thanks in advance for any help!
jasonYou have a few problems with this. First, a uniqueidentifier cannot be
a relational key, so this is not a table by definition. Where does it
occur in the reality of the data model? Next, Rows are not records;
fields are not columns; tables are not files. Totally differerent
concepts. You are building a sequential file in SQL
DEFAULT comes after the data type in STANDARD SQL, and you can use
CURRENT_TIMESTAMP instead of the proprietary getdate(). But you should
not put audit trail information in the table (ask your accountant about
proper procedures).
There is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. So what are you using to
assign the rank values? The data model should have some rule so you
can validate the data.
Granted this is a sample table, but all of the column names are
incomplete (type of what? Name of what? Etc.) Your sample design
should look more like this:
CREATE TABLE NewFoo
(foo_name VARCHAR(50) NOT NULL PRIMARY KEY,
foo_type INTEGER NOT NULL,
bar_rank INTEGER NOT NULL,
UNIQUE (foo_type, bar_rank)); -- is this the PK?
Now, let's kill the old one and get things into an RDBMS:
INSERT INTO NewFoo (foo_name, foo_type, bar_rank)
SELECT name, type,
(SELECT COUNT(F1.*)
FROM Foo AS F1
WHERE F1.type = Foo.type
AND F1.name = Foo.name)
FROM Foo;
Then drop Foo and rename NewFoo.|||> First, a uniqueidentifier cannot be a relational key, so this
> is not a table by definition. Where does it occur in the
> reality of the data model?
i call bs on this one, CELKO. a uniqueidentifier is a perfectly valid
artificial key for any record. what makes you think it can't be a
relational key?

> But you should not put audit trail information in the table
i call bs on this one too, i'm afraid. row-level auditing is a
perfectly valid practice for a number of reasons.
as for the rest, yeah, i was lazy, i didn't actually try the create
table statement before posting :) and i'll try to be more descriptive
in future examples.
as for the rank, it's definitely not an attempt to make the table a
sequential file, so you guessed wrong there i'm afraid. i use clustered
indexes if order is important for something, which has specific,
uncommon purposes in my opinion.
the rank is a meaningful value that a subscriber application needs to
know. you can think of it like your standing in a contest, you're
either in first, second, third, etc. place. the rank can't actually be
determined by any values in the database, it is determined by the
providers of the data, and we just store the rank. it's meaningful, not
physically sequential, etc.
and lastly, regarding the insert statement: THANK YOU. exactly what i
was looking for. i look forward to trying it out when i get back to the
office tomorrow.
thankful as always,
jason|||>> call bs on this one, CELKO. a uniqueidentifier is a perfectly valid artificial
key for any record [sic]. what makes you think it can't be a relational key? <<
The very definition of a relationall key, the stuff Dr. Codd wrote and
basic data model concepts. A relational key is subset of the
attributes of an entity that is unique. A uniqueidentifier is derived
from physical storage and has nothing whatsoever to do with the entity.
Newbies who do not know that a row (logical construct) is nothing like
a record (physical storage) constantly make this mistake and build file
systems in SQL.
An artificial key has to have validation and verification rules, and a
uniqueidentifier does not.
That is fine, but putting the audit trail into the table that is being
audited is not a proper accounting practice. The changes need to be
caught outside of the table. Talk to the accounting department or the
SOX guy for your company. This is like letting developers do their own
QA.|||assuming (name, type) is unique
update foo set rank = (select count(*) from foo f1 where f1.type =
foo.type and f1.name<foo.name)+1
if (name, type) is NOT unique, it it still doable but more complex|||> A uniqueidentifier is derived from physical storage
well, I guess whoever says this at a job interview is less likely to
get hired ;)|||> A relational key is subset of the
> attributes of an entity that is unique.
actually, if i'm reading you correctly, that's a called NATURAL key.
relational keys do not have the necessary condition of being natural
elements of an entity. the only necessary condition of a relational key
is that it be a column or columns whose values are gauranteed to be
unique across all occurrences in a given table. that's it.
by this definition, a relational key can be natural OR artificial. what
you're describing is a natural relational key, and good for you, that's
totally fine. and so are artificial relational keys.

> An artificial key has to have validation and verification rules, and a
> uniqueidentifier does not.
the only "validation and verification" rule required to act as a
relational key is that it be UNIQUE. and uniqueidentifiers, when
properly used, are certainly that.
i presume that you would have just as many objections about using an
identity integer as a relational key? if that's true, then you're
grossly misrepresenting your argument. you're not arguing the
definition of a relational key, you're arguing the validity of
artificial versus natural keys AS relational keys. totally different
argument.

> That is fine, but putting the audit trail into the table that is being
> audited is not a proper accounting practice.
i don't see your logic here. what is the difference between attaching
such a column to the entity it is auditing and putting it in another
table, and relating it to the entity it is auditing? the only
difference i can think of is that you could apply different user
permissions to each table. that's fine and well, but there are plenty
of other places to handle security, and other considerations, such as
performance.|||this worked like a charm, thank you very much!
jason|||yeah, i wasn't sure where that was coming from either. aren't they
derived from like a bunch of crazy variables? datetime, cpu serial
number, mac address, your mother's maiden name, the position of the
every valence electron in your body ...|||>> what is the difference between attaching such a column to the entity it i
s auditing and putting it in another table, and relating it to the entity it
is auditing? <<
You do not have to put the audit information in the schema at all. It
can be in an external file system or other RDBMS.
Separation is a basic accounting principle, like double entry
bookkeeping. For example, when I submit an article to a publisher, I
send the editor one copy of the invoice and another copy to Accounts
Payable. The A/P clerk has to match both copies of the invoice before
they issue a check to me.
My editor deletes their copies of my invoice. The A/P clerk now has an
invoice without a mate at the end of the payment cycle, so they know to
start calling people.
The A/P department deletes their copies of my invoice. The editor now
has an invoice which was no paid at the end of the payment cycle, so
they know to call the A/P department.
Both editor and A/P delete their copies of my invoice. Accounting sees
a missing invoice number at the end of the payment cycle, because they
designed an invoice number that can be validated and verified rather
than a meaningless, hardware generated number. Accounting makes life
hell for everyone until they can trace that missing invoice number.
.

Complex where clause?

Hi,

I have a stored procedure with a few parameters. One of them is @.ProviderParam and it defaults to Null. If a value is passed into that parameter when the sp is called, I need to include a check for the provider in the where clause, like this:

Where <some other stuff> AND Provider = @.ProviderParam

But if nothing is passed into that parameter (and it defaults to Null), I need to do nothing with provider in the where clause. The where clause would look like this:

Where <some other stuff>

I believe the solution is to use a CASE statement in the where clause somehow, but I'm not sure how to proceed. Can anyone please help?

Thanks.

One way is to do something like:

AND ( @.ProviderParam is null or
Provider = @.ProviderParam)

If your PROVIDER column is a non-null column you also might be able to use:

AND Provider = ISNULL (@.ProviderParam, Provider)

Again, the column must be a NOT NULL column or this filter will not work correctly whenever both @.ProviderParam and Provider are null.

|||

If I understand your issue correctly, you wish to optionally provide a value for the @.ProviderParam, use it in the WHERE clause if it is available, otherwise if it is NULL, ignore @.ProviderParam. If so, this may work for you:

AND Provider = coalesce( @.ProviderParam, Provider )

As Kent indicated, Provider MUST be a NOT NULL column for this approach to work properly.

|||The advantage of coalesce is that it will also work with DB2 whereas ISNULL will not.|||

Great stuff. Thank you, both.

Provider is a null column. DB2 is not a factor. Kent's 1st solution is really elegant. It doesn't involve a function call, which is efficient, and it's so simple. Just too much elegance for me to pass by! :)

Thanks, again, for these insights.

|||

Just a note to say that the other advantage of COALESCE is that it can take an arbitrary number of arguments and returns the first one that is not null.

SET A_Value = COALESCE(First_Choice, Second_Choice, Desperate_Choice, Default_Value)

This can be useful if you have something like 3 different names you could use before giving up and using 'UNKNOWN'.

Complex UPDATE

Given the 2 tables Sponsor and SponsorEvent with SponsorID on SponsorEvent
as a FK
CREATE TABLE [dbo].[Sponsor] (
[SponsorID] [int] NOT NULL ,
[SponsorCode] [char] (15) NOT NULL ,
[BrandID] [int] NOT NULL ,
[FirstDate] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sponsor] WITH NOCHECK ADD
CONSTRAINT [PK_Sponsor] PRIMARY KEY CLUSTERED
(
[SponsorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[SponsorEvent] (
[Date] [int] NOT NULL ,
[BarbChanID] [int] NOT NULL ,
[StartTime] [int] NOT NULL ,
[AreaFlags] [smallint] NOT NULL ,
[PlatformFlags] [tinyint] NOT NULL ,
[EndTime] [int] NOT NULL ,
[SponsorID] [int] NOT NULL ,
[SponsorICodeID] [int] NOT NULL ,
[SponsorINameID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SponsorEvent] WITH NOCHECK ADD
CONSTRAINT [PK_SponsorEvent] PRIMARY KEY CLUSTERED
(
[Date],
[BarbChanID],
[StartTime],
[AreaFlags],
[PlatformFlags]
) WITH FILLFACTOR = 90 ON [PRIMARY]
what is wrong with this syntax
update Sponsor set FirstDate = att.[Date]
from (
SELECT SponsorID,MIN([DATE]) From SponsorEvent GROUP by SponsorID)
att,Sponsor s
where s.SponsorID = att.SponsorID
Query Analyser complains of
"No column was specified for column 2 of 'att'."
I am trying to group by SponsorID on SponsorEvent, work out what the minimum
date is on SponsorEvent and update [Date] on Sponsor with this where
SponsorID is in common.
Thanks
Stephen Howe> Query Analyser complains of
> "No column was specified for column 2 of 'att'."
Isn't it "No column NAME was specified...?"
This happens when you apply a calculation on a column and don't include an
alias. The outer query doesn't see a column named "Date", it only sees the
expression MIN([Date]), which is not the same thing.
How about :
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor
INNER JOIN
(
SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att
ON s.SponsorID = att.SponsorID
Or, better yet, instead of having to run this update every single time the
SponsorEvent changes, drop the column firstdate from the sponsor table.
There is no reason to store this redundant information when you can always
get it directly from SponsorEvent.
As an aside, I recommend renaming the column [Date]. It is never a good
idea to use reserved words as column names.
A|||Sorry, forgot the s:

> FROM Sponsor
Should be
FROM Sponsor s|||Thanks for response

> Isn't it "No column NAME was specified...?"
No. Definitely not. What I wrote was copy and pasted out of QA.
There is a message in colour red just before. The full message is
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'att'.

> How about :
> UPDATE Sponsor
> SET FirstDate = att.FirstDate
> FROM Sponsor
> INNER JOIN
> (
> SELECT SponsorID, [Date] = MIN([DATE])
> FROM SponsorEvent
> GROUP by SponsorID
> ) Att
> ON s.SponsorID = att.SponsorID
Does not work. I changed text to
SET FirstDate = att.[Date]
FROM Sponsor s
and now I get
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'att' does not match with a table name or alias name used
in the query."

> Or, better yet, instead of having to run this update every single time the
> SponsorEvent changes, drop the column firstdate from the sponsor table.
> There is no reason to store this redundant information when you can always
> get it directly from SponsorEvent.
Yes you are right and I would like to.
But the reason why is that we have a analogous situation for tables where
Sponsor and SponsorEvent which have SponsorID in common
is reproduced with
SpotFilm and Spot which have SpotFilmID in common
SpotFilm and Sponsor have identical columns apart from the ID fields. It is
also such that if you concatenated them, the ID's are unique, no row in
common.
I say "somewhat symmetrical" because the difference is that data in
SponsorEvent is split between Spot and Slot.
Spot as a table has over 100 million rows (and gets larger per w).
Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDate
just happens to be a compromise. I know what you are saying, and I agree in
principal. At some point this year the server will be upgraded from SQL
Server 7 to 2000 and maybe the optimisation might be better.

> As an aside, I recommend renaming the column [Date]. It is never a good
> idea to use reserved words as column names.
I know. Noted. We have a table called [Break] which I am loathe to change.
Granted it is a keyword, but in the industry I am in "Break" is the most
descriptive word there is for the data it contains. They really are called
"Commercial Breaks". But I will change [Date] :-)
Stephen Howe|||What, do you have a case sensitive collation? Try using att everywhere and
no Att vs. att?
This works fine for me. You might want to provide similar DDL in the future
to make it easier for others to reproduce your scenario and test their
results. (See http://www.aspfaq.com/5006)
USE Tempdb
GO
CREATE TABLE Sponsor
(
SponsorID INT,
FirstDate SMALLDATETIME
)
GO
CREATE TABLE SponsorEvent
(
SponsorID INT,
[Date] SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT Sponsor SELECT 1, NULL
INSERT Sponsor SELECT 2, NULL
INSERT Sponsor SELECT 3, NULL
INSERT Sponsor SELECT 4, NULL
INSERT SponsorEvent SELECT 1, '20010501'
INSERT SponsorEvent SELECT 1, '20010601'
INSERT SponsorEvent SELECT 1, '20040801'
INSERT SponsorEvent SELECT 3, '20040701'
INSERT SponsorEvent SELECT 3, '20010601'
INSERT SponsorEvent SELECT 4, '20030801'
GO
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor s
INNER JOIN
(
SELECT SponsorID, FirstDate = MIN([Date])
FROM SponsorEvent
GROUP BY SponsorID
) att
ON s.SponsorID = att.SponsorID
GO
SELECT * FROM Sponsor
GO
DROP TABLE SponsorEvent, Sponsor
GO
On 3/12/05 12:57 PM, in article ORy87zyJFHA.2716@.TK2MSFTNGP15.phx.gbl,
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote:

> Thanks for response
>
> No. Definitely not. What I wrote was copy and pasted out of QA.
> There is a message in colour red just before. The full message is
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 2 of 'att'.
>
> Does not work. I changed text to
> SET FirstDate = att.[Date]
> FROM Sponsor s
> and now I get
> "Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'att' does not match with a table name or alias name use
d
> in the query."
>
> Yes you are right and I would like to.
> But the reason why is that we have a analogous situation for tables where
> Sponsor and SponsorEvent which have SponsorID in common
> is reproduced with
> SpotFilm and Spot which have SpotFilmID in common
> SpotFilm and Sponsor have identical columns apart from the ID fields. It i
s
> also such that if you concatenated them, the ID's are unique, no row in
> common.
> I say "somewhat symmetrical" because the difference is that data in
> SponsorEvent is split between Spot and Slot.
> Spot as a table has over 100 million rows (and gets larger per w).
> Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
> Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDa
te
> just happens to be a compromise. I know what you are saying, and I agree i
n
> principal. At some point this year the server will be upgraded from SQL
> Server 7 to 2000 and maybe the optimisation might be better.
>
> I know. Noted. We have a table called [Break] which I am loathe to change.
> Granted it is a keyword, but in the industry I am in "Break" is the most
> descriptive word there is for the data it contains. They really are called
> "Commercial Breaks". But I will change [Date] :-)
> Stephen Howe
>
>
>|||> Sorry, forgot the s:
>
> Should be
> FROM Sponsor s
Thanks Aaaron, done it
UPDATE Sponsor
SET FirstDate = att.[Date]
FROM
(SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att INNER JOIN Sponsor s ON Att.SponsorID=s.SPonsorID|||> What, do you have a case sensitive collation? Try using att everywhere
and
> no Att vs. att?
I don't think we do. Anything to do with SQL Server 7 not recognising the
syntax?
Perhaps SQL Server 2000 is "improved" in that order does not matter
I changed the order putting Sponsor s last, (after the SELECT .. GROUP BY)
and at that point it recognised it.
Strange
But thanks
Stephen|||Date is not just a reserved word, it is too vague to be a valid data
element name -- date of what' Is there only one sponsor, as you
showed with a singular name? If you were writing SQL instead of a
strange dialect, would this be what you meant? It is a bad practice to
name relationship tables by concatenating names together -- always ask
if the relationship has its own name. Endorsements, sponsorships, etc.
UPDATE Sponsors
SET firstdate -- of what'
= (SELECT MIN(foobar_date)
FROM Endorsements AS E
WHERE E.sponsor_id = Sponsors.sponsor_id)
You might want to read a book on SQL and see what the standard UPDATE
syntax is.
And get a book on data modeling. Names like "SponsorICodeID" make
absolutley no sense. A code is not an identifier; it is a scalar value
on a nominal scale. Most of the rest of your DDL looks like you are
writing SQL with flags, etc. -- all the classic mistakes of someone who
does not know how to make a schema.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110772877.481289.236930@.l41g2000cwc.googlegroups.com...
> It is a bad practice to name relationship tables by concatenating
names together
Mr. Celko,
Really? I've been doing a lot of that lately (sometimes M-to-M
relationships are stumpers). Is that a part of a standard I can take
a look at? Perhaps you could provide a weblink to an article or other
work with an extensive description of why it is bad, and the process
of doing better.
Sincerely,
Chris O.|||>> [name relationship tables by concatenating names together ] I've
been doing a lot of that lately (sometimes M-to-M relationships are
stumpers). <<
Not if you start from a data model. Relationships important enough to
be modeled tend to have names -- "Marriages" instead "ManWoman' or even
worse "CivilUnions" instead of "ManMan_WomanWoman_ManWomen". A
relationship name invites the attributes that go with the relationship;
Marriages implies a wedding date, license number, etc.
Google up ISO-11179; the principle is to name a thing for what it *is*,
not for what it *does* in a particular situation, not for hopw you
build it-- i.e. this is an "automobile", not a "TiresFrameMotor"; the
whole not the parts.
an extensive description of why it is bad, and the process of doing
better. <<
Look for an entire book on SQL style about the middle of this year from
me.

complex SQL (for me)

Hello,

Lets look at this table :

CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Identifiant d''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Id'

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Date de l''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Week'

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Projet de rfrence' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Project'

It is a table where i store projects week reports.

I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.

I use SQL 2005. Thanks for any help

You can use a Matrix Report type that supported by many report products

like Crystal report ,

it is easy to use by a power full wizard

|||

I cant use it becouse i need to use it as a navigation tool on web site

|||

See if this set of posts helps you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

We did a rotation of a set like this (more generic in nature) in these posts.

|||

Your sample is very nice but i don't know how to use it.

Lets me show what I want :

set nocount on
create table Histo
(
Id varchar(10) primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-12','Internet'
union all
select '49','2006-13','Intranet'
go

select *
from Histo
go

it make this table :

Id | Week | Project
47 | 2006-12 | Internet
48 | 2006-12 | Internet
49 | 2006-13 | Intranet

And I Want to get something like that :

Project | 2006-12 | 2006-13
Internet | 47 | 49
Intranet | 48 | null

I work on it for days and dont find the solution.

Thanks a lot for any help

|||Well, this is the perfect example of the use of the PIVOT keyword in SQL Server 2005. If you're using SQL 2005 then use PIVOT. If you're using SQL 2000 then you'll have to use a case statement to accomplish this and it can be a bit cumbersome.|||Please demonstrate. I don't quite know how you will achieve what is being asked.|||nobody can help me ?|||

I found the solution :

set nocount on
create table Histo
(
Id varchar(10)primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-13','Internet'
union all
select '49','2006-12','Intranet'

go

select*
from Histo

go

SELECT Project,
[2006-12],
[2006-13]
FROM
(SELECT Project, week, id
FROM histo) s
PIVOT
(
max(id)
FOR Week IN ([2006-12],[2006-13])
) p
ORDER BY [Project]
go

drop table histo

complex SELECT

Hi,

I have problem about writing a proper SELECT query for the following
goal:

Table name: peoplelist
column 1: id (not NULL, auto_incremental)
column 2: name
column 3: country

now, there are about 7,000 rows in this table. I want to select out:
first 10 or less people in the table for each country.

for example: suppose there are :

1000 people from US
3000 people from UK
3000 people from Canada

I want to list totally 30 people, i.e. 10 people from each country.

The problem is , the actual table includes many countries, not only
three. How can I do this by a SELECT sql query ?

Thanks.

HanAssuming you want the 10 people from each country based on their IDs (10
lowest values), one of the following queries should do it.

CREATE TABLE People (id INTEGER PRIMARY KEY, name VARCHAR(30) NOT NULL
UNIQUE, countrycode CHAR(2) NOT NULL /* REFERENCES Countries (countrycode)
*/)

Standard SQL:

SELECT P.id, P.name, P.countrycode
FROM People AS P
WHERE id IN
(SELECT P1.id
FROM People AS P1
JOIN People AS P2
ON P1.id >= P2.id
AND P1.countrycode = P.countrycode
AND P2.countrycode = P.countrycode
GROUP BY P1.id
HAVING COUNT(*)<=10)

TSQL using TOP:

SELECT P.id, P.name, P.countrycode
FROM People AS P
WHERE id IN
(SELECT TOP 10 id
FROM People
WHERE countrycode = P.countrycode
ORDER BY id)

--
David Portas
SQL Server MVP
--

Complex Delet Query .. Help me!

I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.have you tried ON DELETE CASCADE in the foreign key relationship?

i've never done this myself (cascading delete in the adjacency model hierarchy), so i'd be very interested in finding out that it works|||I have not had much SQL experience.. Im more of a C#/C++/asp.net developer! Moreover Iam deleting the records in the same table.. so there is no foriegn key or anything here.. did I miss something!?|||create table yourtable
( SECID integer
, PARENTID integer
, NAME varchar(50)
, primary key (SECID)
, constraint validparent
foreign key (PARENTID)
references yourtable (SECID)
on delete cascade
)

-- load table

delete from yourtable where SECID=8001

Edit: nope, that don't workError: Introducing FOREIGN KEY constraint 'validparent' on table 'yourtable' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (State:37000, Native Code: 6F9)
Error: Could not create constraint. See previous errors. (State:37000, Native Code: 6D6)|||you'll have to do a recursive user define function...

(working on this RUDF..)|||Originally posted by netjkus
I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.

This is a complex query. I never heard or came accross like this. The parent-->child is nesting many times. I am not sure you can do it in one SQL. Might have to use a store procedure and keep the nodes in a temp table. Delete from the main table until no records found in temp table|||Check this one - with triggers (they have to be recursive - database option)...

drop table test
create table test (id int primary key,pid int)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
insert test values(4,2)
go
select * from test
go
create trigger tr_test on test
for delete
as
if not exists(select * from deleted)
return
delete test where pid in(select id from deleted)
go
delete test where id=2-- or 1|||smasanam

I'm almost there with my RUDF...
just a few minutes more|||GOT IT GOOD

coming up !|||Create these functions

CREATE FUNCTION Trim (@.Mot Varchar(230))
RETURNS Varchar(230) AS
BEGIN
return(Rtrim(Ltrim(@.Mot)))
END

CREATE Function ListOfChildren (@.Parents Varchar(100))
RETURNS Varchar(100)
As
Begin
Declare @.List Varchar(100),
@.NewList VarChar(100),
@.TotalList VarChar(100)

set @.List=@.Parents
set @.NewList='abc'
set @.TotalList='|' + @.Parents

while @.NewList<>''
begin
set @.NewList=''
Select @.NewList = @.NewList + '|' + dbo.Trim(SectID) From Family Where PatIndex('%' +dbo.Trim(ParentID)+ '%', @.List )<>0
set @.List= @.NewList
set @.TotalList=@.List+@.TotalList
end

Return(@.TotalList)
End|||then you can do :

delete family
where patindex('%' + dbo.Trim(sectid) +'%' , dbo.listofchildren('8007'))<>0 or
patindex('%' + dbo.Trim(parentid) +'%' , dbo.listofchildren('8007'))<>0

in fact the function ListOfChildren returns for 8007 the list of :
all children (8009,8010)
all children of children (8011)
the named parent (8007)|||http://www.sqlteam.com/item.asp?ItemID=8866

Or

http://archives.postgresql.org/pgsql-sql/2002-11/msg00358.php|||my solution seems simpler than Brett's...

wooooooooo
risky saying this|||Karolyn

Thanks a ton.. Iam trying this right now...|||Originally posted by Karolyn
my solution seems simpler than Brett's...

wooooooooo
risky saying this

troublemaker, heh?

Anyway isn't

while @.NewList<>''
begin
set @.NewList=''

False right away?|||nope 'cause it's set to 'abc'

need gllasssseesss ?|||(ding ding ding)
ROUND 1

Karolyn : right strong punch
Brett : KO|||and when you do the total

Brett : 1289
Karolyn : 1

catching up !!!|||How right you are...the evaluation doesn't come until the END...|||I can't answer lots of question
so when I do
I make a big fuss out of it
(a Frenchy-of-France-Attitude that I learned here in France)|||BUT STILL...
your

code:------------------------
while @.NewList<>''
begin
set @.NewList=''
------------------------
False right away?


was a VERY Low one|||Very nice...

USE Northwind
GO

CREATE TABLE family(SECtID varchar(10), PARENTID varchar(10))
GO

INSERT INTO family(SECtID, PARENTID)
SELECT '8000', NULL UNION ALL
SELECT '8001', '8000' UNION ALL
SELECT '8002', '8000' UNION ALL
SELECT '8003', '8002' UNION ALL
SELECT '8004', '8002' UNION ALL
SELECT '8005', '8003' UNION ALL
SELECT '8006', '8003' UNION ALL
SELECT '8007', '8001' UNION ALL
SELECT '8008', '8001' UNION ALL
SELECT '8009', '8007' UNION ALL
SELECT '8010', '8007' UNION ALL
SELECT '8011', '8009'
GO

SELECT dbo.ListOfChildren(8007)
GO

DROP FUNCTION TRIM
DROP FUNCTION ListOfChildren
DROP TABLE family
GO

And I like the fact that is comes out in asceding hierarchy, with the grandfather of it all last...|||Originally posted by Karolyn
BUT STILL...
your



was a VERY Low one

Well I apologize if I offended you.

Listen, that's why I'm a scrub...I missed it...

(had nothing to do with trying to put you down...a very silly game I don't play)|||Karolyn

Thanks a ton.. It works!! You made my day!! Iam implementing it !

compatibility level

Sorry here is the query
select Client_Name = rtrim(p.Last_Name) +
Case p.Mid_Name
WHEN null then ' '
WHEN ' ' then ' '
Else ' ' + p.Mid_Name + ' '
End
+ ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id

>--Original Message--
>I recently upgraded my database from 6.5 to 2000. To
take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query
in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks
>.
>
As Sue stated in her post, your query will be affected by the
CONCAT_NULL_YIELDS_NULL setting. To avoid that dependency, or write the
query better overall, you would use
select Client_Name = rtrim(p.Last_Name) +
isnull(p.Mid_Name,' ') + ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:173f01c499c1$0b721420$a401280a@.phx.gbl...[vbcol=seagreen]
> Sorry here is the query
> select Client_Name = rtrim(p.Last_Name) +
> Case p.Mid_Name
> WHEN null then ' '
> WHEN ' ' then ' '
> Else ' ' + p.Mid_Name + ' '
> End
> + ltrim(p.First_Name)
> FROM Client_Info c, Persons p
> WHERE c.Client_no = '0004530184'
> and c.person_id = p.Person_id
> take
> in

Comparison to NULL and '' fields

Guys, can anybody remind the settings responsible for the results of the
search by the NULL fields? I saw this database setting several months ago
but forgot this switch. The problem is the following. We're trying to run a
search in a few tables using some SQL query with a multilevel combination of
AND/OR with
WHERE SOMEFIELD LIKE '%SOMETEXT%'
If the field is NULL or '' (zero length string) then two possible results
are available depending on this database setting, true or false. The default
setting is so that if the field is NULL these records are excluded from my
result list but I need all these records to be included. So I need to find
this switch to get TRUE always when I compare to NULL strings to include the
records with NULL fields into the result list.
Thanks,
Dmitri.Do you mean
SET ANSI_NULLS
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<> )
comparison operators when used with null values.
Jens SUessmeyer.
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:Ojtbe.70654$lz2.58655@.fed1read07...
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
> a search in a few tables using some SQL query with a multilevel
> combination of AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The
> default setting is so that if the field is NULL these records are excluded
> from my result list but I need all these records to be included. So I need
> to find this switch to get TRUE always when I compare to NULL strings to
> include the records with NULL fields into the result list.
> Thanks,
> Dmitri.
>|||I strongly recommend not to do this. Take a look at this interesting puzzle
and see the consequnces of setting ANSI_NULLS OFF.
http://groups-beta.google.com/group...aca91a7912bdc76
To accomplish what you want, use:
...
WHERE SOMEFIELD LIKE '%SOMETEXT%' or soemfield is null
AMB
"Just D." wrote:

> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
a
> search in a few tables using some SQL query with a multilevel combination
of
> AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The defau
lt
> setting is so that if the field is NULL these records are excluded from my
> result list but I need all these records to be included. So I need to find
> this switch to get TRUE always when I compare to NULL strings to include t
he
> records with NULL fields into the result list.
> Thanks,
> Dmitri.
>
>|||Hi Jens,
Probably you're right. But if it works with LIKE ? Or only with <> = ?
Thanks,
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Do you mean
> SET ANSI_NULLS
> Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to
> (<> ) comparison operators when used with null values.
> Jens SUessmeyer.
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:Ojtbe.70654$lz2.58655@.fed1read07...
>|||I think it only changes the behavior of comparisons that include
at least one literal or variable that is NULL. Column-to-column
comparisons between NULLs are never true, regardless of the
setting. I think <column> LIKE NULL is never true also.
set ansi_nulls off
go
declare @.t table (
a int,
c char
)
insert into @.t values (null, null)
insert into @.t values (0, '0')
select * from @.t
where a = null
select * from @.t
where a = c
select * from @.t
where c like null
go
set ansi_nulls on
Steve Kass
Drew University
Just D. wrote:

>Hi Jens,
>Probably you're right. But if it works with LIKE ? Or only with <> = ?
>Thanks,
>Dmitri.
>"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
>message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Hi Steve,
My parameters that I provide to SQL are never NULL, the string should look
like this:
SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR Param3 LIKE '%Col3%'
This is a very simplified string because actually it's a very long
combination of OR/ANDs and braces. The Param# are never NULL, but if the
value in the column is NULL then I lose this record from the result and this
is a problem. If I had some global setting it would be nice but if I don't
have this way then I need to evaluate all these cells manually and it will
be nightmare because i need to search in 20-30 fields in a few tables, the
tables are long enough and not necessarily all these value are set to
something instead of NULL.
Dmitri.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>I think it only changes the behavior of comparisons that include
> at least one literal or variable that is NULL. Column-to-column
> comparisons between NULLs are never true, regardless of the
> setting. I think <column> LIKE NULL is never true also.
> set ansi_nulls off
> go
> declare @.t table (
> a int,
> c char
> )
> insert into @.t values (null, null)
> insert into @.t values (0, '0')
> select * from @.t
> where a = null
> select * from @.t
> where a = c
> select * from @.t
> where c like null
> go
> set ansi_nulls on
> Steve Kass
> Drew University
> Just D. wrote:
>|||> My parameters that I provide to SQL are never NULL, the string should look
> like this:
>
> SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR
> Param3 LIKE '%Col3%'
> This is a very simplified string because actually it's a very long combina
tion
> of OR/ANDs and braces. The Param# are never NULL, but if the value in the
> column is NULL then I lose this record from the result and this is a problem.[/col
or]
As I see it, there are a couple of solutions. The first, most obvious soluti
on
is to populate those nulls with something (e.g. an empty string (ugh) ) and
set
the column to be non-nullable. Then you won't have to worry about null colum
n
values.
The other solution is to change your where clause to account for nulls like
so
(depending on the actual logic desired):
Select F1...Fn
From Table
Where Param1 Is Null
Or Param1 Like '%Col1%'
Or Param2 Is Null
Or Param2 Like '%Col2%'
Or Param3 Is Null
Or Param3 Like '%Col3%'
> If I had some global setting it would be nice but if I don't have this way
> then I need to evaluate all these cells manually and it will be nightmare
> because i need to search in 20-30 fields in a few tables, the tables are l
ong
> enough and not necessarily all these value are set to something instead of
> NULL.
I don't see that you have a whole lot of choices. It sounds like populating
those fields with something would be your easiest route. I would definitely
not
recommend playing with the ANSI_NULLS option. It will confuse the hell out o
f
any other developer that looks at your code. It is one of those unobvious
"gotchas" that developers hate.
Thomas|||Dmitri,
What you want isn't going to be found in a setting,
if you want NULL LIKE '%Col1%' to be true.
If you want rows where any column is null, along with
the once returned by the query below, try
WHERE COALESCE(Param1,'Col1') LIKE '%Col1%'
OR COALESCE(Param2,'Col2') LIKE '%Col2%'
OR COALESCE(Param3,'Col3') LIKE '%Col3%'
It seems very strange to have columns named ParamX where you
search for values called Col1, so if this is not what you want, it
will be best if you provide sample table data that includes rows
you want (matching LIKE), rows you want (because of NULLs)
and some rows you don't want, so you can explain and show us
what you want to retrieve.
SK
Just D. wrote:

>Hi Steve,
>My parameters that I provide to SQL are never NULL, the string should look
>like this:
>
>SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
>OR Param3 LIKE '%Col3%'
>This is a very simplified string because actually it's a very long
>combination of OR/ANDs and braces. The Param# are never NULL, but if the
>value in the column is NULL then I lose this record from the result and thi
s
>is a problem. If I had some global setting it would be nice but if I don't
>have this way then I need to evaluate all these cells manually and it will
>be nightmare because i need to search in 20-30 fields in a few tables, the
>tables are long enough and not necessarily all these value are set to
>something instead of NULL.
>Dmitri.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>
>
>