SEO

I'm pretty new to SQL and querying, and I'm not sure if this is the
correct group to post this to, but hopefully someone can point me in
the right direction.

I have the following query:

SELECT Distinct dbo.DLFolders.FolderCode,
dbo.PropertyCharValues.ParentId,
dbo.PropertyCharValues.DocumentTypePropertyId,
dbo.PropertyCharValues.PropertyCharValue,
dbo.PropertyDateValues.DocumentTypePropertyId AS Expr1,
dbo.PropertyDateValues.PropertyDateValue
FROM dbo.PropertyDateValues INNER JOIN
dbo.PropertyCharValues ON
dbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentId
INNER JOIN
dbo.Documents ON dbo.PropertyCharValues.ParentId
= dbo.Documents.DocumentId INNER JOIN
dbo.DLFolders ON dbo.Documents.DLFolderID =
dbo.DLFolders.FolderID
WHERE (dbo.PropertyDateValues.ParentId = '195203')

Which yields these results (except the far right column, which I've
added manually to describe what the PropertyCharValue is. I didn't
bother with the PropertyDateValues, but they are basically Invoice
Date, Post Date, and Check Date):

FolderCode ParentId DocumentTypePropertyId PropertyCharValue Expr1
PropertyDateValue
428 195203 1 19 4 2006-06-03 00:00:00.000 Vendor Number
428 195203 1 19 6 2006-07-05 00:00:00.000 Vendor Number
428 195203 1 19 93 2006-06-30 00:00:00.000 Vendor Number
428 195203 2 Bhellington Corporation 4 2006-06-03 00:00:00.000 Vendor
Name
428 195203 2 Bhellington Corporation 6 2006-07-05 00:00:00.000 Vendor
Name
428 195203 2 Bhellington Corporation 93 2006-06-30 00:00:00.000
Vendor Name
428 195203 3 68001-0421381 4 2006-06-03 00:00:00.000 Invoice Number
428 195203 3 68001-0421381 6 2006-07-05 00:00:00.000 Invoice Number
428 195203 3 68001-0421381 93 2006-06-30 00:00:00.000 Invoice Number
428 195203 5 0000000052 4 2006-06-03 00:00:00.000 Voucher Number
428 195203 5 0000000052 6 2006-07-05 00:00:00.000 Voucher Number
428 195203 5 0000000052 93 2006-06-30 00:00:00.000 Voucher Number
428 195203 7 0000010027 4 2006-06-03 00:00:00.000 Check Number
428 195203 7 0000010027 6 2006-07-05 00:00:00.000 Check Number
428 195203 7 0000010027 93 2006-06-30 00:00:00.000 Check Number
428 195203 22 428 4 2006-06-03 00:00:00.000 Company Code
428 195203 22 428 6 2006-07-05 00:00:00.000 Company Code
428 195203 22 428 93 2006-06-30 00:00:00.000 Company Code
428 195203 23 3908.78 4 2006-06-03 00:00:00.000 Amount
428 195203 23 3908.78 6 2006-07-05 00:00:00.000 Amount
428 195203 23 3908.78 93 2006-06-30 00:00:00.000 Amount
428 195203 24 APVO-0000015 4 2006-06-03 00:00:00.000 Batch ID
428 195203 24 APVO-0000015 6 2006-07-05 00:00:00.000 Batch ID
428 195203 24 APVO-0000015 93 2006-06-30 00:00:00.000 Batch ID
428 195203 25 040575000 4 2006-06-03 00:00:00.000 GL Accounts
428 195203 25 040575000 6 2006-07-05 00:00:00.000 GL Accounts
428 195203 25 040575000 93 2006-06-30 00:00:00.000 GL Accounts
428 195203 25 040576000 4 2006-06-03 00:00:00.000 GL Accounts
428 195203 25 040576000 6 2006-07-05 00:00:00.000 GL Accounts
428 195203 25 040576000 93 2006-06-30 00:00:00.000 GL Accounts
428 195203 123 428 4 2006-06-03 00:00:00.000 Target Company
428 195203 123 428 6 2006-07-05 00:00:00.000 Target Company
428 195203 123 428 93 2006-06-30 00:00:00.000 Target Company


As you can see, the results triplicate the PropertyCharValue items and
multiply by 11 the PropertyDateValues because they are on different
tables and because I don't know what I'm doing.

I would like to see a result with columns like this:

FolderCode ParentID VendorNum VendorName InvNum VouchNum
CompCode Amount BatchID GLAccounts GLAccounts(2) Target
Company InvDate PostDate CheckDate


To that end, I tried the following query as an attempt in that
direction:


