|
|
You're in the
sql server (alternate)
group:Query help needed
sql server (alternate):
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.
[quoted text, click to view]
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.
[quoted text, click to view]
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
[quoted text, click to view]
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.
[quoted text, click to view]
>> 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.
[quoted text, click to view]
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
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |