You are here: Re: Query help needed « MsSQL Server « IT news, forums, messages
Re: Query help needed

Posted by Ed Murphy on 03/15/07 04:32

sephent@gmail.com wrote:

> I will agree with you about the design. However, I didn't create it
> (nor am I in a position to fix it). Blame the good folks at Altec for
> the design or lack of (this is their DocLink).
>
> I'm just the poor slob who has to deal with it.

And hiding the bad tables inside less-bad views is a decent way of
going about that task. Here is how to do what you were attempting:

declare @IDNum int
set @IDNum = '195203'

select f.FolderCode,
doc.DocumentId,
max(case when c.DocumentTypePropertyId = 1
then c.PropertyCharValue end) as VendorNumber,
max(case when c.DocumentTypePropertyId = 2
then c.PropertyCharValue end) as VendorName,
max(case when c.DocumentTypePropertyId = 3
then c.PropertyCharValue end) as InvoiceNumber,
max(case when d.DocumentTypePropertyId = 4
then d.PropertyDateValue end) as InvoiceDate,
-- etc.
from dbo.DLFolders f
join dbo.Documents doc on doc.DLFolderID = f.FolderID
left join dbo.PropertyCharValues c on c.ParentId = doc.DocumentId
left join dbo.PropertyDateValues d on d.ParentId = doc.DocumentId
where doc.DocumentId = @IDNum
group by f.FolderCode, doc.DocumentId

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация