October 21, 2010 at 8:32 am
I'm trying to do a Compute ... By when selecting a substring and the two don't seem to work together - am I doing something wrong or is it a bug?
This code is something similar to what I'm doing. It works on SQL 2005 but not on SQL Server Version 8 so it could be a bug in that version. (I know the example doesn't make sense - I've created an example with as few clauses as possible that uses a table that should be available to all).
select
'table',
substring (so1.name, 1, 50)tables,
count (*)'count'
fromsysobjectsso1
Whereso1.name like 'mf%'
group by
substring (so1.name, 1, 50)
order bytables
Compute sum(count(*)) by substring (so1.name, 1, 50)
Andy
--------------------------------------------------------------
“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
October 22, 2010 at 8:42 am
I used a subquery as a way round the problem (as follows) but is there a better solution?
Selecttables, count(*) 'count'
from(selectsubstring (so1.name, 1, 50)'tables'
fromsysobjectsso1
Whereso1.name like 'mf%'
Group By so1.name)tbl
group bytables
order bytables
Compute sum(count(*)) by tables
--------------------------------------------------------------
“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply