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

[Back to original message]