Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Voller
Starting Member
4 Posts |
 Posted - 2005-02-13 : 17:22:39
|
Hi guys and girls.I'm have just gotten my hands on my first MSSQL-database. I'm having a problem creating a pretty simple view.Let's have a look, shall we? The problem is the dbo.Voller_Weblog.Content column. The datatype is 'text'. That column is what's causing the problem. It's seems like I can't GROUP BY a text-column, but if I remove the column from the GROUP BY-clause, the view won't work either.So what's a guy to do?P.S.: Sorry about poor grammar og spelling, english is not my primary language. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-13 : 17:30:55
|
| I'm not clear on why the view "won't work" if you remove it from the list...what doesn't work? Do you get an error? |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-13 : 17:39:08
|
| If you remove it from the group by then you can't return the value without an aggregate function otherwise server does't know which of the values to return for the group.You can use convert(varchar(8000), dbo.Voller_Weblog.Content) in both the select and group by or remove it from the group be and max(convert(varchar(8000), dbo.Voller_Weblog.Content)) in the select.To get the full text value you would have to join to the table after creating a derived table from the group by.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Voller
Starting Member
4 Posts |
Posted - 2005-02-13 : 17:45:48
|
| Yes I get an error-message if I remove the column from the group by clause: 'Column dbo.Voller_Weblog.Content is invalid in the select list because it is not contained in either an aggregrate function or in the group by clause'.I can't figure it out. If I don't include the content-column in the group by clause, I get the errormessage 'Column dbo.Voller_Weblog.Content is invalid...'. If I do include the column in the group by clause, I get the errormessage as shown on the screenshot.If I simply remove the content-column from both the select and group by list, the view works fine. But I need that column in the view. |
|
|
|
Voller
Starting Member
4 Posts |
Posted - 2005-02-13 : 17:56:32
|
quote:
Originally posted by nr
To get the full text value you would have to join to the table after creating a derived table from the group by.
That's sounds a bit to complicated for me... Could you explain how to do that, or could you direct me to a tutorial somewhere? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-13 : 18:16:13
|
| select a.fld1, a.fld2, a.fld3, tbl3.txtfldfrom(select tbl1.fld1, tbl1.fld2, tbl2.fld3from tbl1 join tbl2 on tbl1.fld = tbl2.fldgroup by fld1, fld2, fld3) ajoin tbl3on a.fld1 = tbl3.fld1==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Voller
Starting Member
4 Posts |
Posted - 2005-02-14 : 07:12:54
|
| Thanks, I've got it working. |
|
|
|
|
|
|
|
|
Can't group by text-field?