Could someone help me understand this code?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Could someone help me understand this code?

Post 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!
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

lol wtf... wtf is in your sig?

/offtopic
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post by daedalus__ »

memememe or himhimhimhimhim?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

Daedalus- wrote:memememe or himhimhimhimhim?
notice the confusing username reference ;)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 :)
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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...
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: Could someone help me understand this code?

Post 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.
Post Reply