-----Original Message----- From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of Spousta Jan Sent: Tuesday, October 18, 2005 2:13 AM To: SPSSX-L@LISTSERV.UGA.EDU Subject: Re: [SPSSX-L] Merging Files

Hi Mahbub, [>>>Peck, Jon] [snip]

GET DATA /TYPE=ODBC /CONNECT= 'DSN=MS Access Database;DBQ=c:\files\js.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;' /SQL = 'SELECT `AC`.`ACCNR` AS `ACCNR`, `SE`.`SECNR` AS `SECNR`' 'FROM `c:\files\js`.`ac` `AC`, `c:\files\js`.`se` `SE` ' .

(In my humble opinion, people constructing programming languages with so many apostrophes should be punished by law: they should pay one dollar for each useless character I must use in the expression.) [>>>Peck, Jon] In fact, the quotes are only necessary in the SQL expression when the names could be confused with reserved words such as FROM or the names contain otherwise illegal characters such as blanks. Unfortunately, and bizarrely in my opinion, the list of reserved words is long and data source specific.

In SPSS 13, we reduced the quoting produced by the Database Wizard to, we thought, a safe minimum to improve readability. We found subsequently that there were more reserved words than we knew about, although they seemed unlikely to show up as field names. Nevertheless, we found a way to get the entire list of reserved words for a data source and now quote only those names plus names with special characters. Even so, some databases are case sensitive in table names etc, and sometimes lower case names then need to be protected with quotes.

So, the safest thing to do is just to quote everything and not try to read the SQL. But at least in the example above, you would see no quotes within the SQL expression if generated by the wizard.

And, BTW, you can now write SQL expressions against the database as part of SAVE TRANSLATE, so if you put an SPSS dataset into a database, you can adjust properties or manipulate tables and queries at the same time.

Regards, Jon Peck SPSS, Inc.