Hello,
I have a report which takes data from a stored procedure and then reveals
some text on a row depending on whether or not a bit is true. The data coming
from teh stored procedure is a single row for a contact, and it displays a
number of rows in the report.
When we run the report into a PDF the data is all tidily on one page,
however in the web browser it is only showng 4 database rows per page (even
if there is only one additional line being displayed), this is because there
are so many rows (10), though the data isnt spaced out it is all at the top
of the report. Is there a way of getting more data to display in the report ?
Many Thanks
ChrisHi Chris,
I understood you would like to hide rows in a table, however I am not sure
in what condition you would like to hide the row? Would you please provide
us some detailed scenario examples?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
Conditional timeout issue when executing SP
In the past week, I've been running into a very unusual timeout issue
with SQL 2005 SP2 and the execution of a single, specific stored
procedure. The stored procedure executes in under 1 second and uses
all appropriate indexes when it's run from the SQL Enterprise Manager,
but when the query is executed as part of the loading process of the
website it's used in, the data doesn't load and a SQL timeout error is
encountered. In both circumstances, the query originates from the same
machine.
In the website, the procedure that is timing out isn't the first query
in the loading process to access that database or set of tables. It
seems that the problem is just something with that query itself. There
really isn't anything too out of the ordinary about the query either;
it's just a single SELECT statement with a few joins, two subqueries
(one of which contains a subquery of its own) in the SELECT and one
subquery in the WHERE, and
I tried restarting IIS on the machine just for kicks, but it doesn't
solve the problem. I also tried copying the website application to
another machine, but I still receive the same timeout error on the
same query even when the query continues to execute just fine from the
Enterprise Manager environment on the first machine. To make matters
even more puzzling, the query / stored procedure _usually_ loads
without fuss when I pass in a different value for the single parameter
it takes; sometimes though, the procedure never loads within the
website application even when I try all of the different reasonable
values for that parameter.
The only way to fix the problem that I've found is to either restart
the SQL service or change the number of threads that the service is
using (which seems to issue a "soft restart" of sorts to SQL itself).
Once restarted, the website application loads fine and runs fine for a
few days. However, once a few days have passed, the SQL server again
starts giving timeouts for that query. The timeout errors that _do_
occur at this point are usually given when the single parameter is
different that the last parameter that gave the timeout errors before
the last time the server was restarted. In all reality, everything
just seems so random, and so it's hard to pin down any more details
for sure.
Like I said, this issue has only popped up in the past week or so and
had been running fine for the 6+ months previous. Has anyone else
encountered this issue before or otherwise have any suggestions for
how I can fix the problem? I'd really appreciate anything at this
point because I'm running out of ideas.My guess is that you have parameter sniffing issues. I suggest you spend an hour or two with below.
It is worth your time.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"amattie" <amattie@.gmail.com> wrote in message
news:57616074-99f3-4c49-9b49-babbddfe11d9@.e23g2000prf.googlegroups.com...
> In the past week, I've been running into a very unusual timeout issue
> with SQL 2005 SP2 and the execution of a single, specific stored
> procedure. The stored procedure executes in under 1 second and uses
> all appropriate indexes when it's run from the SQL Enterprise Manager,
> but when the query is executed as part of the loading process of the
> website it's used in, the data doesn't load and a SQL timeout error is
> encountered. In both circumstances, the query originates from the same
> machine.
> In the website, the procedure that is timing out isn't the first query
> in the loading process to access that database or set of tables. It
> seems that the problem is just something with that query itself. There
> really isn't anything too out of the ordinary about the query either;
> it's just a single SELECT statement with a few joins, two subqueries
> (one of which contains a subquery of its own) in the SELECT and one
> subquery in the WHERE, and
> I tried restarting IIS on the machine just for kicks, but it doesn't
> solve the problem. I also tried copying the website application to
> another machine, but I still receive the same timeout error on the
> same query even when the query continues to execute just fine from the
> Enterprise Manager environment on the first machine. To make matters
> even more puzzling, the query / stored procedure _usually_ loads
> without fuss when I pass in a different value for the single parameter
> it takes; sometimes though, the procedure never loads within the
> website application even when I try all of the different reasonable
> values for that parameter.
> The only way to fix the problem that I've found is to either restart
> the SQL service or change the number of threads that the service is
> using (which seems to issue a "soft restart" of sorts to SQL itself).
> Once restarted, the website application loads fine and runs fine for a
> few days. However, once a few days have passed, the SQL server again
> starts giving timeouts for that query. The timeout errors that _do_
> occur at this point are usually given when the single parameter is
> different that the last parameter that gave the timeout errors before
> the last time the server was restarted. In all reality, everything
> just seems so random, and so it's hard to pin down any more details
> for sure.
> Like I said, this issue has only popped up in the past week or so and
> had been running fine for the 6+ months previous. Has anyone else
> encountered this issue before or otherwise have any suggestions for
> how I can fix the problem? I'd really appreciate anything at this
> point because I'm running out of ideas.
with SQL 2005 SP2 and the execution of a single, specific stored
procedure. The stored procedure executes in under 1 second and uses
all appropriate indexes when it's run from the SQL Enterprise Manager,
but when the query is executed as part of the loading process of the
website it's used in, the data doesn't load and a SQL timeout error is
encountered. In both circumstances, the query originates from the same
machine.
In the website, the procedure that is timing out isn't the first query
in the loading process to access that database or set of tables. It
seems that the problem is just something with that query itself. There
really isn't anything too out of the ordinary about the query either;
it's just a single SELECT statement with a few joins, two subqueries
(one of which contains a subquery of its own) in the SELECT and one
subquery in the WHERE, and
I tried restarting IIS on the machine just for kicks, but it doesn't
solve the problem. I also tried copying the website application to
another machine, but I still receive the same timeout error on the
same query even when the query continues to execute just fine from the
Enterprise Manager environment on the first machine. To make matters
even more puzzling, the query / stored procedure _usually_ loads
without fuss when I pass in a different value for the single parameter
it takes; sometimes though, the procedure never loads within the
website application even when I try all of the different reasonable
values for that parameter.
The only way to fix the problem that I've found is to either restart
the SQL service or change the number of threads that the service is
using (which seems to issue a "soft restart" of sorts to SQL itself).
Once restarted, the website application loads fine and runs fine for a
few days. However, once a few days have passed, the SQL server again
starts giving timeouts for that query. The timeout errors that _do_
occur at this point are usually given when the single parameter is
different that the last parameter that gave the timeout errors before
the last time the server was restarted. In all reality, everything
just seems so random, and so it's hard to pin down any more details
for sure.
Like I said, this issue has only popped up in the past week or so and
had been running fine for the 6+ months previous. Has anyone else
encountered this issue before or otherwise have any suggestions for
how I can fix the problem? I'd really appreciate anything at this
point because I'm running out of ideas.My guess is that you have parameter sniffing issues. I suggest you spend an hour or two with below.
It is worth your time.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"amattie" <amattie@.gmail.com> wrote in message
news:57616074-99f3-4c49-9b49-babbddfe11d9@.e23g2000prf.googlegroups.com...
> In the past week, I've been running into a very unusual timeout issue
> with SQL 2005 SP2 and the execution of a single, specific stored
> procedure. The stored procedure executes in under 1 second and uses
> all appropriate indexes when it's run from the SQL Enterprise Manager,
> but when the query is executed as part of the loading process of the
> website it's used in, the data doesn't load and a SQL timeout error is
> encountered. In both circumstances, the query originates from the same
> machine.
> In the website, the procedure that is timing out isn't the first query
> in the loading process to access that database or set of tables. It
> seems that the problem is just something with that query itself. There
> really isn't anything too out of the ordinary about the query either;
> it's just a single SELECT statement with a few joins, two subqueries
> (one of which contains a subquery of its own) in the SELECT and one
> subquery in the WHERE, and
> I tried restarting IIS on the machine just for kicks, but it doesn't
> solve the problem. I also tried copying the website application to
> another machine, but I still receive the same timeout error on the
> same query even when the query continues to execute just fine from the
> Enterprise Manager environment on the first machine. To make matters
> even more puzzling, the query / stored procedure _usually_ loads
> without fuss when I pass in a different value for the single parameter
> it takes; sometimes though, the procedure never loads within the
> website application even when I try all of the different reasonable
> values for that parameter.
> The only way to fix the problem that I've found is to either restart
> the SQL service or change the number of threads that the service is
> using (which seems to issue a "soft restart" of sorts to SQL itself).
> Once restarted, the website application loads fine and runs fine for a
> few days. However, once a few days have passed, the SQL server again
> starts giving timeouts for that query. The timeout errors that _do_
> occur at this point are usually given when the single parameter is
> different that the last parameter that gave the timeout errors before
> the last time the server was restarted. In all reality, everything
> just seems so random, and so it's hard to pin down any more details
> for sure.
> Like I said, this issue has only popped up in the past week or so and
> had been running fine for the 6+ months previous. Has anyone else
> encountered this issue before or otherwise have any suggestions for
> how I can fix the problem? I'd really appreciate anything at this
> point because I'm running out of ideas.
Tuesday, February 14, 2012
Concurrency
Do single commands (or stored procedures) execute concurrently, or they are executed one by one. How do you perform a lock during the execution of a command (or stored procedure).
Single commands are 'implied' transactions, that is the command executes completely, or nothing is done.
For multiple commands, where there is a need to make sure the several statements execute in a 'all or nothing' scenario, use TRANSACTIONS.
Refer to Books Online, Topic: Transactions
|||Other good topics in the BOL are "Isolation mode" and locking hints.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Sunday, February 12, 2012
Concatenation Isssue (SELECT QUERY)
here is the sample data. I want a query which can fetch me a single record that can concatenate the value in the 3rd column for the same value in 1st column. do let me know if any understanding issue is there.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 2 arrier and DeltaV Controller is
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 3 nets and field equipment interface.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 1 This is the quote for RS3 migration
Thanks,
Rahul Jhasomething like SUM() for varchar data type.|||avast, you should be doing this in your application layer, me hearty
:)|||have just gt a code. wanted to share this with others, and looking forward for the comment from the forum.
-- Prepare sample data
DECLARE @.Sample TABLE (ID INT, Code VARCHAR(3))
INSERT @.Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'
SELECT * FROM @.Sample
-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID
SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID
SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID|||but my database is 2000. not 2005. and in 2000 the above code will not work.|||Why not do it in the presentation layer/|||This here DBA needs to walk the plank.|||Arggggghhhh
shiver me timbers|||Your wood is cold..?
I just don't even want to know :p
...seriously, what doesthat phrase even mean?|||...seriously, what doesthat phrase even mean?haaaarr, ye be a pitiful excuse for a young pirate, me lad
http://en.wikipedia.org/wiki/Shiver_my_timbers|||hahaha its true its Pirate day today :) aaaaaarrrrrrr|||Arggggghhhh
shiver me timbers
Brett, Opie and Anthony fan ?
They had pirate talk today.|||buffett
He mentioned it last night at his concert at MSG|||buffett
He mentioned it last night at his concert at MSG
Rush Fan ? they played Monday night at MSG.|||Rush Fan ? they played Monday night at MSG.
In another lifetime|||Original Quote Posted By MCrowley.....
Why not do it in the presentation layer/
No I can't do this at the presentation layer. This issue has come duriong the data migration phase. It has to be done at the DB side only.........
Thanks,
Rahul Jha|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.
Actaully m normalising the DB. currently it's in De-normalised state......
Thanks,
Rahul Jha|||Ha!
Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=|||Chances are that you are de-normalising your data by concatenation.that's a bit tentative, isn't it george?
for sure it's denormalizing the data
:)|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.
DateField + TimeField
Bleugh.|||You can try this out...
"SELECT THIRD_COLUMN+FIRST_COLUMN FROM TABLE"
...it works if both are varchar type columns.
if you wanna add some special character inbetween ...
THIRD_COLUMN+'-'+FIRST_COLUMN will solve your purpose.|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.
DateField + TimeField
Bleugh.actually, that is "really concatenation" :)
what he wants to do is aggregation (a column operation over several rows)
it's a reasonable request
in MySQL, the GROUP_CONCAT aggregate function performs exactly this operation, with options for the separator and sequence of terms|||Ha!
Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=
How is it gonna help Georgy by any chance...........?? Have a look again on the query............. And if the link can help me to qrite the query then kindly guide me thru.........
Thanks,
Rahul Jha|||what he wants to do is aggregation (a column operation over several rows)
ur rgt.......
Thanks,
Rahul Jha|||start here:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html|||ur rgt.......
Thanks,
Rahul Jha
I love those phone commercials
omg wtf lol roflmao|||buffett
He mentioned it last night at his concert at MSG
You went all the way from the Channel Islands to Madison Square Gardens?! Wow, you are a serious Jimmy Buffett fan!! :D|||ur rgt.......OMG, is there a new instance of The Great Bangalore Alphabet Famine of 1978 (www.nevermind-Im-just-kidding) going on?
Horrors.
Fortunately, relief packages of 1000 letters are available on a first-come-first-served basis at the CIIL (http://www.ciil.org/)|||he is debugging his app on a cell phone while driving
using only his thumb
actually, i will cut him a lot of slack because of this
:cool:
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 2 arrier and DeltaV Controller is
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 3 nets and field equipment interface.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 1 This is the quote for RS3 migration
Thanks,
Rahul Jhasomething like SUM() for varchar data type.|||avast, you should be doing this in your application layer, me hearty
:)|||have just gt a code. wanted to share this with others, and looking forward for the comment from the forum.
-- Prepare sample data
DECLARE @.Sample TABLE (ID INT, Code VARCHAR(3))
INSERT @.Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'
SELECT * FROM @.Sample
-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID
SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID
SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID|||but my database is 2000. not 2005. and in 2000 the above code will not work.|||Why not do it in the presentation layer/|||This here DBA needs to walk the plank.|||Arggggghhhh
shiver me timbers|||Your wood is cold..?
I just don't even want to know :p
...seriously, what doesthat phrase even mean?|||...seriously, what doesthat phrase even mean?haaaarr, ye be a pitiful excuse for a young pirate, me lad
http://en.wikipedia.org/wiki/Shiver_my_timbers|||hahaha its true its Pirate day today :) aaaaaarrrrrrr|||Arggggghhhh
shiver me timbers
Brett, Opie and Anthony fan ?
They had pirate talk today.|||buffett
He mentioned it last night at his concert at MSG|||buffett
He mentioned it last night at his concert at MSG
Rush Fan ? they played Monday night at MSG.|||Rush Fan ? they played Monday night at MSG.
In another lifetime|||Original Quote Posted By MCrowley.....
Why not do it in the presentation layer/
No I can't do this at the presentation layer. This issue has come duriong the data migration phase. It has to be done at the DB side only.........
Thanks,
Rahul Jha|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.
Actaully m normalising the DB. currently it's in De-normalised state......
Thanks,
Rahul Jha|||Ha!
Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=|||Chances are that you are de-normalising your data by concatenation.that's a bit tentative, isn't it george?
for sure it's denormalizing the data
:)|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.
DateField + TimeField
Bleugh.|||You can try this out...
"SELECT THIRD_COLUMN+FIRST_COLUMN FROM TABLE"
...it works if both are varchar type columns.
if you wanna add some special character inbetween ...
THIRD_COLUMN+'-'+FIRST_COLUMN will solve your purpose.|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.
DateField + TimeField
Bleugh.actually, that is "really concatenation" :)
what he wants to do is aggregation (a column operation over several rows)
it's a reasonable request
in MySQL, the GROUP_CONCAT aggregate function performs exactly this operation, with options for the separator and sequence of terms|||Ha!
Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=
How is it gonna help Georgy by any chance...........?? Have a look again on the query............. And if the link can help me to qrite the query then kindly guide me thru.........
Thanks,
Rahul Jha|||what he wants to do is aggregation (a column operation over several rows)
ur rgt.......
Thanks,
Rahul Jha|||start here:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html|||ur rgt.......
Thanks,
Rahul Jha
I love those phone commercials
omg wtf lol roflmao|||buffett
He mentioned it last night at his concert at MSG
You went all the way from the Channel Islands to Madison Square Gardens?! Wow, you are a serious Jimmy Buffett fan!! :D|||ur rgt.......OMG, is there a new instance of The Great Bangalore Alphabet Famine of 1978 (www.nevermind-Im-just-kidding) going on?
Horrors.
Fortunately, relief packages of 1000 letters are available on a first-come-first-served basis at the CIIL (http://www.ciil.org/)|||he is debugging his app on a cell phone while driving
using only his thumb
actually, i will cut him a lot of slack because of this
:cool:
Friday, February 10, 2012
Concatenating strings
I have a table with seven different descriptor fields, and at times it's
necessary to concatenate all seven into a single field in a query. I tried
simply concatenating the fields together, but if a single input field has a
null the output is a null. It does this even though the
concat_null_yields_null property is set to false, which will never cease to
mystify me.
Regardless, I can probably get the results I want by writing an extensive
CASE statement, but was wondering if there was a simpler method. I'm
wondering if there's a similar function to COALESCE -- instead of returning
the first non-null field I want to return all non-null fields. Either that o
r
is there some other reason besides concat_null_yields_null why null input
would return null using simple string concatenation.
Thanks in advance.Use function ISNULL.
Example.
select 'SQL ' + isnull(cast(null as varchar(25), 'Server')
go
AMB
"mike" wrote:
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease t
o
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of returnin
g
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.|||You need to say:
SELECT COALESCE(col1, '')+COALESCE(col2, '')+...+COALESCE(colN, '') ...
Or, do the concatenation at the client/presentation tier.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:594CA1BB-46BB-4A34-9C5C-D4EEBB4B9985@.microsoft.com...
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease
to
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of
returning
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.
necessary to concatenate all seven into a single field in a query. I tried
simply concatenating the fields together, but if a single input field has a
null the output is a null. It does this even though the
concat_null_yields_null property is set to false, which will never cease to
mystify me.
Regardless, I can probably get the results I want by writing an extensive
CASE statement, but was wondering if there was a simpler method. I'm
wondering if there's a similar function to COALESCE -- instead of returning
the first non-null field I want to return all non-null fields. Either that o
r
is there some other reason besides concat_null_yields_null why null input
would return null using simple string concatenation.
Thanks in advance.Use function ISNULL.
Example.
select 'SQL ' + isnull(cast(null as varchar(25), 'Server')
go
AMB
"mike" wrote:
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease t
o
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of returnin
g
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.|||You need to say:
SELECT COALESCE(col1, '')+COALESCE(col2, '')+...+COALESCE(colN, '') ...
Or, do the concatenation at the client/presentation tier.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:594CA1BB-46BB-4A34-9C5C-D4EEBB4B9985@.microsoft.com...
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease
to
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of
returning
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.
Subscribe to:
Posts (Atom)