Declare @IDNum int
Set @IDNum = '195203'
SELECT distinct dbo.DLFolders.FolderCode,
dbo.PropertyCharValues.ParentId,
(Select PropertyCharValue from PropertyCharValues Where
ParentId = '195203' and DocumentTypePropertyId= '5') as VouchNum,
(Select PropertyCharValue from PropertyCharValues Where
ParentID = '195203' and DocumentTypePropertyID= '25') as GLNumber,
--dbo.PropertyCharValues.DocumentTypePropertyId,
dbo.PropertyCharValues.PropertyCharValue,
(SELECT PropertyDateValue
FROM dbo.PropertyDateValues
WHERE ParentId = '195203' AND
DocumentTypePropertyID = '4') AS InvoiceDate,
(SELECT PropertyDateValue
FROM dbo.PropertyDateValues
WHERE ParentId = '195203' AND
DocumentTypePropertyID = '93') AS PostDate,
(SELECT PropertyDateValue
FROM dbo.PropertyDateValues
WHERE ParentId = '195203' AND
DocumentTypePropertyID = '6') AS CheckDate
FROM dbo.PropertyDateValues INNER JOIN
dbo.PropertyCharValues ON
dbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentId
INNER JOIN
dbo.Documents ON dbo.PropertyCharValues.ParentId
= dbo.Documents.DocumentId INNER JOIN
dbo.DLFolders ON dbo.Documents.DLFolderID =
dbo.DLFolders.FolderID
WHERE (dbo.PropertyDateValues.ParentId = '195203')



And I discovered that a subquery can't have more than one result.

I'm not sure what avenues to try next. Any suggestions?

Your entire approach is wrong. Google up the EAV (entity-attribute-
value) design flaw. Your data element names are absurd. Consider
"DocumentTypePropertyId" in which you have things like "type" and "id"
mixed together. A data element by definition can be either a type or
an identifier, but never both. You are cramming mixed data into
single columns -- the magical "Briteny Spears, Squids and Automobiles"
of newbies who never bothered with a single book to tell about First
Normal Form.

This is so bad that no newsgroup can do your job for you. Get help
and start over before you hurt someone.

On Mar 14, 1:03 pm, "--CELKO--" <jcelko...@earthlink.net> wrote:
> Your entire approach is wrong. Google up the EAV (entity-attribute-
> value) design flaw. Your data element names are absurd. Consider
> "DocumentTypePropertyId" in which you have things like "type" and "id"
> mixed together. A data element by definition can be either a type or
> an identifier, but never both. You are cramming mixed data into
> single columns -- the magical "Briteny Spears, Squids and Automobiles"
> of newbies who never bothered with a single book to tell about First
> Normal Form.
>
> This is so bad that no newsgroup can do your job for you. Get help
> and start over before you hurt someone.


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.

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

On Mar 14, 9:24 pm, seph...@gmail.com wrote:

> I'm not sure what avenues to try next. Any suggestions?

You might try to create a temp table with all the column names you
desire (FolderCode ParentID... etc). Fill the table making sure you
only join with one or two tables at a time. Each time you check the
result. If you don't like it then reduce the number of tables in your
join. You keep joining with other tables and fill your temp table. If
this does not make sense then post DDL of all tables with some sample
data and we can take a crack at it.

>> 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. <<

The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors
of Hercules, he got to kill the bastard that gave him this job.

On Mar 14, 9:24 am, seph...@gmail.com wrote:
> I'm pretty new to SQL and querying, and I'm not sure if this is the
> correct group to post this to, but hopefully someone can point me in
> the right direction.
>
> I have the following query:
>
> SELECT Distinct dbo.DLFolders.FolderCode,
> dbo.PropertyCharValues.ParentId,
> dbo.PropertyCharValues.DocumentTypePropertyId,
> dbo.PropertyCharValues.PropertyCharValue,
> dbo.PropertyDateValues.DocumentTypePropertyId AS Expr1,
> dbo.PropertyDateValues.PropertyDateValue
> FROM dbo.PropertyDateValues INNER JOIN
> dbo.PropertyCharValues ON
> dbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentId
> INNER JOIN
> dbo.Documents ON dbo.PropertyCharValues.ParentId
> = dbo.Documents.DocumentId INNER JOIN
> dbo.DLFolders ON dbo.Documents.DLFolderID =
> dbo.DLFolders.FolderID
> WHERE (dbo.PropertyDateValues.ParentId = '195203')
>
> Which yields these results (except the far right column, which I've
> added manually to describe what the PropertyCharValue is. I didn't
> bother with the PropertyDateValues, but they are basically Invoice
> Date, Post Date, and Check Date):
>
> FolderCode ParentId DocumentTypePropertyId PropertyCharValue Expr1
> PropertyDateValue
> 428 195203 1 19 4 2006-06-03 00:00:00.000 Vendor Number
> 428 195203 1 19 6 2006-07-05 00:00:00.000 Vendor Number
> 428 195203 1 19 93 2006-06-30 00:00:00.000 Vendor Number
> 428 195203 2 Bhellington Corporation 4 2006-06-03 00:00:00.000 Vendor
> Name
> 428 195203 2 Bhellington Corporation 6 2006-07-05 00:00:00.000 Vendor
> Name
> 428 195203 2 Bhellington Corporation 93 2006-06-30 00:00:00.000
> Vendor Name
> 428 195203 3 68001-0421381 4 2006-06-03 00:00:00.000 Invoice Number
> 428 195203 3 68001-0421381 6 2006-07-05 00:00:00.000 Invoice Number
> 428 195203 3 68001-0421381 93 2006-06-30 00:00:00.000 Invoice Number
> 428 195203 5 0000000052 4 2006-06-03 00:00:00.000 Voucher Number
> 428 195203 5 0000000052 6 2006-07-05 00:00:00.000 Voucher Number
> 428 195203 5 0000000052 93 2006-06-30 00:00:00.000 Voucher Number
> 428 195203 7 0000010027 4 2006-06-03 00:00:00.000 Check Number
> 428 195203 7 0000010027 6 2006-07-05 00:00:00.000 Check Number
> 428 195203 7 0000010027 93 2006-06-30 00:00:00.000 Check Number
> 428 195203 22 428 4 2006-06-03 00:00:00.000 Company Code
> 428 195203 22 428 6 2006-07-05 00:00:00.000 Company Code
> 428 195203 22 428 93 2006-06-30 00:00:00.000 Company Code
> 428 195203 23 3908.78 4 2006-06-03 00:00:00.000 Amount
> 428 195203 23 3908.78 6 2006-07-05 00:00:00.000 Amount
> 428 195203 23 3908.78 93 2006-06-30 00:00:00.000 Amount
> 428 195203 24 APVO-0000015 4 2006-06-03 00:00:00.000 Batch ID
> 428 195203 24 APVO-0000015 6 2006-07-05 00:00:00.000 Batch ID
> 428 195203 24 APVO-0000015 93 2006-06-30 00:00:00.000 Batch ID
> 428 195203 25 040575000 4 2006-06-03 00:00:00.000 GL Accounts
> 428 195203 25 040575000 6 2006-07-05 00:00:00.000 GL Accounts
> 428 195203 25 040575000 93 2006-06-30 00:00:00.000 GL Accounts
> 428 195203 25 040576000 4 2006-06-03 00:00:00.000 GL Accounts
> 428 195203 25 040576000 6 2006-07-05 00:00:00.000 GL Accounts
> 428 195203 25 040576000 93 2006-06-30 00:00:00.000 GL Accounts
> 428 195203 123 428 4 2006-06-03 00:00:00.000 Target Company
> 428 195203 123 428 6 2006-07-05 00:00:00.000 Target Company
> 428 195203 123 428 93 2006-06-30 00:00:00.000 Target Company
>
> As you can see, the results triplicate the PropertyCharValue items and
> multiply by 11 the PropertyDateValues because they are on different
> tables and because I don't know what I'm doing.
>
> I would like to see a result with columns like this:
>
> FolderCode ParentID VendorNum VendorName InvNum VouchNum
> CompCode Amount BatchID GLAccounts GLAccounts(2) Target
> Company InvDate PostDate CheckDate
>
> To that end, I tried the following query as an attempt in that
> direction:
>
> Declare @IDNum int
> Set @IDNum = '195203'
> SELECT distinct dbo.DLFolders.FolderCode,
> dbo.PropertyCharValues.ParentId,
> (Select PropertyCharValue from PropertyCharValues Where
> ParentId = '195203' and DocumentTypePropertyId= '5') as VouchNum,
> (Select PropertyCharValue from PropertyCharValues Where
> ParentID = '195203' and DocumentTypePropertyID= '25') as GLNumber,
> --dbo.PropertyCharValues.DocumentTypePropertyId,
> dbo.PropertyCharValues.PropertyCharValue,
> (SELECT PropertyDateValue
> FROM dbo.PropertyDateValues
> WHERE ParentId = '195203' AND
> DocumentTypePropertyID = '4') AS InvoiceDate,
> (SELECT PropertyDateValue
> FROM dbo.PropertyDateValues
> WHERE ParentId = '195203' AND
> DocumentTypePropertyID = '93') AS PostDate,
> (SELECT PropertyDateValue
> FROM dbo.PropertyDateValues
> WHERE ParentId = '195203' AND
> DocumentTypePropertyID = '6') AS CheckDate
> FROM dbo.PropertyDateValues INNER JOIN
> dbo.PropertyCharValues ON
> dbo.PropertyDateValues.ParentId = dbo.PropertyCharValues.ParentId
> INNER JOIN
> dbo.Documents ON dbo.PropertyCharValues.ParentId
> = dbo.Documents.DocumentId INNER JOIN
> dbo.DLFolders ON dbo.Documents.DLFolderID =
> dbo.DLFolders.FolderID
> WHERE (dbo.PropertyDateValues.ParentId = '195203')
>
> And I discovered that a subquery can't have more than one result.
>
> I'm not sure what avenues to try next. Any suggestions?

You may need to do some cleaning up or better filtering. ParentID
195203 has three dates associated with it which spits out as both a
date and a date value (some normalization please?!?!?). Since you
aren't singling out any of these dates it returns all three dates for
each result... therefore the triplication.

I hope that helps somehow.

James