ODBC sql syntax problem
Locked
 
|
I've run the first sql script successfully with the result of an extremely large file.
I tried to add selection criteria to the end of the where clause but I keep getting the same message about not continuing my line correctly. I suspect theres's more wrong than that. Could someone help add my highlighted code in the second script correctly? And, I'd really like my date selection to be ge 12/01/2005. Thanks. Successful sql script GET DATA /TYPE=ODBC /CONNECT= 'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER ;DBA=W;APA=T;EXC'+ '=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;' 'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;' /SQL = 'SELECT T5568.CWIN, T5568.EFF_BGN_DT, T5568.EFF_END_DT, T5568.E'+ 'MP_STS_CD, T5568.HIST_IND, T4874.PGM_TYP_CD, ' 'T4874.CS_ID, T4874.CWIN AS CWIN1, T4874.ELIG_STS_CD, T5187.CSLD_ID, '+ 'T5783.OFF_ID, T5783.CSLD_ID AS CSLD_ID1, ' 'T5187.CS_ID AS CS_ID1, T7092.OFF_NM, T7092.OFF_ID AS OFF_ID1 FROM CIS'+ '01.INDV_WRK_REQR T5568, CIS01.AG_INDV_ELIG_RSLT ' 'T4874, CIS01.CS_PGM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 WHERE'+ ' T4874.CWIN = T5568.CWIN and T5187.CS_ID = ' 'T4874.CS_ID and T5783.CSLD_ID = T5187.CSLD_ID and T7092.OFF_ID = T5783'+ '.OFF_ID and (T5568.CWIN = T4874.CWIN AND ' 'T4874.CS_ID = T5187.CS_ID AND T5187.CSLD_ID = T5783.CSLD_ID AND T5783.OF'+ 'F_ID = T7092.OFF_ID )' /ASSUMEDSTRWIDTH=255 . CACHE. //May be removed but insures data integrity. Unsuccessful script due to addition on last line GET DATA /TYPE=ODBC /CONNECT= 'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER ;DBA=W;APA=T;EXC'+ '=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;' 'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;' /SQL = 'SELECT T5568.CWIN, T5568.EFF_BGN_DT, T5568.EFF_END_DT, T5568.E'+ 'MP_STS_CD, T5568.HIST_IND, T4874.PGM_TYP_CD, ' 'T4874.CS_ID, T4874.CWIN AS CWIN1, T4874.ELIG_STS_CD, T5187.CSLD_ID, '+ 'T5783.OFF_ID, T5783.CSLD_ID AS CSLD_ID1, ' 'T5187.CS_ID AS CS_ID1, T7092.OFF_NM, T7092.OFF_ID AS OFF_ID1 FROM CIS'+ '01.INDV_WRK_REQR T5568, CIS01.AG_INDV_ELIG_RSLT ' 'T4874, CIS01.CS_PGM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 WHERE'+ ' T4874.CWIN = T5568.CWIN and T5187.CS_ID = ' 'T4874.CS_ID and T5783.CSLD_ID = T5187.CSLD_ID and T7092.OFF_ID = T5783'+ '.OFF_ID and (T5568.CWIN = T4874.CWIN AND ' 'T4874.CS_ID = T5187.CS_ID AND T5187.CSLD_ID = T5783.CSLD_ID AND T5783.OF'+ 'F_ID = T7092.OFF_ID )' and t4874.pgm_typ_cd = CW CR WW AND ( {fn YEAR(T5568.EFF_BGN_DT)} GE 2005 )' /ASSUMEDSTRWIDTH=255 . CACHE. //May be removed but insures data integrity. Sydelle Raffe, Alameda County Social Services Agency Information Services Division, Office of Data Management e:mail: [hidden email] phone: 510-271-9174 fax: 510-271-9107 If you have a request for information, please submit an ODM request form at: https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm ====================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 |
|
|
Sydelle,
SPSS requires quotes around each line. I like to edit for readability like this: .........GM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 ' ' WHERE ' ' T4874.CWIN = T5568.CWIN ' ' and T5187.CS_ID = T4874.CS_ID ' ' and T5783.CSLD_ID = T5187.CSLD_ID ' ' and T7092.OFF_ID = T5783.OFF_ID ' ' and ( T5568.CWIN = T4874.CWIN ' ' and T4874.CS_ID = T5187.CS_ID ' ' and T5187.CSLD_ID = T5783.CSLD_ID ' ' and T5783.OFF_ID = T7092.OFF_ID ) ' ' and t4874.pgm_typ_cd = CW CR WW ' ' AND ( {YEAR(T5568.EFF_BGN_DT)} GE 2005 );' /ASSUMEDSTRWIDTH=255. CACHE. I haven't evaluated the actual sql...see if getting the quotes right fixes it...post if it doesn't. -Gary ===================== 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: ODBC sql syntax problem
|
|
It's those quotes I was having a hard time with -- couldn't find any help in a couple of things I looked at.
I'll try this. Thanks. -----Original Message----- From: Hal 9000 [mailto:[hidden email]] Sent: Wednesday, December 12, 2007 2:41 PM To: Raffe, Sydelle, SSA Cc: [hidden email] Subject: Re: ODBC sql syntax problem Sydelle, SPSS requires quotes around each line. I like to edit for readability like this: .........GM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 ' ' WHERE ' ' T4874.CWIN = T5568.CWIN ' ' and T5187.CS_ID = T4874.CS_ID ' ' and T5783.CSLD_ID = T5187.CSLD_ID ' ' and T7092.OFF_ID = T5783.OFF_ID ' ' and ( T5568.CWIN = T4874.CWIN ' ' and T4874.CS_ID = T5187.CS_ID ' ' and T5187.CSLD_ID = T5783.CSLD_ID ' ' and T5783.OFF_ID = T7092.OFF_ID ) ' ' and t4874.pgm_typ_cd = CW CR WW ' ' AND ( {YEAR(T5568.EFF_BGN_DT)} GE 2005 );' /ASSUMEDSTRWIDTH=255. CACHE. I haven't evaluated the actual sql...see if getting the quotes right fixes it...post if it doesn't. -Gary ===================== 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 |
|
|
It would appear that the text of documentation for the SQL subcommand is somewhat misleading on this point. Each line of the SQL statement must be enclosed in quotes, not each "SQL statement" as the documentation states. Also, the plus sign between quoted strings on separate lines is not required here.
________________________________ From: SPSSX(r) Discussion on behalf of Raffe, Sydelle, SSA Sent: Wed 12/12/2007 4:42 PM To: [hidden email] Subject: Re: ODBC sql syntax problem It's those quotes I was having a hard time with -- couldn't find any help in a couple of things I looked at. I'll try this. Thanks. -----Original Message----- From: Hal 9000 [mailto:[hidden email]] Sent: Wednesday, December 12, 2007 2:41 PM To: Raffe, Sydelle, SSA Cc: [hidden email] Subject: Re: ODBC sql syntax problem Sydelle, SPSS requires quotes around each line. I like to edit for readability like this: .........GM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 ' ' WHERE ' ' T4874.CWIN = T5568.CWIN ' ' and T5187.CS_ID = T4874.CS_ID ' ' and T5783.CSLD_ID = T5187.CSLD_ID ' ' and T7092.OFF_ID = T5783.OFF_ID ' ' and ( T5568.CWIN = T4874.CWIN ' ' and T4874.CS_ID = T5187.CS_ID ' ' and T5187.CSLD_ID = T5783.CSLD_ID ' ' and T5783.OFF_ID = T7092.OFF_ID ) ' ' and t4874.pgm_typ_cd = CW CR WW ' ' AND ( {YEAR(T5568.EFF_BGN_DT)} GE 2005 );' /ASSUMEDSTRWIDTH=255. CACHE. I haven't evaluated the actual sql...see if getting the quotes right fixes it...post if it doesn't. -Gary ===================== 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 ====================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 |
|
|
Oops. Wait a minute. The GET TRANSLATE, SQL subcommand documentation is correct. It's the SAVE TRANSLATE, SQL subcommand documenation that is not quite right. The GET TRANSLATE, SQL subcommand documenation states:
* Each line of SQL must be enclosed in quotation marks and cannot exceed 255 characters. * When the command is processed, all of the lines of the SQL statement are merged together in a very literal fashion; so each line should either begin or end with a blank space where spaces should occur between specifications. ________________________________ From: Oliver, Richard Sent: Wed 12/12/2007 7:48 PM To: Raffe, Sydelle, SSA; [hidden email] Cc: Oliver, Richard Subject: RE: Re: ODBC sql syntax problem It would appear that the text of documentation for the SQL subcommand is somewhat misleading on this point. Each line of the SQL statement must be enclosed in quotes, not each "SQL statement" as the documentation states. Also, the plus sign between quoted strings on separate lines is not required here. ________________________________ From: SPSSX(r) Discussion on behalf of Raffe, Sydelle, SSA Sent: Wed 12/12/2007 4:42 PM To: [hidden email] Subject: Re: ODBC sql syntax problem It's those quotes I was having a hard time with -- couldn't find any help in a couple of things I looked at. I'll try this. Thanks. -----Original Message----- From: Hal 9000 [mailto:[hidden email]] Sent: Wednesday, December 12, 2007 2:41 PM To: Raffe, Sydelle, SSA Cc: [hidden email] Subject: Re: ODBC sql syntax problem Sydelle, SPSS requires quotes around each line. I like to edit for readability like this: .........GM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 ' ' WHERE ' ' T4874.CWIN = T5568.CWIN ' ' and T5187.CS_ID = T4874.CS_ID ' ' and T5783.CSLD_ID = T5187.CSLD_ID ' ' and T7092.OFF_ID = T5783.OFF_ID ' ' and ( T5568.CWIN = T4874.CWIN ' ' and T4874.CS_ID = T5187.CS_ID ' ' and T5187.CSLD_ID = T5783.CSLD_ID ' ' and T5783.OFF_ID = T7092.OFF_ID ) ' ' and t4874.pgm_typ_cd = CW CR WW ' ' AND ( {YEAR(T5568.EFF_BGN_DT)} GE 2005 );' /ASSUMEDSTRWIDTH=255. CACHE. I haven't evaluated the actual sql...see if getting the quotes right fixes it...post if it doesn't. -Gary ===================== 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 ====================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 |
|
|
Arghh. One more correction. I meant GET DATA, not GET TRANSLATE.
________________________________ From: Oliver, Richard Sent: Wed 12/12/2007 7:52 PM To: Oliver, Richard; Raffe, Sydelle, SSA; [hidden email] Subject: RE: Re: ODBC sql syntax problem Oops. Wait a minute. The GET TRANSLATE, SQL subcommand documentation is correct. It's the SAVE TRANSLATE, SQL subcommand documenation that is not quite right. The GET TRANSLATE, SQL subcommand documenation states: * Each line of SQL must be enclosed in quotation marks and cannot exceed 255 characters. * When the command is processed, all of the lines of the SQL statement are merged together in a very literal fashion; so each line should either begin or end with a blank space where spaces should occur between specifications. ________________________________ From: Oliver, Richard Sent: Wed 12/12/2007 7:48 PM To: Raffe, Sydelle, SSA; [hidden email] Cc: Oliver, Richard Subject: RE: Re: ODBC sql syntax problem It would appear that the text of documentation for the SQL subcommand is somewhat misleading on this point. Each line of the SQL statement must be enclosed in quotes, not each "SQL statement" as the documentation states. Also, the plus sign between quoted strings on separate lines is not required here. ________________________________ From: SPSSX(r) Discussion on behalf of Raffe, Sydelle, SSA Sent: Wed 12/12/2007 4:42 PM To: [hidden email] Subject: Re: ODBC sql syntax problem It's those quotes I was having a hard time with -- couldn't find any help in a couple of things I looked at. I'll try this. Thanks. -----Original Message----- From: Hal 9000 [mailto:[hidden email]] Sent: Wednesday, December 12, 2007 2:41 PM To: Raffe, Sydelle, SSA Cc: [hidden email] Subject: Re: ODBC sql syntax problem Sydelle, SPSS requires quotes around each line. I like to edit for readability like this: .........GM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 ' ' WHERE ' ' T4874.CWIN = T5568.CWIN ' ' and T5187.CS_ID = T4874.CS_ID ' ' and T5783.CSLD_ID = T5187.CSLD_ID ' ' and T7092.OFF_ID = T5783.OFF_ID ' ' and ( T5568.CWIN = T4874.CWIN ' ' and T4874.CS_ID = T5187.CS_ID ' ' and T5187.CSLD_ID = T5783.CSLD_ID ' ' and T5783.OFF_ID = T7092.OFF_ID ) ' ' and t4874.pgm_typ_cd = CW CR WW ' ' AND ( {YEAR(T5568.EFF_BGN_DT)} GE 2005 );' /ASSUMEDSTRWIDTH=255. CACHE. I haven't evaluated the actual sql...see if getting the quotes right fixes it...post if it doesn't. -Gary ===================== 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 ====================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: ODBC sql syntax problem
|
|
In reply to this post by Hal 9000
Hi Sydelle,
In addition: I don't think the semicolon (;) at the end of the WHERE clause should be used. And also, although it works, it is generally not recommended to use the ANSI notation for inner joins. An INNER JOIN statement is more readable. The exact syntax depends on your sql dialect. Cheers!! Albert-Jan --- Hal 9000 <[hidden email]> wrote: > Sydelle, > > SPSS requires quotes around each line. I like to > edit for readability like this: > > .........GM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF > T7092 ' > ' WHERE ' > ' T4874.CWIN = T5568.CWIN ' > ' and T5187.CS_ID = T4874.CS_ID ' > ' and T5783.CSLD_ID = T5187.CSLD_ID ' > ' and T7092.OFF_ID = T5783.OFF_ID ' > ' and ( T5568.CWIN = T4874.CWIN ' > ' and T4874.CS_ID = T5187.CS_ID ' > ' and T5187.CSLD_ID = T5783.CSLD_ID ' > ' and T5783.OFF_ID = T7092.OFF_ID ) ' > ' and t4874.pgm_typ_cd = CW CR WW ' > ' AND ( {YEAR(T5568.EFF_BGN_DT)} GE 2005 );' > /ASSUMEDSTRWIDTH=255. > CACHE. > > I haven't evaluated the actual sql...see if getting > the quotes right > fixes it...post if it doesn't. > > -Gary > > ===================== > 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 > Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ===================== 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 |
|
|
In reply to this post by Oliver, Richard
Hi list,
Below is a GET DATA syntax about which I have two questions: 1. the connect string is supposed to work from each computer with each user, but it's not. I can use it from each computer, but my colleague can't. Any idea why? I removed the WSID from the syntax already, but the syntax still appears to depend on my credentials. Any suggestions? 2. A minor nuisance: how can I use the apostrophe in a WHERE clause? The example below will not work because of the "Rock 'n roll" part. I tried using SET QUOTED_IDENTIFIER OFF but I don't think it works. Of course a LIKE solution works, but I don't "like" that solution too much ;-) get data /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' /sql = ' SET QUOTED_IDENTIFIER OFF ' ' SELECT artist, album ' ' FROM music ' ' WHERE genre IN ("Rock 'n roll", "Techno") ' /assumedstrwidth=255 . cache. exe. * this code fragment does work: ' OR genre LIKE ("Rock _n roll") ' Cheers!! Albert-Jan Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ===================== 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 |
|
|
Hello Albert-Jan,
Have you tried to double the single quote within the string like in: ... ' WHERE genre IN ("Rock ''n roll", "Techno") ' ... Often the boundary characters of something als functions as an escape character. I.e. when de single quote (') is the boundary character, you can use the double version ('') to get one version of it. I did'nt test it as i am more into jazz and blues... If you want to create the syntax automagically, you would need to devise a function to double the escape character (often called Escape() and to undo it (change double escape characters into single ones) you would devise an unescape function (Unescape()). Regards, Kees de Boer ________________________________ Ing. C.P.J. (Kees) de Boer EMGO, VUmc Datamanagement & Systeembeheer D-451 tel. 020-44 49828 "Geloof: Geloven zonder bewijs in wat iemand die spreekt zonder kennis heeft gezegd over zaken die onvergelijkbaar zijn." Ambrose Bierce -----Oorspronkelijk bericht----- Van: SPSSX(r) Discussion [mailto:[hidden email]]Namens Albert-jan Roskam Verzonden: donderdag 13 december 2007 10:11 Aan: [hidden email] Onderwerp: Apostrophes in SQL Hi list, Below is a GET DATA syntax about which I have two questions: 1. the connect string is supposed to work from each computer with each user, but it's not. I can use it from each computer, but my colleague can't. Any idea why? I removed the WSID from the syntax already, but the syntax still appears to depend on my credentials. Any suggestions? 2. A minor nuisance: how can I use the apostrophe in a WHERE clause? The example below will not work because of the "Rock 'n roll" part. I tried using SET QUOTED_IDENTIFIER OFF but I don't think it works. Of course a LIKE solution works, but I don't "like" that solution too much ;-) get data /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' /sql = ' SET QUOTED_IDENTIFIER OFF ' ' SELECT artist, album ' ' FROM music ' ' WHERE genre IN ("Rock 'n roll", "Techno") ' /assumedstrwidth=255 . cache. exe. * this code fragment does work: ' OR genre LIKE ("Rock _n roll") ' Cheers!! Albert-Jan Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ===================== 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 ===================== 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 |
|
|
If that doesn't work, try using the accent character (same key as the tilde on my computer) instead of quotes to enclose the line:
`WHERE genre IN ("Rock 'n roll", "Techno") ` . ________________________________ From: SPSSX(r) Discussion on behalf of Boer, C.P.J. de Sent: Thu 12/13/2007 5:08 AM To: [hidden email] Subject: Re: Apostrophes in SQL Hello Albert-Jan, Have you tried to double the single quote within the string like in: ... ' WHERE genre IN ("Rock ''n roll", "Techno") ' ... Often the boundary characters of something als functions as an escape character. I.e. when de single quote (') is the boundary character, you can use the double version ('') to get one version of it. I did'nt test it as i am more into jazz and blues... If you want to create the syntax automagically, you would need to devise a function to double the escape character (often called Escape() and to undo it (change double escape characters into single ones) you would devise an unescape function (Unescape()). Regards, Kees de Boer ________________________________ Ing. C.P.J. (Kees) de Boer EMGO, VUmc Datamanagement & Systeembeheer D-451 tel. 020-44 49828 "Geloof: Geloven zonder bewijs in wat iemand die spreekt zonder kennis heeft gezegd over zaken die onvergelijkbaar zijn." Ambrose Bierce -----Oorspronkelijk bericht----- Van: SPSSX(r) Discussion [mailto:[hidden email]]Namens Albert-jan Roskam Verzonden: donderdag 13 december 2007 10:11 Aan: [hidden email] Onderwerp: Apostrophes in SQL Hi list, Below is a GET DATA syntax about which I have two questions: 1. the connect string is supposed to work from each computer with each user, but it's not. I can use it from each computer, but my colleague can't. Any idea why? I removed the WSID from the syntax already, but the syntax still appears to depend on my credentials. Any suggestions? 2. A minor nuisance: how can I use the apostrophe in a WHERE clause? The example below will not work because of the "Rock 'n roll" part. I tried using SET QUOTED_IDENTIFIER OFF but I don't think it works. Of course a LIKE solution works, but I don't "like" that solution too much ;-) get data /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' /sql = ' SET QUOTED_IDENTIFIER OFF ' ' SELECT artist, album ' ' FROM music ' ' WHERE genre IN ("Rock 'n roll", "Techno") ' /assumedstrwidth=255 . cache. exe. * this code fragment does work: ' OR genre LIKE ("Rock _n roll") ' Cheers!! Albert-Jan Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ===================== 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 ===================== 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 ====================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 |
|
|
In reply to this post by Boer, CPJ de
I've had to use the apostrophe twice ('') instead of the double quote
(") to correctly enclose strings in SQL statements. I haven't had to use the two apostrophes for a string containing a single aposprophe though. The easiest approach would be add a numeric field to the table and query against that :~) --jim -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Boer, C.P.J. de Sent: Thursday, December 13, 2007 5:08 AM To: [hidden email] Subject: Re: Apostrophes in SQL Hello Albert-Jan, Have you tried to double the single quote within the string like in: ... ' WHERE genre IN ("Rock ''n roll", "Techno") ' ... Often the boundary characters of something als functions as an escape character. I.e. when de single quote (') is the boundary character, you can use the double version ('') to get one version of it. I did'nt test it as i am more into jazz and blues... If you want to create the syntax automagically, you would need to devise a function to double the escape character (often called Escape() and to undo it (change double escape characters into single ones) you would devise an unescape function (Unescape()). Regards, Kees de Boer ________________________________ Ing. C.P.J. (Kees) de Boer EMGO, VUmc Datamanagement & Systeembeheer D-451 tel. 020-44 49828 "Geloof: Geloven zonder bewijs in wat iemand die spreekt zonder kennis heeft gezegd over zaken die onvergelijkbaar zijn." Ambrose Bierce -----Oorspronkelijk bericht----- Van: SPSSX(r) Discussion [mailto:[hidden email]]Namens Albert-jan Roskam Verzonden: donderdag 13 december 2007 10:11 Aan: [hidden email] Onderwerp: Apostrophes in SQL Hi list, Below is a GET DATA syntax about which I have two questions: 1. the connect string is supposed to work from each computer with each user, but it's not. I can use it from each computer, but my colleague can't. Any idea why? I removed the WSID from the syntax already, but the syntax still appears to depend on my credentials. Any suggestions? 2. A minor nuisance: how can I use the apostrophe in a WHERE clause? The example below will not work because of the "Rock 'n roll" part. I tried using SET QUOTED_IDENTIFIER OFF but I don't think it works. Of course a LIKE solution works, but I don't "like" that solution too much ;-) get data /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' /sql = ' SET QUOTED_IDENTIFIER OFF ' ' SELECT artist, album ' ' FROM music ' ' WHERE genre IN ("Rock 'n roll", "Techno") ' /assumedstrwidth=255 . cache. exe. * this code fragment does work: ' OR genre LIKE ("Rock _n roll") ' Cheers!! Albert-Jan Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ________________________________________________________________________ ____________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ===================== 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 ===================== 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 ===================== 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 |
|
|
In reply to this post by Oliver, Richard
Databases vary in how they handle quotes. The SPSS ODBC code queries the database to find out how to quote a literal, so you can probably get your code to work by looking to see how SPSS quotes reserved words. Try (in the DB wizard) including a variable named FROM, for example, and see how SPSS quotes it when you paste the syntax.
But, since the quote in "Rock 'n roll" would match the SPSS outer quotes, it has to be doubled in order to get through properly to the database (you can't just swap the outer quotes, because the text has both kinds). HTH, Jon Peck -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Oliver, Richard Sent: Thursday, December 13, 2007 5:32 AM To: [hidden email] Subject: Re: [SPSSX-L] Apostrophes in SQL If that doesn't work, try using the accent character (same key as the tilde on my computer) instead of quotes to enclose the line: `WHERE genre IN ("Rock 'n roll", "Techno") ` . ________________________________ From: SPSSX(r) Discussion on behalf of Boer, C.P.J. de Sent: Thu 12/13/2007 5:08 AM To: [hidden email] Subject: Re: Apostrophes in SQL Hello Albert-Jan, Have you tried to double the single quote within the string like in: ... ' WHERE genre IN ("Rock ''n roll", "Techno") ' ... Often the boundary characters of something als functions as an escape character. I.e. when de single quote (') is the boundary character, you can use the double version ('') to get one version of it. I did'nt test it as i am more into jazz and blues... If you want to create the syntax automagically, you would need to devise a function to double the escape character (often called Escape() and to undo it (change double escape characters into single ones) you would devise an unescape function (Unescape()). Regards, Kees de Boer ________________________________ Ing. C.P.J. (Kees) de Boer EMGO, VUmc Datamanagement & Systeembeheer D-451 tel. 020-44 49828 "Geloof: Geloven zonder bewijs in wat iemand die spreekt zonder kennis heeft gezegd over zaken die onvergelijkbaar zijn." Ambrose Bierce -----Oorspronkelijk bericht----- Van: SPSSX(r) Discussion [mailto:[hidden email]]Namens Albert-jan Roskam Verzonden: donderdag 13 december 2007 10:11 Aan: [hidden email] Onderwerp: Apostrophes in SQL Hi list, Below is a GET DATA syntax about which I have two questions: 1. the connect string is supposed to work from each computer with each user, but it's not. I can use it from each computer, but my colleague can't. Any idea why? I removed the WSID from the syntax already, but the syntax still appears to depend on my credentials. Any suggestions? 2. A minor nuisance: how can I use the apostrophe in a WHERE clause? The example below will not work because of the "Rock 'n roll" part. I tried using SET QUOTED_IDENTIFIER OFF but I don't think it works. Of course a LIKE solution works, but I don't "like" that solution too much ;-) get data /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' /sql = ' SET QUOTED_IDENTIFIER OFF ' ' SELECT artist, album ' ' FROM music ' ' WHERE genre IN ("Rock 'n roll", "Techno") ' /assumedstrwidth=255 . cache. exe. * this code fragment does work: ' OR genre LIKE ("Rock _n roll") ' Cheers!! Albert-Jan Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ===================== 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 ===================== 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 ======= 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 ===================== 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 |
|
|
Dear Kees, Richard, Jon,
Thank you for your replies! I tested the options you mentioned. The test syntax below works. It contains an reserved sql word (count), which are double-quoted, and an apostrophe, which get (many) single quotes. Luckily cases like this don't happen very often! Cheers!! Albert-Jan ** appendix: test syntax. data list free / x. begin data 1 1 1 1 end data. * note (1) use of keyword "count" as variable name. * note (2) from' (with apostrophe) as string value. string count (a8). compute count = "from' ". save translate /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' / table = 'testtesttest' / replace . exe. get data /type=odbc /connect= 'driver=sql server;' 'server=myserver;' 'database = mydb;' 'trusted_connection=Yes' /sql = ' SELECT x, "count" from testtesttest ' ' WHERE ("count" = ''from'''''' ) ' /assumedstrwidth=255 . cache. exe. --- "Peck, Jon" <[hidden email]> wrote: > Databases vary in how they handle quotes. The SPSS > ODBC code queries the database to find out how to > quote a literal, so you can probably get your code > to work by looking to see how SPSS quotes reserved > words. Try (in the DB wizard) including a variable > named FROM, for example, and see how SPSS quotes it > when you paste the syntax. > > But, since the quote in "Rock 'n roll" would match > the SPSS outer quotes, it has to be doubled in order > to get through properly to the database (you can't > just swap the outer quotes, because the text has > both kinds). > > HTH, > Jon Peck > > -----Original Message----- > From: SPSSX(r) Discussion > [mailto:[hidden email]] On Behalf Of > Oliver, Richard > Sent: Thursday, December 13, 2007 5:32 AM > To: [hidden email] > Subject: Re: [SPSSX-L] Apostrophes in SQL > > If that doesn't work, try using the accent character > (same key as the tilde on my computer) instead of > quotes to enclose the line: > > `WHERE genre IN ("Rock 'n roll", "Techno") ` > > . > > ________________________________ > > From: SPSSX(r) Discussion on behalf of Boer, C.P.J. > de > Sent: Thu 12/13/2007 5:08 AM > To: [hidden email] > Subject: Re: Apostrophes in SQL > > > > Hello Albert-Jan, > > > Have you tried to double the single quote within the > string like in: > ... > ' WHERE genre IN ("Rock ''n roll", "Techno") ' > ... > > Often the boundary characters of something als > functions as an escape character. I.e. when de > single quote (') is the boundary character, you can > use the double version ('') to get one version of > it. > I did'nt test it as i am more into jazz and blues... > > If you want to create the syntax automagically, you > would need to devise a function to double the escape > character (often called Escape() and to undo it > (change double escape characters into single ones) > you would devise an unescape function (Unescape()). > > > Regards, > > Kees de Boer > ________________________________ > Ing. C.P.J. (Kees) de Boer > EMGO, VUmc > Datamanagement & Systeembeheer > D-451 tel. 020-44 49828 > > "Geloof: Geloven zonder bewijs in wat iemand die > spreekt zonder kennis heeft gezegd over zaken die > onvergelijkbaar zijn." Ambrose Bierce > > -----Oorspronkelijk bericht----- > Van: SPSSX(r) Discussion > [mailto:[hidden email]]Namens > Albert-jan Roskam > Verzonden: donderdag 13 december 2007 10:11 > Aan: [hidden email] > Onderwerp: Apostrophes in SQL > > > Hi list, > > Below is a GET DATA syntax about which I have two > questions: > 1. the connect string is supposed to work from each > computer with each user, but it's not. I can use it > from each computer, but my colleague can't. Any idea > why? I removed the WSID from the syntax already, but > the syntax still appears to depend on my > credentials. > Any suggestions? > > 2. A minor nuisance: how can I use the apostrophe in > a > WHERE clause? The example below will not work > because > of the "Rock 'n roll" part. I tried using SET > QUOTED_IDENTIFIER OFF but I don't think it works. Of > course a LIKE solution works, but I don't "like" > that > solution too much ;-) > > get data /type=odbc /connect= > 'driver=sql server;' > 'server=myserver;' > 'database = mydb;' > 'trusted_connection=Yes' > /sql = > ' SET QUOTED_IDENTIFIER OFF ' > ' SELECT artist, album ' > ' FROM music ' > ' WHERE genre IN ("Rock 'n roll", "Techno") ' > /assumedstrwidth=255 . > cache. > exe. > > * this code fragment does work: > ' OR genre LIKE ("Rock _n roll") ' > > Cheers!! > Albert-Jan > > Cheers! > Albert-Jan > > > Did you know that 87.166253% of all statistics claim > a precision of results that is not justified by the > method employed? [HELMUT RICHTER] > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > > > ____________________________________________________________________________________ > Never miss a thing. Make Yahoo your home page. > http://www.yahoo.com/r/hs > > ===================== > 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 > > ===================== > 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 > > ======= > 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 > > ===================== > 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 > Cheers! Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Did you know that 87.166253% of all statistics claim a precision of results that is not justified by the method employed? [HELMUT RICHTER] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |