August 23, 2011 at 2:27 am
Hi,
I have a View that I am trying to use to determine true debtor days from our database, I guess that is irrelevant, anyway..
I have joined a table (Detail) to 2 instances of a second table (Ledger Entries) as the data is in such a way as an Invoice can be raised then paid or a payment can be raised and then assigned an invoice, so I can start with a document type 1 (Payment) that is linked to the detail and then back to Ledger Entries where I want to pick up the opposite document type 2 (Invoice). It also works the opposite way, invoice to detail to payment. I also have used some case statements to ensure I get the document date and document number I want. My concern is that in 1 instance I am getting a payment date, linked to a payment date, which in my head looking at the SQL can't happen. My confusion is around the OR statement. I thought that it would apply everything in the where statement before the 'or' OR everything after but I'm not sure it's doing that, I tried putting brackets but SQL SMS took them out anyway.If there's some SQL genius out there who'd like to have a look at the SQL, here it is..
SELECT TOP (100) PERCENT dbo.[MVLive$Cust_ Ledger Entry].[Customer No_],
CASE [MVLive$Cust_ Ledger Entry].[Document Type] WHEN 1 THEN [MVLive$Cust_ Ledger Entry].[Posting Date] ELSE [MVLive$Cust_ Ledger Entry_1].[Posting Date]
END AS [Payment Date],
CASE [MVLive$Cust_ Ledger Entry].[Document Type] WHEN 1 THEN [MVLive$Cust_ Ledger Entry_1].[Document No_] ELSE [MVLive$Cust_ Ledger Entry].[Document No_]
END AS [Document No_],
CASE [MVLive$Cust_ Ledger Entry_1].[Document Type] WHEN 1 THEN [MVLive$Cust_ Ledger Entry].[Posting Date] ELSE [MVLive$Cust_ Ledger Entry_1].[Posting Date]
END AS [Invoice Date], [MVLive$Cust_ Ledger Entry_1].[Open]
FROM dbo.[MVLive$Cust_ Ledger Entry] INNER JOIN
dbo.[MVLive$Detailed Cust_ Ledg_ Entry] ON
dbo.[MVLive$Cust_ Ledger Entry].[Entry No_] = dbo.[MVLive$Detailed Cust_ Ledg_ Entry].[Applied Cust_ Ledger Entry No_] INNER JOIN
dbo.[MVLive$Cust_ Ledger Entry] AS [MVLive$Cust_ Ledger Entry_1] ON
dbo.[MVLive$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] = [MVLive$Cust_ Ledger Entry_1].[Entry No_] INNER JOIN
dbo.CustLedgDocTypes$ ON [MVLive$Cust_ Ledger Entry_1].[Document Type] = dbo.CustLedgDocTypes$.[Document Type] INNER JOIN
dbo.CustLedgDocTypes$ AS CustLedgDocTypes$_1 ON
dbo.[MVLive$Cust_ Ledger Entry].[Document Type] = CustLedgDocTypes$_1.[Document Type]
WHERE (dbo.[MVLive$Cust_ Ledger Entry].[Document Type] = 1) AND ([MVLive$Cust_ Ledger Entry_1].[Document Type] = 2) AND
(dbo.[MVLive$Cust_ Ledger Entry].[Open] = 0) AND ([MVLive$Cust_ Ledger Entry_1].[Open] = 0) OR
(dbo.[MVLive$Cust_ Ledger Entry].[Document Type] = 2) AND ([MVLive$Cust_ Ledger Entry_1].[Document Type] = 1) AND
(dbo.[MVLive$Cust_ Ledger Entry].[Open] = 0) AND ([MVLive$Cust_ Ledger Entry_1].[Open] = 0)
ORDER BY [Invoice Date]
Thanks in advance for any insight..
August 23, 2011 at 6:38 pm
Are you sure you encased your statements properly?
I've reformatted your query to make it a bit more readable, and placed the brackets in the appropriate places. Try it out and see if it still causes problems
SELECT
dbo.[MVLive$Cust_ Ledger Entry].[Customer No_],
CASE [MVLive$Cust_ Ledger Entry].[Document Type] WHEN 1 THEN [MVLive$Cust_ Ledger Entry].[Posting Date] ELSE [MVLive$Cust_ Ledger Entry_1].[Posting Date] END AS [Payment Date],
CASE [MVLive$Cust_ Ledger Entry].[Document Type] WHEN 1 THEN [MVLive$Cust_ Ledger Entry_1].[Document No_] ELSE [MVLive$Cust_ Ledger Entry].[Document No_] END AS [Document No_],
CASE [MVLive$Cust_ Ledger Entry_1].[Document Type] WHEN 1 THEN [MVLive$Cust_ Ledger Entry].[Posting Date] ELSE [MVLive$Cust_ Ledger Entry_1].[Posting Date] END AS [Invoice Date], [MVLive$Cust_ Ledger Entry_1].[Open]
FROM dbo.[MVLive$Cust_ Ledger Entry]
INNER JOIN dbo.[MVLive$Detailed Cust_ Ledg_ Entry] ON dbo.[MVLive$Cust_ Ledger Entry].[Entry No_] = dbo.[MVLive$Detailed Cust_ Ledg_ Entry].[Applied Cust_ Ledger Entry No_]
INNER JOIN dbo.[MVLive$Cust_ Ledger Entry] AS [MVLive$Cust_ Ledger Entry_1] ON dbo.[MVLive$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] = [MVLive$Cust_ Ledger Entry_1].[Entry No_]
INNER JOIN dbo.CustLedgDocTypes$ ON [MVLive$Cust_ Ledger Entry_1].[Document Type] = dbo.CustLedgDocTypes$.[Document Type]
INNER JOIN dbo.CustLedgDocTypes$ AS CustLedgDocTypes$_1 ON dbo.[MVLive$Cust_ Ledger Entry].[Document Type] = CustLedgDocTypes$_1.[Document Type]
WHERE(
dbo.[MVLive$Cust_ Ledger Entry].[Document Type] = 1
AND [MVLive$Cust_ Ledger Entry_1].[Document Type] = 2
AND dbo.[MVLive$Cust_ Ledger Entry].[Open] = 0
AND [MVLive$Cust_ Ledger Entry_1].[Open] = 0
)
OR
(
dbo.[MVLive$Cust_ Ledger Entry].[Document Type] = 2
AND [MVLive$Cust_ Ledger Entry_1].[Document Type] = 1
AND dbo.[MVLive$Cust_ Ledger Entry].[Open] = 0
AND [MVLive$Cust_ Ledger Entry_1].[Open] = 0
)
ORDER BY
[Invoice Date]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply