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 ?
Showing posts with label concat. Show all posts
Showing posts with label concat. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Concat with Auto-increment column
I have a column with consist of customer number.e.g.
c001
c002
c003
c001
c002
c003
How do I add the character "c" to the auto-incremental number everything I add?
You mean you want to add "c" to your identity column? Well, you cannot prefix an identity column because the column must be numeric - how is it going to increment if it's alpha.
However, you can create a compute column that does the prefixing for you.
e.g.
Code Snippet
create table t(pk int identity primary key, custid as 'c'+right(1000+pk,3));
insert t default values;
insert t default values;
insert t default values;
select * from t;
|||You can try this :
--create a function in that you can make all the conact or incrementations you need
USE [test]
GO
CREATE FUNCTION [dbo].[ConcAuto](@.incr INT)
RETURNS varchar(11)
AS
BEGIN
DECLARE @.Result varchar(11)
SET @.Result = 'C'+cast(@.incr as varchar(10))
RETURN
(
@.Result
)
END
GO
--then create a column , col, as computed column
CREATE TABLE [dbo].[aa](
[id] [int] IDENTITY(1,1) NOT NULL,
[col] AS ([dbo].[ConcAuto]([id])),
[name] [nchar](10) NULL
) ON [secondary]
GO
Concat tables into one row in view
If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.
SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.
single row in table 1.
What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail
I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.
table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated
thx
M@.M@. (mattcushing@.gmail.com) writes:
Quote:
Originally Posted by
If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.
>
What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail
>
I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.
>
table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated
SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql
concat selected values in column of a table with cr/lf
In a column of my table i will concat two selected values (Strings). I do
this with the &-Sign.
The Problem: Is it possible to make a carriage-return and line-feed in a
column ?
--
Message posted via http://www.sqlmonster.comDid you try "Text" & vbcrlf & "Test"?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Holger Schulz via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:2d8df2df1adf408e87dfe4a194b2f8ef@.SQLMonster.com...
> In a column of my table i will concat two selected values (Strings). I do
> this with the &-Sign.
> The Problem: Is it possible to make a carriage-return and line-feed in a
> column ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works fine...
--
Message posted via http://www.sqlmonster.com
this with the &-Sign.
The Problem: Is it possible to make a carriage-return and line-feed in a
column ?
--
Message posted via http://www.sqlmonster.comDid you try "Text" & vbcrlf & "Test"?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Holger Schulz via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:2d8df2df1adf408e87dfe4a194b2f8ef@.SQLMonster.com...
> In a column of my table i will concat two selected values (Strings). I do
> this with the &-Sign.
> The Problem: Is it possible to make a carriage-return and line-feed in a
> column ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works fine...
--
Message posted via http://www.sqlmonster.com
Concat rows into string
I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison
Concat rows into string
I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison
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 w
s 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.
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 w
s of MS Certificate crammingthinks 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.
Concat key Query Question
I have 2 tables with the fields: FiscalYear, Account, Region, Program
I want to treat these values as if they are a concatenated key. I want to
compare
2 tables to see if the one table has any concatenated key in that table that
does not
exist in the other. I need to do this without modifiying the tables with ke
ys
extra fields etc. I want to do this with just Transact SQL and not
using other languages. Any sugestions?
Thanks - EdEd,
You do not need to concatenate columns to do this.
select *
from dbo.t1
where not exists (
select *
from dbo.t2
where
t2.FiscalYear = t1.FiscalYear
and te.Account = t1.Account
and t2.Region = t1.Region
and t2.Program = t1.Program
);
AMB
"Ed" wrote:
> I have 2 tables with the fields: FiscalYear, Account, Region, Program
> I want to treat these values as if they are a concatenated key. I want to
> compare
> 2 tables to see if the one table has any concatenated key in that table th
at
> does not
> exist in the other. I need to do this without modifiying the tables with
keys
> extra fields etc. I want to do this with just Transact SQL and not
> using other languages. Any sugestions?
> Thanks - Ed
>|||>> I have 2 tables with the fields [sic]: FiscalYear, Account, Region, Program <
<
Columns are not fields; you are going to screw up a lot things until
you learn that. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications. It is very hard to debug code when you do
not let us see it.
Then there is the question as to why you have two tables with the same
structure, in violation of some basic RDBMS rules? This is a pretty
good sign that you have serious atrtribute splitting problems and a
non-relational schema.
There is no such term in RDBMS, or in SQL. Did you mean a compound
key? You still think that data is physically contigous and stored as
text -- the COBOL model!
QL and not using other languages. Any sugestions? <<
The *right* answer is to combine these vague tables into a single table
with a column for the values of the attribute you used to split them.
The kludge is below -- it also gives some ideas about the ISO-11179
rules for data element names that you did not follow:
SELECT S1.*, S2.*
FROM SplitNamelessTable AS S1
FULL OUTER JOIN
SplitNamelessTable AS S2
ON S1.fiscalyear = S2.fiscalyear
AND S1.foobar_account = S2.foobar_account
AND S1.region_id = S1.region_id
AND S1.program_name = S2.program_name
WHERE COALESCE (S1.fiscalyear, S1.foobar_account, S1.region_id,
S1.program_name) IS NULL
OR COALESCE (S2.fiscalyear, S2.foobar_account, S2.region_id,
S2.program_name) IS NULL;
Since you did not bother to tell us about NULLs and how they affect
matching rules, data types and all that other *vital information*, this
is only a guess.
There is also a version with EXISTS() predicates that has been posted
several times.|||Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B)
SQL Server 2005's readable version of Joe's Solution.
(Select * from A
UNION
Select * from B)
EXCEPT
(select * from A
INTERSECT
select * from B)
Untested, but should work :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B
) .. SQL Server 2005's readable version of Joe's Solution. <<
Ands the SQL-92 version would be
SELECT * FROM A OUTER UNION SELECT * FROM B;
but n obody has implemented the OUTER UNION.sqlsql
I want to treat these values as if they are a concatenated key. I want to
compare
2 tables to see if the one table has any concatenated key in that table that
does not
exist in the other. I need to do this without modifiying the tables with ke
ys
extra fields etc. I want to do this with just Transact SQL and not
using other languages. Any sugestions?
Thanks - EdEd,
You do not need to concatenate columns to do this.
select *
from dbo.t1
where not exists (
select *
from dbo.t2
where
t2.FiscalYear = t1.FiscalYear
and te.Account = t1.Account
and t2.Region = t1.Region
and t2.Program = t1.Program
);
AMB
"Ed" wrote:
> I have 2 tables with the fields: FiscalYear, Account, Region, Program
> I want to treat these values as if they are a concatenated key. I want to
> compare
> 2 tables to see if the one table has any concatenated key in that table th
at
> does not
> exist in the other. I need to do this without modifiying the tables with
keys
> extra fields etc. I want to do this with just Transact SQL and not
> using other languages. Any sugestions?
> Thanks - Ed
>|||>> I have 2 tables with the fields [sic]: FiscalYear, Account, Region, Program <
<
Columns are not fields; you are going to screw up a lot things until
you learn that. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications. It is very hard to debug code when you do
not let us see it.
Then there is the question as to why you have two tables with the same
structure, in violation of some basic RDBMS rules? This is a pretty
good sign that you have serious atrtribute splitting problems and a
non-relational schema.
There is no such term in RDBMS, or in SQL. Did you mean a compound
key? You still think that data is physically contigous and stored as
text -- the COBOL model!
QL and not using other languages. Any sugestions? <<
The *right* answer is to combine these vague tables into a single table
with a column for the values of the attribute you used to split them.
The kludge is below -- it also gives some ideas about the ISO-11179
rules for data element names that you did not follow:
SELECT S1.*, S2.*
FROM SplitNamelessTable AS S1
FULL OUTER JOIN
SplitNamelessTable AS S2
ON S1.fiscalyear = S2.fiscalyear
AND S1.foobar_account = S2.foobar_account
AND S1.region_id = S1.region_id
AND S1.program_name = S2.program_name
WHERE COALESCE (S1.fiscalyear, S1.foobar_account, S1.region_id,
S1.program_name) IS NULL
OR COALESCE (S2.fiscalyear, S2.foobar_account, S2.region_id,
S2.program_name) IS NULL;
Since you did not bother to tell us about NULLs and how they affect
matching rules, data types and all that other *vital information*, this
is only a guess.
There is also a version with EXISTS() predicates that has been posted
several times.|||Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B)
SQL Server 2005's readable version of Joe's Solution.
(Select * from A
UNION
Select * from B)
EXCEPT
(select * from A
INTERSECT
select * from B)
Untested, but should work :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B
) .. SQL Server 2005's readable version of Joe's Solution. <<
Ands the SQL-92 version would be
SELECT * FROM A OUTER UNION SELECT * FROM B;
but n obody has implemented the OUTER UNION.sqlsql
Concat int with string
Hi,
I am trying to concat an int with an nvarchar in my select:
select distinct(id + name)
from load
where name not like '%test%'
but I am getting an error saying:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.
My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.
Thanks for any help.
Mongodid you try this --
select distinct(cast(id as nvarchar) + name) ...
I am trying to concat an int with an nvarchar in my select:
select distinct(id + name)
from load
where name not like '%test%'
but I am getting an error saying:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.
My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.
Thanks for any help.
Mongodid you try this --
select distinct(cast(id as nvarchar) + name) ...
Concat instead of SUM when grouping results
Hello,
Hey, thank you very much, it works :). The only problem is that it
lasts more than 10 s to execute it and that with only 5 records :(.
Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy :). Thank you very much!
>
Do it in the front end instead violating 1NF in the Database side.
Hi,
>>
>Do it in the front end instead violating 1NF in the Database side.
>
Hi,
>
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
You're "wrong".
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you design your database properly, your queries follow from that.
Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?
>
>
>
>
You're "wrong".
>
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
>
If you design your database properly, your queries follow from that.
I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.
>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
>
As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.
It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...
I have a very simple problem which I will illustrate with an example:
I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K
I now want to group them and the result has to be:
A 1,2,3 C
B 8,9 K
So the results in the second row have to be concatenated. I guess
there is no function to do this... What is the simplest solution?
Kind regards,
Bart WarnezHi Bart,
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
DROP TABLE test
which seems to work :)
Good luck!
J|||On 23 nov, 12:52, jhofm...@.googlemail.com wrote:
Quote:
Originally Posted by
Hi Bart,
>
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
>
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
>
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
>
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
>
DROP TABLE test
>
which seems to work :)
>
Good luck!
J
Hey, thank you very much, it works :). The only problem is that it
lasts more than 10 s to execute it and that with only 5 records :(.
Kind Regards,
Bart|||I have also tried out the solution below (with the same test-table),
with a function. But again the response time is very slow...
create function dbo.fn_groupIt(@.test1 varchar(5),@.test3 varchar(5))
returns varchar(5000)
as
begin
declare @.out varchar(5000)
select@.out = coalesce(@.out + ',' + convert(varchar,test2),
convert(varchar,test2))
fromtest
wheretest1 = @.test1 and
test3 = @.test3
return @.out
end
selecttest1, dbo.fn_groupIt(test1,test3) test2,test3
from(
selecttest1,test3
fromtest
group by test1,test3
) a|||Hi Bart,
What spec server are you using? I can run either script in under a
second :-/
J|||On 23 nov, 15:46, jhofm...@.googlemail.com wrote:
Quote:
Originally Posted by
Hi Bart,
>
What spec server are you using? I can run either script in under a
second :-/
>
J
Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy :). Thank you very much!
Bart|||>I guess there is no function to do this... What is the simplest solution? <<
Do it in the front end instead violating 1NF in the Database side.|||On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
I guess there is no function to do this... What is the simplest solution? <<
>
Do it in the front end instead violating 1NF in the Database side.
Hi,
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
Kind regards,
Bart|||"Bart op de grote markt" <warnezb@.googlemail.comwrote in message
news:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
Quote:
Originally Posted by
On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
>I guess there is no function to do this... What is the simplest
>solution? <<
>>
>Do it in the front end instead violating 1NF in the Database side.
>
Hi,
>
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
You're "wrong".
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
If you design your database properly, your queries follow from that.
Quote:
Originally Posted by
>
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
Quote:
Originally Posted by
>
Kind regards,
>
Bart
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you design your database properly, your queries follow from that.
This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.
Quote:
Originally Posted by
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?
J|||On 26 nov, 14:09, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:
Quote:
Originally Posted by
"Bart op de grote markt" <warn...@.googlemail.comwrote in messagenews:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
>
Quote:
Originally Posted by
On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
I guess there is no function to do this... What is the simplest
solution? <<
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Do it in the front end instead violating 1NF in the Database side.
>
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
>
You're "wrong".
>
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
>
If you design your database properly, your queries follow from that.
I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.
And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )
Quote:
Originally Posted by
Quote:
Originally Posted by
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
>
As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.
Kind regards and thx for all your comments
Bart|||You're "wrong".
Actually Greg - You're "wrong".
SQL Server is a data engine and not just a relational data storage method.
There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.
For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.
There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.
Quote:
Originally Posted by
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
CONCAT in Execute SQL Task
I have a package that uses an event error to store msgs into a field. But, I end up getting several errors that will overwrite my db field each time. Only about two out of 10 are relevent to my problem...
I just need to know if there is a way to suppress the number of event errors that come over , or concat them into one event, per event that occurs? Right now when I hit an error, I end up getting 10 error messages that follow, so my OnError event gets triggered 10 times..
Jason
One way to handle this may be to count the errors using a script in your event handler. Update a variable until you reach the last error you wish to handle, then enable the behaviour you wish to execute.
Donald Farmer
Group Program Manager
SQL Server Integration Services
|||Thanks Donald, how do I determine when the final error occurs in a failure?
So, let's say I have an object that fails, I get 5 onerror event called msgs, how do I know that 5 (or whatever number) is my last error msg for the failure?|||
Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:
UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?
where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?
|||scoobyjw wrote:
Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:
UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?
where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?
Jason,
Why not try building the SQL statement using a property expression on the SQLStatementSource property?
-Jamie|||The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]
and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:
if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?
? Sorry about the pseudo code, I am not an expert at SQL.|||
The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?] and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:
if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?? Sorry about the pseudo code, I am not an expert at SQL.
Yeah, try this:
[UPDATE ETL_Transactions SET LogDetails = COALESCE(LogDetails, '') + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]
I really think you should look at using a property expression tho
-Jamie
CONCAT function + SQL Sever 2005
I cant find a clear answer to the syntax of concat in for SQL 2005. Can anyone help.it's still the same old plus sign|||I figured out what I was doing wrong. I used text as my data type for the two fields I was trying to CONCAT. So when I did First_name + ' ' + Last_name is was treating the two columns as text and the blank space as a varchar. So i would execute.|||don't use the text type for such small fields as first and last names. text is for big blobs of text longer than 8000 chars. I don't know anyone with such a long name. :)
and in 2005, don't use text at all. use varchar(max) or nvarchar(max).
and in 2005, don't use text at all. use varchar(max) or nvarchar(max).
CONCAT function
Apologies for the glib answer but the concatinate function
you are looking for is the '+' that you already have.
The function that you are talking about (CONCAT) will work
however unless you are going to have a different number of
parameters then its not really worth it, and even then you
will need a bit of fancy code.
If it is then go ahead, but there is not a built in one.
if you have any further questions then please feel fee to
email me at peternolan67@.REMOVETHIS@.hotmail.com
Peter
Adam and Eve had many advantages but the principal one was
that they escaped teething.
Mark Twain
>--Original Message--
>Does SQL Server 2000 support the CONCAT SQL function?
>For example:
>
>-> 'SQLServer'
>I know it can be written as this:
>SELECT 'SQL' + 'Ser' + 'ver';
>
>My thoughts are to create a user-based function called
CONCAT. Are other
>thoughts on this?
>Thanks!
>.
>I tried to write this user-based function called CONCAT, yet is not working
properly. Only the first character is returned.
CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
RETURNS nvarchar AS
BEGIN
DECLARE @.fullstring nvarchar;
SELECT @.fullstring = @.string1 + @.string2;
return @.fullstring;
END
select dbo.concat('aaaa','bbbb')
> 'a'
"Peter The Spate" wrote:
> Apologies for the glib answer but the concatinate function
> you are looking for is the '+' that you already have.
> The function that you are talking about (CONCAT) will work
> however unless you are going to have a different number of
> parameters then its not really worth it, and even then you
> will need a bit of fancy code.
> If it is then go ahead, but there is not a built in one.
> if you have any further questions then please feel fee to
> email me at peternolan67@.REMOVETHIS@.hotmail.com
> Peter
> Adam and Eve had many advantages but the principal one was
> that they escaped teething.
> Mark Twain
>
> CONCAT. Are other
>|||You have to define a length for the nvarchar variables
/*for example*/
@.string1 nvarchar(100)
Keith
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> I tried to write this user-based function called CONCAT, yet is not
working[vbcol=seagreen]
> properly. Only the first character is returned.
> CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
> RETURNS nvarchar AS
> BEGIN
> DECLARE @.fullstring nvarchar;
> SELECT @.fullstring = @.string1 + @.string2;
> return @.fullstring;
> END
>
> select dbo.concat('aaaa','bbbb')
>
> "Peter The Spate" wrote:
>|||I granted execute to a user called ENTRY, yet the user has to explicit state
the owner when making the call:
This fails:
select concat('a','b')
Server: Msg 195, Level 15, State 10, Line 1
'concat' is not a recognized function name.
This works
select dbo.concat('a'b')
>ab
How do I allow the user to use the dbo owned function without specifying the
owner?
Thanks!
"Keith Kratochvil" wrote:
> You have to define a length for the nvarchar variables
> /*for example*/
> @.string1 nvarchar(100)
> --
> Keith
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> working
>|||> How do I allow the user to use the dbo owned function without specifying then">
> owner?
You cannot. The must specify the owner and function name in order to use
a user defined function.
Gert-Jan|||Books Online states this:
If an object is not qualified with the object owner when it is referenced
(for example, my_table instead of owner.my_table), SQL Server looks for an
object in the database in the following order:
Owned by the current user.
Owned by dbo.
According to this, the dbo function should be found. I this documentation
not correct?
"Gert-Jan Strik" wrote:
> You cannot. The must specify the owner and function name in order to use
> a user defined function.
> Gert-Jan
>|||However, user definined functions are a special case, different from other
objects. ALL user defined functions must include the owner name, even if it
is the current user.
Please read about User Defined Functions in the Books Online.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...[vbcol=seagreen]
> Books Online states this:
> If an object is not qualified with the object owner when it is referenced
> (for example, my_table instead of owner.my_table), SQL Server looks for an
> object in the database in the following order:
> Owned by the current user.
> Owned by dbo.
>
> According to this, the dbo function should be found. I this documentation
> not correct?
>
> "Gert-Jan Strik" wrote:
>
specifying the[vbcol=seagreen]|||Is there any workaround to this? Can I trick the database into thinking this
is a system function?
"Kalen Delaney" wrote:
> However, user definined functions are a special case, different from other
> objects. ALL user defined functions must include the owner name, even if i
t
> is the current user.
> Please read about User Defined Functions in the Books Online.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...
> specifying the
>
>|||No, the system functions are actually not objects in the normal sense. They
are almost like built in commands: substring, getdate, power, etc. The
system functions do not exist in any system tables, and their definitions
are not available in the TSQL language. There is no way to have the names
you give your UDFs be recognized by the parser, unless you get in and change
SQL Server's source code. In fact, supplying the owner name the way the
parser can tell that you are indicating a UDF, and not something else, like
a system function. Leaving off the owner name makes the parser think you are
specifying a system function, but then it realizes it has no built in
function of that name, so you get the error.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:7AEA29F7-74D3-468A-9711-4829E36C3437@.microsoft.com...
> Is there any workaround to this? Can I trick the database into thinking
this[vbcol=seagreen]
> is a system function?
> "Kalen Delaney" wrote:
>
other[vbcol=seagreen]
it[vbcol=seagreen]
referenced[vbcol=seagreen]
for an[vbcol=seagreen]
documentation[vbcol=seagreen]
use[vbcol=seagreen]sqlsql
you are looking for is the '+' that you already have.
The function that you are talking about (CONCAT) will work
however unless you are going to have a different number of
parameters then its not really worth it, and even then you
will need a bit of fancy code.
If it is then go ahead, but there is not a built in one.
if you have any further questions then please feel fee to
email me at peternolan67@.REMOVETHIS@.hotmail.com
Peter
Adam and Eve had many advantages but the principal one was
that they escaped teething.
Mark Twain
>--Original Message--
>Does SQL Server 2000 support the CONCAT SQL function?
>For example:
>
>-> 'SQLServer'
>I know it can be written as this:
>SELECT 'SQL' + 'Ser' + 'ver';
>
>My thoughts are to create a user-based function called
CONCAT. Are other
>thoughts on this?
>Thanks!
>.
>I tried to write this user-based function called CONCAT, yet is not working
properly. Only the first character is returned.
CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
RETURNS nvarchar AS
BEGIN
DECLARE @.fullstring nvarchar;
SELECT @.fullstring = @.string1 + @.string2;
return @.fullstring;
END
select dbo.concat('aaaa','bbbb')
> 'a'
"Peter The Spate" wrote:
> Apologies for the glib answer but the concatinate function
> you are looking for is the '+' that you already have.
> The function that you are talking about (CONCAT) will work
> however unless you are going to have a different number of
> parameters then its not really worth it, and even then you
> will need a bit of fancy code.
> If it is then go ahead, but there is not a built in one.
> if you have any further questions then please feel fee to
> email me at peternolan67@.REMOVETHIS@.hotmail.com
> Peter
> Adam and Eve had many advantages but the principal one was
> that they escaped teething.
> Mark Twain
>
> CONCAT. Are other
>|||You have to define a length for the nvarchar variables
/*for example*/
@.string1 nvarchar(100)
Keith
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> I tried to write this user-based function called CONCAT, yet is not
working[vbcol=seagreen]
> properly. Only the first character is returned.
> CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
> RETURNS nvarchar AS
> BEGIN
> DECLARE @.fullstring nvarchar;
> SELECT @.fullstring = @.string1 + @.string2;
> return @.fullstring;
> END
>
> select dbo.concat('aaaa','bbbb')
>
> "Peter The Spate" wrote:
>|||I granted execute to a user called ENTRY, yet the user has to explicit state
the owner when making the call:
This fails:
select concat('a','b')
Server: Msg 195, Level 15, State 10, Line 1
'concat' is not a recognized function name.
This works
select dbo.concat('a'b')
>ab
How do I allow the user to use the dbo owned function without specifying the
owner?
Thanks!
"Keith Kratochvil" wrote:
> You have to define a length for the nvarchar variables
> /*for example*/
> @.string1 nvarchar(100)
> --
> Keith
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> working
>|||> How do I allow the user to use the dbo owned function without specifying then">
> owner?
You cannot. The must specify the owner and function name in order to use
a user defined function.
Gert-Jan|||Books Online states this:
If an object is not qualified with the object owner when it is referenced
(for example, my_table instead of owner.my_table), SQL Server looks for an
object in the database in the following order:
Owned by the current user.
Owned by dbo.
According to this, the dbo function should be found. I this documentation
not correct?
"Gert-Jan Strik" wrote:
> You cannot. The must specify the owner and function name in order to use
> a user defined function.
> Gert-Jan
>|||However, user definined functions are a special case, different from other
objects. ALL user defined functions must include the owner name, even if it
is the current user.
Please read about User Defined Functions in the Books Online.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...[vbcol=seagreen]
> Books Online states this:
> If an object is not qualified with the object owner when it is referenced
> (for example, my_table instead of owner.my_table), SQL Server looks for an
> object in the database in the following order:
> Owned by the current user.
> Owned by dbo.
>
> According to this, the dbo function should be found. I this documentation
> not correct?
>
> "Gert-Jan Strik" wrote:
>
specifying the[vbcol=seagreen]|||Is there any workaround to this? Can I trick the database into thinking this
is a system function?
"Kalen Delaney" wrote:
> However, user definined functions are a special case, different from other
> objects. ALL user defined functions must include the owner name, even if i
t
> is the current user.
> Please read about User Defined Functions in the Books Online.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...
> specifying the
>
>|||No, the system functions are actually not objects in the normal sense. They
are almost like built in commands: substring, getdate, power, etc. The
system functions do not exist in any system tables, and their definitions
are not available in the TSQL language. There is no way to have the names
you give your UDFs be recognized by the parser, unless you get in and change
SQL Server's source code. In fact, supplying the owner name the way the
parser can tell that you are indicating a UDF, and not something else, like
a system function. Leaving off the owner name makes the parser think you are
specifying a system function, but then it realizes it has no built in
function of that name, so you get the error.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:7AEA29F7-74D3-468A-9711-4829E36C3437@.microsoft.com...
> Is there any workaround to this? Can I trick the database into thinking
this[vbcol=seagreen]
> is a system function?
> "Kalen Delaney" wrote:
>
other[vbcol=seagreen]
it[vbcol=seagreen]
referenced[vbcol=seagreen]
for an[vbcol=seagreen]
documentation[vbcol=seagreen]
use[vbcol=seagreen]sqlsql
CONCAT function
Apologies for the glib answer but the concatinate function
you are looking for is the '+' that you already have.
The function that you are talking about (CONCAT) will work
however unless you are going to have a different number of
parameters then its not really worth it, and even then you
will need a bit of fancy code.
If it is then go ahead, but there is not a built in one.
if you have any further questions then please feel fee to
email me at peternolan67@.REMOVETHIS@.hotmail.com
Peter
Adam and Eve had many advantages but the principal one was
that they escaped teething.
Mark Twain
>--Original Message--
>Does SQL Server 2000 support the CONCAT SQL function?
>For example:
>-> 'SQLServer'
>I know it can be written as this:
>SELECT 'SQL' + 'Ser' + 'ver';
>
>My thoughts are to create a user-based function called
CONCAT. Are other
>thoughts on this?
>Thanks!
>.
>
I tried to write this user-based function called CONCAT, yet is not working
properly. Only the first character is returned.
CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
RETURNS nvarchar AS
BEGIN
DECLARE @.fullstring nvarchar;
SELECT @.fullstring = @.string1 + @.string2;
return @.fullstring;
END
select dbo.concat('aaaa','bbbb')
> 'a'
"Peter The Spate" wrote:
> Apologies for the glib answer but the concatinate function
> you are looking for is the '+' that you already have.
> The function that you are talking about (CONCAT) will work
> however unless you are going to have a different number of
> parameters then its not really worth it, and even then you
> will need a bit of fancy code.
> If it is then go ahead, but there is not a built in one.
> if you have any further questions then please feel fee to
> email me at peternolan67@.REMOVETHIS@.hotmail.com
> Peter
> Adam and Eve had many advantages but the principal one was
> that they escaped teething.
> Mark Twain
>
> CONCAT. Are other
>
|||You have to define a length for the nvarchar variables
/*for example*/
@.string1 nvarchar(100)
Keith
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> I tried to write this user-based function called CONCAT, yet is not
working[vbcol=seagreen]
> properly. Only the first character is returned.
> CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
> RETURNS nvarchar AS
> BEGIN
> DECLARE @.fullstring nvarchar;
> SELECT @.fullstring = @.string1 + @.string2;
> return @.fullstring;
> END
>
> select dbo.concat('aaaa','bbbb')
>
> "Peter The Spate" wrote:
|||I granted execute to a user called ENTRY, yet the user has to explicit state
the owner when making the call:
This fails:
select concat('a','b')
Server: Msg 195, Level 15, State 10, Line 1
'concat' is not a recognized function name.
This works
select dbo.concat('a'b')
>ab
How do I allow the user to use the dbo owned function without specifying the
owner?
Thanks!
"Keith Kratochvil" wrote:
> You have to define a length for the nvarchar variables
> /*for example*/
> @.string1 nvarchar(100)
> --
> Keith
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> working
>
|||> How do I allow the user to use the dbo owned function without specifying the
> owner?
You cannot. The must specify the owner and function name in order to use
a user defined function.
Gert-Jan
|||Books Online states this:
If an object is not qualified with the object owner when it is referenced
(for example, my_table instead of owner.my_table), SQL Server looks for an
object in the database in the following order:
Owned by the current user.
Owned by dbo.
According to this, the dbo function should be found. I this documentation
not correct?
"Gert-Jan Strik" wrote:
> You cannot. The must specify the owner and function name in order to use
> a user defined function.
> Gert-Jan
>
|||However, user definined functions are a special case, different from other
objects. ALL user defined functions must include the owner name, even if it
is the current user.
Please read about User Defined Functions in the Books Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...[vbcol=seagreen]
> Books Online states this:
> If an object is not qualified with the object owner when it is referenced
> (for example, my_table instead of owner.my_table), SQL Server looks for an
> object in the database in the following order:
> Owned by the current user.
> Owned by dbo.
>
> According to this, the dbo function should be found. I this documentation
> not correct?
>
> "Gert-Jan Strik" wrote:
specifying the[vbcol=seagreen]
|||Is there any workaround to this? Can I trick the database into thinking this
is a system function?
"Kalen Delaney" wrote:
> However, user definined functions are a special case, different from other
> objects. ALL user defined functions must include the owner name, even if it
> is the current user.
> Please read about User Defined Functions in the Books Online.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...
> specifying the
>
>
|||No, the system functions are actually not objects in the normal sense. They
are almost like built in commands: substring, getdate, power, etc. The
system functions do not exist in any system tables, and their definitions
are not available in the TSQL language. There is no way to have the names
you give your UDFs be recognized by the parser, unless you get in and change
SQL Server's source code. In fact, supplying the owner name the way the
parser can tell that you are indicating a UDF, and not something else, like
a system function. Leaving off the owner name makes the parser think you are
specifying a system function, but then it realizes it has no built in
function of that name, so you get the error.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:7AEA29F7-74D3-468A-9711-4829E36C3437@.microsoft.com...
> Is there any workaround to this? Can I trick the database into thinking
this[vbcol=seagreen]
> is a system function?
> "Kalen Delaney" wrote:
other[vbcol=seagreen]
it[vbcol=seagreen]
referenced[vbcol=seagreen]
for an[vbcol=seagreen]
documentation[vbcol=seagreen]
use[vbcol=seagreen]
you are looking for is the '+' that you already have.
The function that you are talking about (CONCAT) will work
however unless you are going to have a different number of
parameters then its not really worth it, and even then you
will need a bit of fancy code.
If it is then go ahead, but there is not a built in one.
if you have any further questions then please feel fee to
email me at peternolan67@.REMOVETHIS@.hotmail.com
Peter
Adam and Eve had many advantages but the principal one was
that they escaped teething.
Mark Twain
>--Original Message--
>Does SQL Server 2000 support the CONCAT SQL function?
>For example:
>-> 'SQLServer'
>I know it can be written as this:
>SELECT 'SQL' + 'Ser' + 'ver';
>
>My thoughts are to create a user-based function called
CONCAT. Are other
>thoughts on this?
>Thanks!
>.
>
I tried to write this user-based function called CONCAT, yet is not working
properly. Only the first character is returned.
CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
RETURNS nvarchar AS
BEGIN
DECLARE @.fullstring nvarchar;
SELECT @.fullstring = @.string1 + @.string2;
return @.fullstring;
END
select dbo.concat('aaaa','bbbb')
> 'a'
"Peter The Spate" wrote:
> Apologies for the glib answer but the concatinate function
> you are looking for is the '+' that you already have.
> The function that you are talking about (CONCAT) will work
> however unless you are going to have a different number of
> parameters then its not really worth it, and even then you
> will need a bit of fancy code.
> If it is then go ahead, but there is not a built in one.
> if you have any further questions then please feel fee to
> email me at peternolan67@.REMOVETHIS@.hotmail.com
> Peter
> Adam and Eve had many advantages but the principal one was
> that they escaped teething.
> Mark Twain
>
> CONCAT. Are other
>
|||You have to define a length for the nvarchar variables
/*for example*/
@.string1 nvarchar(100)
Keith
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> I tried to write this user-based function called CONCAT, yet is not
working[vbcol=seagreen]
> properly. Only the first character is returned.
> CREATE FUNCTION CONCAT (@.string1 nvarchar, @.string2 nvarchar)
> RETURNS nvarchar AS
> BEGIN
> DECLARE @.fullstring nvarchar;
> SELECT @.fullstring = @.string1 + @.string2;
> return @.fullstring;
> END
>
> select dbo.concat('aaaa','bbbb')
>
> "Peter The Spate" wrote:
|||I granted execute to a user called ENTRY, yet the user has to explicit state
the owner when making the call:
This fails:
select concat('a','b')
Server: Msg 195, Level 15, State 10, Line 1
'concat' is not a recognized function name.
This works
select dbo.concat('a'b')
>ab
How do I allow the user to use the dbo owned function without specifying the
owner?
Thanks!
"Keith Kratochvil" wrote:
> You have to define a length for the nvarchar variables
> /*for example*/
> @.string1 nvarchar(100)
> --
> Keith
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:8E24AC41-ECE8-4E96-A716-5700F2C5BE50@.microsoft.com...
> working
>
|||> How do I allow the user to use the dbo owned function without specifying the
> owner?
You cannot. The must specify the owner and function name in order to use
a user defined function.
Gert-Jan
|||Books Online states this:
If an object is not qualified with the object owner when it is referenced
(for example, my_table instead of owner.my_table), SQL Server looks for an
object in the database in the following order:
Owned by the current user.
Owned by dbo.
According to this, the dbo function should be found. I this documentation
not correct?
"Gert-Jan Strik" wrote:
> You cannot. The must specify the owner and function name in order to use
> a user defined function.
> Gert-Jan
>
|||However, user definined functions are a special case, different from other
objects. ALL user defined functions must include the owner name, even if it
is the current user.
Please read about User Defined Functions in the Books Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...[vbcol=seagreen]
> Books Online states this:
> If an object is not qualified with the object owner when it is referenced
> (for example, my_table instead of owner.my_table), SQL Server looks for an
> object in the database in the following order:
> Owned by the current user.
> Owned by dbo.
>
> According to this, the dbo function should be found. I this documentation
> not correct?
>
> "Gert-Jan Strik" wrote:
specifying the[vbcol=seagreen]
|||Is there any workaround to this? Can I trick the database into thinking this
is a system function?
"Kalen Delaney" wrote:
> However, user definined functions are a special case, different from other
> objects. ALL user defined functions must include the owner name, even if it
> is the current user.
> Please read about User Defined Functions in the Books Online.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:CA4058D0-9569-4602-AB81-BB525A75456E@.microsoft.com...
> specifying the
>
>
|||No, the system functions are actually not objects in the normal sense. They
are almost like built in commands: substring, getdate, power, etc. The
system functions do not exist in any system tables, and their definitions
are not available in the TSQL language. There is no way to have the names
you give your UDFs be recognized by the parser, unless you get in and change
SQL Server's source code. In fact, supplying the owner name the way the
parser can tell that you are indicating a UDF, and not something else, like
a system function. Leaving off the owner name makes the parser think you are
specifying a system function, but then it realizes it has no built in
function of that name, so you get the error.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:7AEA29F7-74D3-468A-9711-4829E36C3437@.microsoft.com...
> Is there any workaround to this? Can I trick the database into thinking
this[vbcol=seagreen]
> is a system function?
> "Kalen Delaney" wrote:
other[vbcol=seagreen]
it[vbcol=seagreen]
referenced[vbcol=seagreen]
for an[vbcol=seagreen]
documentation[vbcol=seagreen]
use[vbcol=seagreen]
ConCat data
I need to create a csv file where it's just one giant file.
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit
about your comment regarding CSV in your first
post. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit about your comment regarding CSV in your first
> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit
about your comment regarding CSV in your firstpost. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit
about your comment regarding CSV in your first> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>
Concat columns
I have to do some paging stuff. I am doing the follow in a proc.
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:
> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:
>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:
> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:
> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:
>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:
> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>
Concat and convert datetime
Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
Concat and convert datetime
Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
Imran
Can you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
sqlsql
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
Imran
Can you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
sqlsql
Concat and convert datetime
Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> > Hi,
> > I have two columns name indate and intime. Both of these fields are in
> > varchar type. I need to concat two such a way that it looks like a
> datetime
> > type. Or if there is a way to convert this varchar type into datetime
> > datatype then that would work too.
> >
> > Thanks.
> > Imran
> >
>
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> > Hi,
> > I have two columns name indate and intime. Both of these fields are in
> > varchar type. I need to concat two such a way that it looks like a
> datetime
> > type. Or if there is a way to convert this varchar type into datetime
> > datatype then that would work too.
> >
> > Thanks.
> > Imran
> >
>
concat all col2 values for each col1, and add sum(col3) (was "query help")
Hi,
Can anybody help me to create a single query? I have this problem.
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)
INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)
I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)
(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50
Any help would be greatly appreciated !!(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)
SET NOCOUNT ON
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)
INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)
SELECT Col1,
Col2a,
Col2b,
Col2c,
SUM(Col3) AS TheTotal
FROM --Pivot data
(SELECT TOP 100 PERCENT
Col1,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
ORDER BY
B.Col2) AS Col2a,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 1 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)
ORDER BY
B.Col2) AS Col2b,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 2 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)) AS Col2c,
Col3
FROM dbo.t1 AS A
ORDER BY
Col1,
Col2) AS DerT
GROUP BY
Col1,
Col2a,
Col2b,
Col2c
DROP TABLE t1
SET NOCOUNT OFF
HTH|||Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)
Awesome, as usual...thanks a ton Pootie!!:rolleyes:|||nevermind, you have a better solution above.|||As a possible alternative, blindman's neat function here could be adapted, resulting in a much simpler query:
SELECT col1, dbo.Concat_ICD(col1) as TheNames, Sum(col3) as TheTotal
FROM t1
GROUP BY col1
http://www.dbforums.com/showthread.php?t=1605725
This would not produce the visible NULL in the result, but I was presuming that wasn't a requirement.|||The function is your best solutions, because it works for any number of records.
By the way, I wish I could take credit for that function, but it is actually one of the many things I have learned from participating in this forum over that past few years.|||Actually that was the solution I hoped to use - it allows n values to be concatenated. However I read the requirement as the return putting the names into three columns rather than one. If this isn't a requirement then defo go with Blindman's solution.|||I wanted it in three different columns.So I used Pootie's one.
Anyways,Thanks everybody for their valuable info.|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)|||I'm calling you on that one Rudy. Did you read the requirements carefully?|||...but here is a shorter method of coding it for SQL Server:SET NOCOUNT ON
CREATE TABLE #t1
(col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT)
INSERT INTO #t1 VALUES('A001','Tom',30)
INSERT INTO #t1 VALUES('A001','Rick',40)
INSERT INTO #t1 VALUES('A001','Harry',10)
INSERT INTO #t1 VALUES('A002','Peter',50)
INSERT INTO #t1 VALUES('A002','Sam',50)
INSERT INTO #t1 VALUES('A003','Fred',50)
select A.col1,
min(A.col2) as name1,
min(B.col2) as name2,
min(C.col2) as name3,
max(coalesce(A.col3, 0) + coalesce(B.col3, 0) + coalesce(C.col3, 0)) as col3total
from #t1 A
left outer join #t1 B on A.col1 = B.col1 and A.col2 < B.col2
left outer join #t1 C on B.col1 = C.col1 and B.col2 < C.col2
group by A.col1
drop table #t1|||I'm calling you on that one Rudy. Did you read the requirements carefully?
oh, SHEEEEEEEESH, okay :S
select col1
, group_concat(col2 separator ' ')
, sum(col3)
from t1
group
by col1|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)
...and that would be just great if it was a real ANSI compliant databa...
Oh, never mind|||pot? meet kettle
kettle? meet pot
:p|||That was great,thank you Blindman.And thank you all of you for your help.
Can anybody help me to create a single query? I have this problem.
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)
INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)
I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)
(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50
Any help would be greatly appreciated !!(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)
SET NOCOUNT ON
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)
INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)
SELECT Col1,
Col2a,
Col2b,
Col2c,
SUM(Col3) AS TheTotal
FROM --Pivot data
(SELECT TOP 100 PERCENT
Col1,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
ORDER BY
B.Col2) AS Col2a,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 1 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)
ORDER BY
B.Col2) AS Col2b,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 2 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)) AS Col2c,
Col3
FROM dbo.t1 AS A
ORDER BY
Col1,
Col2) AS DerT
GROUP BY
Col1,
Col2a,
Col2b,
Col2c
DROP TABLE t1
SET NOCOUNT OFF
HTH|||Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)
Awesome, as usual...thanks a ton Pootie!!:rolleyes:|||nevermind, you have a better solution above.|||As a possible alternative, blindman's neat function here could be adapted, resulting in a much simpler query:
SELECT col1, dbo.Concat_ICD(col1) as TheNames, Sum(col3) as TheTotal
FROM t1
GROUP BY col1
http://www.dbforums.com/showthread.php?t=1605725
This would not produce the visible NULL in the result, but I was presuming that wasn't a requirement.|||The function is your best solutions, because it works for any number of records.
By the way, I wish I could take credit for that function, but it is actually one of the many things I have learned from participating in this forum over that past few years.|||Actually that was the solution I hoped to use - it allows n values to be concatenated. However I read the requirement as the return putting the names into three columns rather than one. If this isn't a requirement then defo go with Blindman's solution.|||I wanted it in three different columns.So I used Pootie's one.
Anyways,Thanks everybody for their valuable info.|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)|||I'm calling you on that one Rudy. Did you read the requirements carefully?|||...but here is a shorter method of coding it for SQL Server:SET NOCOUNT ON
CREATE TABLE #t1
(col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT)
INSERT INTO #t1 VALUES('A001','Tom',30)
INSERT INTO #t1 VALUES('A001','Rick',40)
INSERT INTO #t1 VALUES('A001','Harry',10)
INSERT INTO #t1 VALUES('A002','Peter',50)
INSERT INTO #t1 VALUES('A002','Sam',50)
INSERT INTO #t1 VALUES('A003','Fred',50)
select A.col1,
min(A.col2) as name1,
min(B.col2) as name2,
min(C.col2) as name3,
max(coalesce(A.col3, 0) + coalesce(B.col3, 0) + coalesce(C.col3, 0)) as col3total
from #t1 A
left outer join #t1 B on A.col1 = B.col1 and A.col2 < B.col2
left outer join #t1 C on B.col1 = C.col1 and B.col2 < C.col2
group by A.col1
drop table #t1|||I'm calling you on that one Rudy. Did you read the requirements carefully?
oh, SHEEEEEEEESH, okay :S
select col1
, group_concat(col2 separator ' ')
, sum(col3)
from t1
group
by col1|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)
...and that would be just great if it was a real ANSI compliant databa...
Oh, never mind|||pot? meet kettle
kettle? meet pot
:p|||That was great,thank you Blindman.And thank you all of you for your help.
Subscribe to:
Posts (Atom)