Derived Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Derived Tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-25 : 08:56:46

Alex writes "I looked all through your site for examples and I couldn't find something similar to what I need to do. I hope you can help me.

I'm new to this and I'm trying to get a handle on Derived Tables. I'm trying to get all this info on one row in a datagrid. The tblClaim is one to many to the tblClaimDetail. Meaning there is one row in tblClaim to two rows in tblClaimDetail. So because of the two rows in tblClaimDetail, I get two rows on my datagrid. So I gave several attempts and I got no where. This select statement was my last attempt. I would truly be grateful for any help anyone might lend. TIA

I'm aware that because of one-to-many cardinality I will get two rows. But is it possible to get it all on one row? If so, How?

Here is the Select statement:

SELECT dm.DebitMemoNumber as DebitMemoNumber
, vscc.SCCVendorName AS SCCVendorName
, tc.OriginalFindingsAmount AS OriginalFindingsAmount
, tc.AmountRecovered AS AmountRecovered
, dm.ClaimPriorityID AS ClaimPriority
, tc.ClaimTypeID AS ClaimType
, tc.RootCauseID AS RootCause
, cd.ClientVendorNumber AS ClientVendorNumber
, vci.Address1 AS Address1
, vci.Address2 AS Address2
, vci.Address3 AS Address3
, vci.City AS City
, vci.State AS State
, vci.Zip AS Zip
, vci.Country AS Country
, cd.CompanyCode AS CompanyCode
, tc.ClaimYear AS ClaimYear
, cd.ActiveVendor AS ActiveVendor
, dm.DebitMemoDate AS DebitMemoDate
, tc.ReportReference AS ReportReference
, tc.DateGivenToClient AS DateGivenToClient
, tc.DateApprovedByClient AS DateApprovedByClient
, tc.AmountApprovedByClient AS AmountApprovedByClient
, tc.RecoveryMethod AS RecoveryMethod
, tc.Date1DMMailed AS Date1DMMailed
, tc.Date2DMMailed, 101 AS Date2DMMailed
, tc.Date3DMMailed AS Date3DMMailed
, tc.DateEnteredIntoClientsSystem AS DateEnteredIntoClientsSystem
, tc.AmountRecoveredOnCheckRequest AS AmountRecoveredOnCheckRequest
, cd.InvoiceNumber AS InvoiceNumber
, cd.InvoiceDate, 101 AS InvoiceDate
, cd.CheckNumber AS CheckNumber
, cd.CheckDate AS CheckDate
, cd.CheckAmt AS CheckAmt
, tc.AmountRecoveredOnCheckRuns AS AmountRecoveredOnCheckRuns
, tc.CurrentBalanceAmount AS CurrentBalanceAmount
, tc.VoidedFindingAmount AS VoidedFindingAmount
, tc.DateDebitMemoVoided AS DateDebitMemoVoided
, tc.VoidID AS VoidCause
, tc.PrimaryClaimStatusID AS ClaimStatus
, tc.PrimaryStatusAmount AS PrimaryStatusAmount
, tc.DateInvoiced AS DateInvoiced
, tc.AmountInvoiced AS AmountInvoiced
, tc.SCCInvoiceNo AS SCCInvoiceNo
, cd.Notes AS Notes
, dm.CSGReviewFlag AS CSGReviewFlag
, dm.DocumentationReviewedBy AS DocumentationReviewedBy
, dm.ClientVendorInfoID AS ClientVendorInfoID
, tc.ClaimID AS ClaimID

FROM dbo.tblDebitMemo dm INNER JOIN
dbo.tblVendorClientInfo vci ON dm.ClientVendorInfoID = vci.ClientVendorInfoID INNER JOIN
dbo.tblVendorSCC vscc ON dm.SCCVendorID = vscc.SCCVendorID INNER JOIN
dbo.tblClaim tc ON dm.DebitMemoNumber = tc.DebitMemoNumber INNER JOIN
(Select ClientVendorNumber, CompanyCode, ActiveVendor, InvoiceNumber,
InvoiceDate, CheckNumber, CheckDate, CheckAmt, Notes, ClaimID from dbo.tblClaimDetail) cd ON tc.ClaimID = cd.ClaimID
WHERE (dm.ClientNumber = @strClientNo)

---@strClientNo is a parm being passed in the stored procedure this select statement belongs to."

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-25 : 13:07:50

I cna show you how to display that info in a "Parent / Child" way in ASP.net

This might help ya:
http://www.wimdows.net/articles/article.aspx?aid=19

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-25 : 13:32:56

If I'm not mistaken you can re-write the piece:

(Select ClientVendorNumber, CompanyCode, ActiveVendor, InvoiceNumber,
InvoiceDate, CheckNumber, CheckDate, CheckAmt, Notes, ClaimID
from dbo.tblClaimDetail) cd ON tc.ClaimID = cd.ClaimID

as following:

(Select
t.ClientVendorNumber, t.CompanyCode, t.ActiveVendor, t.InvoiceNumber,
t.InvoiceDate, t.CheckNumber, t.CheckDate, t.CheckAmt, t.Notes, t.ClaimID,

tt.ClientVendorNumber, tt.CompanyCode, tt.ActiveVendor, tt.InvoiceNumber,
tt.InvoiceDate, tt.CheckNumber, tt.CheckDate, tt.CheckAmt, tt.Notes

from dbo.tblClaimDetail t INNER JOIN dbo.tblClaimDetail tt ON
t.ClaimID=tt.ClaimID AND t.ClientVendorNumber<tt.ClientVendorNumber)

cd ON tc.ClaimID = cd.ClaimID

Also, you should add here aliases for ouput column names (something like
t.ClientVendorNumber AS ClientVendorNumber1
and
tt.ClientVendorNumber AS ClientVendorNumber2 .... and so on ....) and take
into account these new names in your very outer select statement.
Here it is supposed that ClientVendorNumber is crucial for discerning the two
rows (see the part in bold).
Anyway, I hope that the main idea is clear enough.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-26 : 06:14:21

Michael:

a nice sample on your link, encouraging to learn more...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources