July 7, 2006 at 12:08 pm
I have a query that allows me to aggregate the separate French and English record details (text fields) of a table into one row output like this:
MAX(CASE LangID WHEN 1 then convert(varchar(8000),dbo.UpdateDetails.UpdateContent) end) as UpdateContent,
MAX(CASE LangID WHEN 2 then convert(varchar(8000),dbo.UpdateDetails.UpdateContent) end) as UpdateContentFR
In order to set it up I had to convert the text field to varchar(8000) to avoid the error message:
Error 279: The text, ntext and image data types are invalid in this subquery or aggregate expression
I would like to avoid having to do this so that entries longer than 8000 characters are returned in their entirety. Is there a way to do this?
Andrew
July 7, 2006 at 3:16 pm
Do you actually need the entire field to aggregate? In other words, would 8000 characters be enough to actually know if the various records should be aggregated? If this premise is true, use a subselect INNER JOIN with the MAX and choose your output that way. Then you can display the full ntext or image data...
I wasn't born stupid - I had to study.
July 8, 2006 at 10:21 am
In this case I don't need to aggregate on the text field. The details table that contains UpdateContent has a primary key (UpdateID, LanguageID) and I just need to group on the UpdateID and where LanguageID=1, return the UpdateContent as UpdateContent and where LanguageID=2, return the UpdateContent in a second column called UpdateContentFR.
Do you have an example of the subselect INNER JOIN that will accomplish this?
July 10, 2006 at 8:36 am
Here is the full original code for the query:
SELECT UpdateSections_1.SectionTitle AS ParentSectionTitle, UpdateSections_1.ID AS ParentSectionID, dbo.UpdateSections.SectionTitle,
dbo.UpdateSections.ID AS SectionID, dbo.Updates.ID AS UpdateID, dbo.Updates.PicFile, dbo.Updates.datetimeadded
,
MAX(CASE LangID WHEN 1 then dbo.UpdateDetails.UpdateTitle end) as UpdateTitle,
MAX(CASE LangID WHEN 1 then convert(varchar(8000),dbo.UpdateDetails.UpdateContent) end) as UpdateContent,
MAX(CASE LangID WHEN 2 then dbo.UpdateDetails.UpdateTitle end) as UpdateTitleFR,
MAX(CASE LangID WHEN 2 then convert(varchar(8000),dbo.UpdateDetails.UpdateContent) end) as UpdateContentFR
FROM dbo.UpdateDetails INNER JOIN
dbo.Updates ON dbo.UpdateDetails.UpdateID = dbo.Updates.ID INNER JOIN
dbo.UpdateSections ON dbo.Updates.SectionID = dbo.UpdateSections.ID INNER JOIN
dbo.UpdateSections UpdateSections_1 ON dbo.UpdateSections.ParentSectionID = UpdateSections_1.ID
GROUP BY UpdateSections_1.SectionTitle, UpdateSections_1.ID, dbo.UpdateSections.SectionTitle, dbo.UpdateSections.ID, dbo.Updates.ID, dbo.Updates.PicFile,
dbo.Updates.datetimeadded
Any help on this is appreciated. thanks.
July 10, 2006 at 8:57 am
Something like this? I suspect you don't need aggregates. If you do, why MAX() of a long character field? This solution assumes (updateID,langID) is unique in updatedetails.
us_1.ID ParentSectionID
us.SectionTitle
us.ID SectionID
u.ID UpdateID
u.PicFile
u.datetimeadded
ud_EN.UpdateTitle
ud_EN.UpdateContent
from dbo.Updates u
dbo.UpdateSections us
dbo.UpdateSections us_1
dbo.UpdateDetails ud_EN
[Edit: amended mistake in code 10 mins after posting...]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 10, 2006 at 7:21 pm
Thanks Tim, That works great.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply