Could someone help me understand this code?
Posted: Mon Jan 08, 2007 10:30 am
Hi,
I have a query from an Access Database. It queries this stupid program called Navision ODBC Database and then returns the results to a spreadsheet. We recently upgraded this program a full two versions and now these queries need to be updated. The problems I am facing are that I do not know how the tables have changed and I can't understand this SQL. Could someone please explain some of this to me.
I understand what the brackets are for and what the Iif statement is doing but beyond that I am pretty lost.
I think that this was generated by Access. :/
I am failing to understand what the exclamation points are doing there. I know that an exclamation point is a wildcard character and I know what it is used for but this doesn't make any sense.
I'm also confused by the end with the obnoxious amount of parentheses and nested(?) join statements.
There are also some brackets that seem out of place but I can not say for sure because I only use basic sql commands.
I understand if nobody wants to help, I sure as hell do not want to be doing this but thank you very much to anyone who can and does help!
I have a query from an Access Database. It queries this stupid program called Navision ODBC Database and then returns the results to a spreadsheet. We recently upgraded this program a full two versions and now these queries need to be updated. The problems I am facing are that I do not know how the tables have changed and I can't understand this SQL. Could someone please explain some of this to me.
I understand what the brackets are for and what the Iif statement is doing but beyond that I am pretty lost.
I think that this was generated by Access. :/
Code: Select all
SELECT
[Rental Header].[Gen_ Bus_ Posting Group] AS [Branch(GBPG)],
[Rental Header].No_ AS [Order No],
[Rental Header].[Shortcut Dimension 2 Code] AS [Project Code],
[Project Code] & '-' & [Order No] AS [Job-Cust-OrdNo],
[Rental Header].[Sell-to Customer No_],
[Rental Header].[Sell-to Customer Name],
[Rental Header].[Sell-to Contact],
[Rental Header].[Phone No_],
[Rental Header].[Sell-to Phone No_],
[Sell-to Customer No_] & '-' & [Sell-to Customer Name] AS [CustNo-Name],
[Rental Header].[Expected Return Date],
[Rental Line].[No_] AS [Product No],
[Rental Line].Description,
[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description],
[Rental Line].[Line No_],
[Rental Line].[From Date],
[Rental Line].[Thru Date],
[Rental Line].[Declared Value],
[Rental Line].[Unit Price],
[Rental Line].[Shipment Date],
[Rental Line].[Return Date],
[Shipment Ledger Entry].[No_] AS [FA No],
[Fixed Asset].[Description] AS [FA Description],
[Fixed Asset].[Serial No_],
[Shipment Ledger Entry]!No_ & '-' & [Fixed Asset]!Description & '-' & [Fixed Asset]![Serial No_] AS [FANo-Description-SN],
[Fixed Asset].[On Rent], [Fixed Asset].[Bill-to Customer No_] AS FACustNo, Customer.Name,
[Shipment Ledger Entry].Quantity AS [Ship Qty],
[Shipment Ledger Entry].[Posting Date],
[Shipment Ledger Entry].[Transaction Type],
[Shipment Ledger Entry].Open,
[Rental Line].[Deal Code],
IIf (IsNull([Rental Price]!Price),0,[Rental Price]!Price) AS [Book Rate],
[Unit Price]-[Book Rate] AS [Price Difference],
[Customer].[Salesperson Code],
[Fixed Asset].[FA Class Code],
[Rental Header].[Insurance Expiration Date],
[Rental Header].[Signed Rental Order],
[Rental Header].[Tax Area Code]
FROM ([Rental Price]
RIGHT JOIN
(
(
[Shipment Ledger Entry]
INNER JOIN
(
[Rental Line] INNER JOIN [Rental Header] ON [Rental Line].[Document No_] = [Rental Header].No_
)
ON
(
[Shipment Ledger Entry].[Rental Line No_] = [Rental Line].[Line No_])
AND ([Shipment Ledger Entry].[Document No_] = [Rental Line].[Document No_])
AND ([Shipment Ledger Entry].[Document No_] = [Rental Line].[Document No_])
)
INNER JOIN [Fixed Asset] ON [Shipment Ledger Entry].No_ = [Fixed Asset].No_
) ON [Rental Price].No_ = [Rental Line].No_
)
LEFT JOIN Customer ON [Fixed Asset].[Bill-to Customer No_] = Customer.No_
WHERE
(
(
(
[Rental Header].No_)="RO-05848"
)
)
);
Code: Select all
[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description],
There are also some brackets that seem out of place but I can not say for sure because I only use basic sql commands.
I understand if nobody wants to help, I sure as hell do not want to be doing this but thank you very much to anyone who can and does help!