macro into SQL statement

[Frank Milthorpe]

Hi

Your syntax got managled and it wasn't obvious how you continued onto
the subsequent lines.

Following are two examples that you should be able to adapt. I suggest
that you build up the syntax in steps, rather than in one full step.
There is/was a bug in some version(s) of SPSS where a line couldn't
finish with a macro variable. For example

...      CONNECT= !o_odbc+!o_user / UNENCRYPTED /      (works)

...      CONNECT= !o_odbc+!o_user
           / UNENCRYPTED /
 (does / did  NOT work)


I can't remember if this has been fixed.  I use the approach above to
make sure it does work.



--- Exhibit 1 ---
The following code works for writing to a specified access database.

DEFINE !access   ( )  'c:\mydata\my_big_database.mdb'
!ENDDEFINE.

SAVE TRANSLATE / TYPE=ODBC /
  CONNECT='DSN=MS Access Database;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;'
     + 'DBQ=' + !access +  ';'  /  TABLE = "Calendar" /
  REPLACE / MAP.



--- Exhibit 2 ---
The following code works for reading data from an Oracle database.

DEFINE !o_odbc  ( ) 'DSN=ORACLE - SPSS;HOST=ORACLE;'  !ENDDEFINE.
DEFINE !o_user  ( ) 'UID=username;PWD=password'          !ENDDEFINE.

GET CAPTURE ODBC /
    CONNECT= !o_odbc+!o_user / UNENCRYPTED /
  SELECT
    l.*
  FROM
    landuse.forecasts       l,
  ORDER BY
    l.year .



I hope this helps.

Cheers

Frank Milthorpe




News:
The majority of staff from the Transport and Population Data Centre
have moved to the Ministry of Transport. Contact details will be
progressively changed over the next few months.
Frank Milthorpe
Senior Manager, Transport Model Development
Transport Data Centre
NSW Ministry of  Transport
Direct:    [phone]     TDC Phone: [phone]
Fax:       [phone]

Street:   Level 7, Tower 2 (TNT Tower East), 1 Lawson Square, Redfern
NSW 2016
Postal:   GPO Box 1620, Sydney NSW 2001
Email:     [hidden email]
Web:      www.transport.nsw.gov.au/tdc





>>> Luca Meyer <[hidden email]> 11/07/2007 5:02 pm >>>
Hello,

I have this SQL query:

GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft Access;DBQ=
C:\TEMP\TEST1.MDB;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;'
/SQL = "SELECT * FROM TABLE1"
/ASSUMEDSTRWIDTH=255 .
CACHE.
EXE.

And I would like to have a macro parameter instead of the TEST1.MDB.

I have tried:

DEFINE !SQL (DB=!TOKENS(1))
!CONCAT ( "GET DATA /TYPE=ODBC /CONNECT='DSN=Database di Microsoft
Access;DBQ= C:\TEMP\",
!SQL,
";DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;'"
)
/SQL = "SELECT * FROM TABLE1"
/ASSUMEDSTRWIDTH=255 .
CACHE.
EXE.
!ENDDEFINE.

!SQL DB=TEST1.MDB.

!SQL DB=TEST2.MDB.

But it gives me errors that do not depend on the database, any
suggestions
on how I could handle this?

Thank you,

Luca

Mr. Luca MEYER
Market research, data analysis & more
HYPERLINK " http://www.lucameyer.com/"www.lucameyer.com - Tel:
+39.339.495.00.21




No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.10.2/893 - Release Date:
09/07/2007
17.22




------------------------------------------------------------------------------------------------------------------------------------------------
This message is intended for the addressee named and may contain
confidential/privileged information. If you are not the intended
recipient, please delete it and notify the sender.
Views expressed in this message are those of the individual sender, and
are not necessarily the views of the Department.
You should scan any attached files for viruses.
------------------------------------------------------------------------------------------------------------------------------------------------