Page 1 of 1

Could someone help me understand this code?

Posted: Mon Jan 08, 2007 10:30 am
by daedalus__
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. :/

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"
		)
	)
);
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.

Code: Select all

[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description], 
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!

Posted: Mon Jan 08, 2007 10:57 am
by wtf
I believe [] would be equivalent of `` ( backticks ). From what I remember if the column name is two letter word ( or more ) and separated you'd have to use [column name], otherwise you get a query error.

Posted: Mon Jan 08, 2007 11:05 am
by Luke
lol wtf... wtf is in your sig?

/offtopic

Posted: Mon Jan 08, 2007 11:06 am
by daedalus__
memememe or himhimhimhimhim?

Posted: Mon Jan 08, 2007 11:20 am
by jayshields
Daedalus- wrote:memememe or himhimhimhimhim?
notice the confusing username reference ;)

Posted: Mon Jan 08, 2007 11:29 am
by Chris Corbyn
[] are Microsoft's way of saying "we'd rather use our own syntax than go with a standard". I think PgSQL supports that syntax too actually.

EDIT | I do apologise, not one of us has actually answered what you asked :P I'll do some digging when I get in from work in an hour and make a nice hot brew :)

Posted: Mon Jan 08, 2007 3:24 pm
by Chris Corbyn
Any joy? Sorry, I got roped into setting up a windows machine for somebody as soon as I stepped foot through the door :( Why do geeks always get roped into out-of-work jobs? Grr...

Posted: Mon Jan 08, 2007 3:38 pm
by Begby

Code: Select all

[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description], 

! is an alternate syntax to [] I believe. So table!field is the same as [table].[field]. However, you have to use the brackets for tables with spaces in the names. Its interchangeable too, you can use [table]!field or table!field.

The reason for all the nested joins and such is because this query was created using what appears to be the query designer in access or something very much like it. On large queries this is what happens, it gets bad fast. You either use the query designer for everything, or you write your own SQL like a man. Sort of like in real life, you can either be a cowboy or a ballet dancer, you can't be both.

If you want to make this manageable, find out what tables the data is coming from, what the query is doing, and then rewrite the query from scratch. It will probably run more efficient anyways.

Posted: Mon Jan 08, 2007 4:28 pm
by volka
http://support.microsoft.com/kb/209935 wrote:In Microsoft Access, the ! (exclamation point) operator and the . (dot) operator are used as identifiers to indicate whether a particular reference is implicit (!) or explicit (.). Implicit referencing usually means that you are referring to a user-defined item. Explicit referencing usually means that you are referring to an item defined by Microsoft Access.
[] works like `` in mysql. You cannot write Rental Line!No_ because the white space is not allowed there, you have to mark the fieldname [Rental Line]
[Rental Line]!No_ & '-' & [Rental Line]!Description AS [ProdNo-Description]
adds a field ProdNo-Description to the result set consisting of the two fields [Rental Line]!No_ and [Rental Line]!Description and a - between them, e.g. 10-Downing Street

Re: Could someone help me understand this code?

Posted: Mon Jan 08, 2007 5:36 pm
by daedalus__
Daedalus- wrote: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. :/
rofl

Thanks for all the help guys! Some of that syntax is obnoxious.