May 24, 2006 at 10:06 am
OK so essentially a couple of tables/views (RDB_Pub_Auth is a table,allNames is a view) , with simple join and a coalesce statement:
DECLARE @AuthList AS Nvarchar(500)
SELECT @AuthList = COALESCE (@AuthList + ',', '') + dbo.allNames.Surname + ' ' + ISNULL(dbo.allNames.initials, '')
FROM dbo.RDB_Pub_Auth inner JOIN
dbo.allNames ON dbo.RDB_Pub_Auth.RDBAuthID = dbo.allNames.nameID
WHERE (dbo.RDB_Pub_Auth.RDBPubID = 781)
ORDER BY rdbauthorder ASC
print @authlist
now without the ORDER by clause it returns as expected, the list of concatenated names seperated with a nice comma - BUT put the ORDER BY rdbauthorder ASC clause in and all I get is the LAST value once sorted, but use a ORDER BY rdbauthorder DESC and I only get get the first - complete opposite to expected and only single values not concatentated strings.
So I thought create a simple view of the join and pull the information from there - same issue, also the same issue using a derived table like such:
DECLARE @AuthList AS Nvarchar(500)
SELECT @AuthList = COALESCE (@AuthList + ',', '') + Surname + ' ' + ISNULL(initials, '')
FROM ( SELECT dbo.allNames.Surname,dbo.allNames.initials,dbo.RDB_Pub_Auth.rdbauthorder
FROM dbo.RDB_Pub_Auth inner JOIN dbo.allNames ON dbo.RDB_Pub_Auth.RDBAuthID = dbo.allNames.nameID
WHERE (dbo.RDB_Pub_Auth.RDBPubID = 781)) DT
ORDER BY rdbauthorder ASC
print @authlist
Anyone have any idea why I can not use coalesce and order in this way? The order clause works as expected as long as it is a straight table - in fact at the moment I am creating a temporary table pushing the filtered joined values into that and use the coalesce statement with the order clause on that.
May 24, 2006 at 11:53 am
Matbe you have a null in Surname which resets the string, and occurs near the start without an order by, but just before the end when you order it. You can eliminate this possibility by using an isnull or coalesce.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 24, 2006 at 2:28 pm
hmmm, no nulls - first thing I checked - only the initials field can allow nulls and with the data I have I have checked. Anyway with the coalesce function it should not matter about nulls, plus when the order command is omitted there is no issue - presence of a null should cause an issue with this.
(edit) I have now tried it with a isnull wrapper on the surname and still exactly the same
May 25, 2006 at 3:25 am
Well if Surname is not null, it' snot null. But a single null might not have the same effect under a different sort order, and you don't have a coalesce around Surname. And even one null could have the effect you suggest. Indulge me, and put an isnull around Surname. Then try 'top 1' in your query, increasing by 10 until it breaks? (then fine tune to find the offending record(s))
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 25, 2006 at 3:32 am
See earlier post - tried the isnull - no luck
BUT
using top x does work - apart from the fact that unless you know how many in the first instance it once again breaks i.e. in this case there are 3 records - top 1 through to 3 returns as expected and required, but top 4 reverts to previous behaviour.
May 25, 2006 at 3:41 am
What is the underlying data in dbo.allNames.Surname and dbo.allNames.initials for these 4 records?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 25, 2006 at 4:15 am
the filter of WHERE (dbo.RDB_Pub_Auth.RDBPubID = 781) should only pull three records (as it does) hence why I said using the TOP command upto and including the number of filtered records works, once past that limit it reverts back to the issue from before (hence useless unless count already known). The underlying data in the surname and initials fields is nvarchar - and all surname and initials fields have string values of greater than one character (I have double checked), thanks for your advice so far.
May 25, 2006 at 4:57 am
Can you post the data from those three records then?
Also try running the query with a left join, as a select without variables. Do you get more records?
You could also try putting your order by clause inside an inline view (derived table, joined subquery). You will need to specify a TOP clause - use the maximum possible for int or bigint as appropriate.
Please also post the execution plans for the two versions of the query (e.g. by running set statistics profile on and copying the entire StmtText column). That could help a lot - or not at all of course.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply