Joining Tables from Different Databases in the GET DATA command
Locked
Joining Tables from Different Databases in the GET DATA command
 
|
I’m trying to write SQL code within the GET DATA command that will pull data elements from two tables in separate databases. Below is the code that I tested in SQL manager before inserting it into the GET DATA command. When I run the
syntax, I get the following warning: >Warning. Command name: GET DATA >SQLExecDirect failed :[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '.'.
GET DATA /TYPE=ODBC /CONNECT='DSN=JJIS-Assessment;Description=JJIS;UID=;Trusted_Connection=Yes;APP=IBM SPSS Products: '+ 'Statistics Common;WSID=D80002060;DATABASE=Assessment;LANGUAGE=us_english;Network=DBMSSOCN' /SQL = 'SELECT T0.DJJID, T0.YouthAssessmentID, T0.YouthAssessmentTypeID, T0.AssessmentID, T0.ProgramCode, '+ 'T1.YthLastName, T1.YthFirstName, T1.YthMiddleName, T1.YthRace, T1.YthSex, T1.YthSSN, T1.YthDOB, T1.YthEthnicity, T1.CountyID' 'FROM Assessment.dbo.tblPACTYouthAssessment as T0 outer join Comreg.dbo.tblYouth as T1 ' ' on T0.DJJID = T1.DJJID ' 'WHERE T0.Deleted = 0' /ASSUMEDSTRWIDTH=255 . The examples I’ve found only reference two tables within the same database. Is it possible to read from two different databases? Thank you in advance for any advice and/or assistance. Have a Great Day! Ronald A. Morrell GOCII, Office of Research and Planning PH: (850) 717-2637 If life were any better, I’d be a twin. You know what, I AM! Learn more about DJJ’s Roadmap to System Excellence at
http://www.djj.state.fl.us/roadmap-to-system-excellence |
Re: Joining Tables from Different Databases in the GET DATA command
|
|
You cannot merge tables from different
databases in a single GET DATA command. You can read each database with
a separate GET DATA command and then use MATCH FILES or STAR JOIN to merge
the tables. Use DATASET NAME to give each one a name, and then refer to
them by the dataset names on the subsequent MATCH FILES or STAR JOIN command.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: "Morrell, Ronald" <[hidden email]> To: [hidden email] Date: 07/24/2014 04:18 PM Subject: Joining Tables from Different Databases in the GET DATA command Sent by: "SPSSX(r) Discussion" <[hidden email]> I’m trying to write SQL code within the GET DATA command that will pull data elements from two tables in separate databases. Below is the code that I tested in SQL manager before inserting it into the GET DATA command. When I run the syntax, I get the following warning: >Warning. Command name: GET DATA >SQLExecDirect failed :[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '.'. GET DATA /TYPE=ODBC /CONNECT='DSN=JJIS-Assessment;Description=JJIS;UID=;Trusted_Connection=Yes;APP=IBM SPSS Products: '+ 'Statistics Common;WSID=D80002060;DATABASE=Assessment;LANGUAGE=us_english;Network=DBMSSOCN' /SQL = 'SELECT T0.DJJID, T0.YouthAssessmentID, T0.YouthAssessmentTypeID, T0.AssessmentID, T0.ProgramCode, '+ 'T1.YthLastName, T1.YthFirstName, T1.YthMiddleName, T1.YthRace, T1.YthSex, T1.YthSSN, T1.YthDOB, T1.YthEthnicity, T1.CountyID' 'FROM Assessment.dbo.tblPACTYouthAssessment as T0 outer join Comreg.dbo.tblYouth as T1 ' ' on T0.DJJID = T1.DJJID ' 'WHERE T0.Deleted = 0' /ASSUMEDSTRWIDTH=255 . The examples I’ve found only reference two tables within the same database. Is it possible to read from two different databases? Thank you in advance for any advice and/or assistance. Have a Great Day! Ronald A. Morrell GOCII, Office of Research and Planning PH: (850) 717-2637 If life were any better, I’d be a twin. You know what, I AM! Learn more about DJJ’s Roadmap to System Excellence at http://www.djj.state.fl.us/roadmap-to-system-excellence ===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Joining Tables from Different Databases in the GET DATA command
|
|
Thanks Rick for the information, have a terrific weekend, Ron Morrell From: Rick Oliver [mailto:[hidden email]]
You cannot merge tables from different databases in a single GET DATA command. You can read each database with a separate GET DATA command and then use MATCH FILES or STAR
JOIN to merge the tables. Use DATASET NAME to give each one a name, and then refer to them by the dataset names on the subsequent MATCH FILES or STAR JOIN command.
===================== To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
|
| Free forum by Nabble | Edit this page |