SQL error
Locked
 
|
I am attempting to run a sql query after using the SQL QUOTE TEXT dialog. I am the following error. Is anyone familiar with running sql from within SPSS? My goal is to build an automated job that begins with gathering the data.
Error reads: >Warning. Command name: GET DATA >SQLExecDirect failed :[DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Category_Id'. CACHE. EXECUTE. My syntax: GET DATA /TYPE=ODBC /CONNECT='DSN=datawarehouse;DB=auctivadatamartetl;PORT=1433;Host=sac-dwsql\warehouse' /SQL= "with c1 as " +"( " +" " +"select pr.id as ProductID " +" ,pr.Name as Product " +" ,pr.price as Price " +" ,case when pr.price = 0 or pr.price is null then avg(mv.price) else pr.price end as Average_variant_price " +" ,pr.quantity as Qty " +" ,case when pr.quantity = 0 or pr.quantity is null then sum(mv.Quantity) else pr.Quantity end as variant_qty " +" ,lcd.level1_descr as Level1 " +" ,lcd.level2_descr as Level2 " +" ,lcd.level3_descr as Level3 " +" , st.ID as StoreID " +" ,st.Name as StoreName " +" ,pc.Category_Id as Category_Id " +" " +"----ADMETL " +"/* " +"from MKTP.marketplace_products pr " +"join MKTP.marketplace_stores st on (st.id = pr.Store_Id and st.isdeleted = 0) " +"and st.Store_State = 2 and ((st.[IsTestStore] <> 1 or st.[IsTestStore] is NULL)) " +"left join MKTP.marketplace_ProductCategories pc on (pc.product_id = pr.id and pc.isprimary = 1) " +"left join dwprd.listing_category_dimension lcd on (lcd.categoryid = pc.category_id) and lcd.current_flag = 'Y' " +"left join [dbo].[Market_Category_Breakdown] as lcd with (nolock) on (lcd.id = pc.category_id) " +"left join [MKTP].[marketplace_variants] mv on (pr.id = mv.product_id) " +"where pr.isdeleted = 0 and pr.visibility = 0 and lcd.Level1_Descr != 'Marketplace Test' " +"--and exists (select os.[MarketplaceStoreId] from [Chordus].[Market].[Store] st2 with (nolock) where os.[MarketplaceStoreId] = st2.[Id])--and pr.id = 1059391 " +" " +"*/ " +" " +"--Chordus " +"from [chordus].[Market].[Product] as pr " +"join [chordus].[Market].[Store] as st on (st.id = pr.Store_Id and st.isdeleted = 0) " +"and st.Store_State = 2 and ((st.[IsTestStore] <> 1 or st.[IsTestStore] is NULL)) " +"left join [chordus].[Market].[ProductCategory] as pc on (pc.product_id = pr.id) and (pc.isprimary = 1) " +"--left join [AuctivaDataMartETL].[dwprd].[listing_category_dimension] as lcd on (lcd.categoryid = pc.category_id) and lcd.current_flag = 'Y' " +"left join [chordus].[dbo].[Market_Category_Breakdown] as lcd with (nolock) on (lcd.id = pc.category_id) " +"left join [chordus].[Market].[Variant] as mv on (pr.id = mv.product_id) " +"where pr.isdeleted = 0 and pr.visibility = 0 and lcd.Level1_Descr != 'Marketplace Test' " +"group by pr.Id, pr.name, pr.price, pr.quantity, lcd.level1_descr, lcd.level2_descr, lcd.level3_descr, " +" st.ID, st.Name, pc.Category_Id " +") " +" " +"select * " +"--into [AuctivaDataMartETL].[oneoffs].[Seller_Assortment_sheet_report_dataset_September_22] " +"from c1 " +"where average_variant_price is not null and average_variant_price !=0 " +"order by Average_variant_price des " /ASSUMEDSTRWIDTH= 255. CACHE. EXECUTE. This email may contain confidential information for the sole use of the intended recipient(s). If you are not an intended recipient, please notify the sender and delete all copies immediately. ===================== 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 you want to join tables you should number the tables and variables. F.e.
=====================
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
GET DATA /TYPE = ODBC /CONNECT = 'Connection String' /SQL = ' SELECT' ' T0.firstName, T0.surnamePrefix, T0.surname, CAST (T0.randomNumber AS CHAR(10)) AS randomNumber' ' , T1.userName, T1.login' ', T2.postCode, T2.houseNumber' ', T4.phoneNumber ' FROM view.names T0' ' LEFT JOIN view.loginDetails T1 ON T0.userID = T1.userID' ' LEFT JOIN view.addressDetails T2 ON T0.userID = T2.userID' ' LEFT JOIN view.anotherTable T3 ON T2.interNetName = T3.interNetName' ' LEFT JOIN view.phoneDetails T4 ON T3.phoneName = T4.phoneName' ' WHERE T0.endDate IS NULL' ' AND T1.randomValue = 10'. Also: see the GET DATA entry in the manual on dates. If you want to select dates yous hould use double quotes in the SQL and single quotes in the date statement. F.e. " SELECT * from view.table" " WHERE date > {d '2014-10-10'}" If you use double quotes in the date statement it will not work. Regarding the error you receive, the first instance of Category_Id in your code is followed by +" " +"----ADMETL " +"/* " My guess is that SPSS can't handle ----ADMETL in the query and stops running there. If you remove the ADMETL, Chordus and "/*" statements in your code, and number the variables and tables, your SQL query should work. Date: Thu, 9 Oct 2014 09:21:30 -0700 From: [hidden email] Subject: SQL error To: [hidden email] I am attempting to run a sql query after using the SQL QUOTE TEXT dialog. I am the following error. Is anyone familiar with running sql from within SPSS? My goal is to build an automated job that begins with gathering the data.
Error reads: >Warning. Command name: GET DATA >SQLExecDirect failed :[DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Category_Id'. CACHE. EXECUTE. My syntax: GET DATA /TYPE=ODBC /CONNECT='DSN=datawarehouse;DB=auctivadatamartetl;PORT=1433;Host=sac-dwsql\warehouse' /SQL= "with c1 as " +"( " +" " +"select pr.id as ProductID " +" ,pr.Name as Product " +" ,pr.price as Price " +" ,case when pr.price = 0 or pr.price is null then avg(mv.price) else pr.price end as Average_variant_price " +" ,pr.quantity as Qty " +" ,case when pr.quantity = 0 or pr.quantity is null then sum(mv.Quantity) else pr.Quantity end as variant_qty " +" ,lcd.level1_descr as Level1 " +" ,lcd.level2_descr as Level2 " +" ,lcd.level3_descr as Level3 " +" , st.ID as StoreID " +" ,st.Name as StoreName " +" ,pc.Category_Id as Category_Id " +" " +"----ADMETL " +"/* " +"from MKTP.marketplace_products pr " +"join MKTP.marketplace_stores st on (st.id = pr.Store_Id and st.isdeleted = 0) " +"and st.Store_State = 2 and ((st.[IsTestStore] <> 1 or st.[IsTestStore] is NULL)) " +"left join MKTP.marketplace_ProductCategories pc on (pc.product_id = pr.id and pc.isprimary = 1) " +"left join dwprd.listing_category_dimension lcd on (lcd.categoryid = pc.category_id) and lcd.current_flag = 'Y' " +"left join [dbo].[Market_Category_Breakdown] as lcd with (nolock) on (lcd.id = pc.category_id) " +"left join [MKTP].[marketplace_variants] mv on (pr.id = mv.product_id) " +"where pr.isdeleted = 0 and pr.visibility = 0 and lcd.Level1_Descr != 'Marketplace Test' " +"--and exists (select os.[MarketplaceStoreId] from [Chordus].[Market].[Store] st2 with (nolock) where os.[MarketplaceStoreId] = st2.[Id])--and pr.id = 1059391 " +" " +"*/ " +" " +"--Chordus " +"from [chordus].[Market].[Product] as pr " +"join [chordus].[Market].[Store] as st on (st.id = pr.Store_Id and st.isdeleted = 0) " +"and st.Store_State = 2 and ((st.[IsTestStore] <> 1 or st.[IsTestStore] is NULL)) " +"left join [chordus].[Market].[ProductCategory] as pc on (pc.product_id = pr.id) and (pc.isprimary = 1) " +"--left join [AuctivaDataMartETL].[dwprd].[listing_category_dimension] as lcd on (lcd.categoryid = pc.category_id) and lcd.current_flag = 'Y' " +"left join [chordus].[dbo].[Market_Category_Breakdown] as lcd with (nolock) on (lcd.id = pc.category_id) " +"left join [chordus].[Market].[Variant] as mv on (pr.id = mv.product_id) " +"where pr.isdeleted = 0 and pr.visibility = 0 and lcd.Level1_Descr != 'Marketplace Test' " +"group by pr.Id, pr.name, pr.price, pr.quantity, lcd.level1_descr, lcd.level2_descr, lcd.level3_descr, " +" st.ID, st.Name, pc.Category_Id " +") " +" " +"select * " +"--into [AuctivaDataMartETL].[oneoffs].[Seller_Assortment_sheet_report_dataset_September_22] " +"from c1 " +"where average_variant_price is not null and average_variant_price !=0 " +"order by Average_variant_price des " /ASSUMEDSTRWIDTH= 255. CACHE. EXECUTE. This email may contain confidential information for the sole use of the intended recipient(s). If you are not an intended recipient, please notify the sender and delete all copies immediately. ===================== 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 |
|
|
Hi Peter, I use SQL within SPSS (version 20) pretty much every day. It is quite painful to edit and debug the SQL syntax. I found it much easier to use SQL to pull a base extract form my database and then use SPSS to cut\edit what I need. I removed a couple of details below like my specific DSN, UID, PWD, WSIS, and DATABASE but below is a simple example of what the syntax using the numbering system might look like. I don’t see an APP specified in your syntax. I have no idea if that is necessary. I have been unable to find good documentation on SQL use in SPSS. Might be easier to use the ODBC connection interface to select your tables, fields, and links and paste the result as syntax into SPSS. Editing will be much easier from there, especially if you use SPSS to cut\edit the data you need and then run the complete syntax as the automated job you would like. Makes my life much easier. GET DATA /TYPE=ODBC /CONNECT='DSN=;UID=;PWD=;APP=IBM SPSS Products: Statistics '+ 'Common;WSID=;DATABASE=' /SQL='SELECT T0.ID_NUMBER, T0.FIRST_NAME, T0.LAST_NAME, T1.ID_NUMBER AS ID_NUMBER1, T1.CURRENT_SEMESTER, T1.COURSE_NUMBER, T1.COURSE_SECTION, T1.FINAL_GRADE, T2.CURRENT_SEMESTER AS CURRENT_SEMESTER1, T2.COURSE_NUMBER AS COURSE_NUMBER1, '+ 'T2.COURSE_SECTION AS COURSE_SECTION1, T2.SITE_NUMBER, T2.PROF_ID_NUMBER, T2.COURSE_TITLE ' + 'FROM dbo.STBIOS T0, dbo.RGENRL T1, dbo.RGCRSE T2 WHERE T0.ID_NUMBER = T2.PROF_ID_NUMBER and T1.COURSE_NUMBER = T2.COURSE_NUMBER and T1.COURSE_SECTION = T2.COURSE_SECTION and '+ 'T1.CURRENT_SEMESTER = T2.CURRENT_SEMESTER and (T1.CURRENT_SEMESTER = 201510)' /ASSUMEDSTRWIDTH=255. CACHE. EXECUTE. Best, Alex Wagner Director of Institutional Research and Planning Fisher College 118 Beacon Street Boston, MA 02116 From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of kwame woei If you want to join tables you should number the tables and variables. F.e. +" " +"----ADMETL " +"/* "
Date: Thu, 9 Oct 2014 09:21:30 -0700 I am attempting to run a sql query after using the SQL QUOTE TEXT dialog. I am the following error. Is anyone familiar with running sql from within SPSS? My goal is to build an automated job that begins with gathering the data. Error reads: >Warning. Command name: GET DATA >SQLExecDirect failed :[DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Category_Id'. CACHE. EXECUTE. My syntax: GET DATA /TYPE=ODBC /CONNECT='DSN=datawarehouse;DB=auctivadatamartetl;PORT=1433;Host=sac-dwsql\warehouse' /SQL= "with c1 as " +"( " +" " +"select pr.id as ProductID " +" ,pr.Name as Product " +" ,pr.price as Price " +" ,case when pr.price = 0 or pr.price is null then avg(mv.price) else pr.price end as Average_variant_price " +" ,pr.quantity as Qty " +" ,case when pr.quantity = 0 or pr.quantity is null then sum(mv.Quantity) else pr.Quantity end as variant_qty " +" ,lcd.level1_descr as Level1 " +" ,lcd.level2_descr as Level2 " +" ,lcd.level3_descr as Level3 " +" , st.ID as StoreID " +" ,st.Name as StoreName " +" ,pc.Category_Id as Category_Id " +" " +"----ADMETL " +"/* " +"from MKTP.marketplace_products pr " +"join MKTP.marketplace_stores st on (st.id = pr.Store_Id and st.isdeleted = 0) " +"and st.Store_State = 2 and ((st.[IsTestStore] <> 1 or st.[IsTestStore] is NULL)) " +"left join MKTP.marketplace_ProductCategories pc on (pc.product_id = pr.id and pc.isprimary = 1) " +"left join dwprd.listing_category_dimension lcd on (lcd.categoryid = pc.category_id) and lcd.current_flag = 'Y' " +"left join [dbo].[Market_Category_Breakdown] as lcd with (nolock) on (lcd.id = pc.category_id) " +"left join [MKTP].[marketplace_variants] mv on (pr.id = mv.product_id) " +"where pr.isdeleted = 0 and pr.visibility = 0 and lcd.Level1_Descr != 'Marketplace Test' " +"--and exists (select os.[MarketplaceStoreId] from [Chordus].[Market].[Store] st2 with (nolock) where os.[MarketplaceStoreId] = st2.[Id])--and pr.id = 1059391 " +" " +"*/ " +" " +"--Chordus " +"from [chordus].[Market].[Product] as pr " +"join [chordus].[Market].[Store] as st on (st.id = pr.Store_Id and st.isdeleted = 0) " +"and st.Store_State = 2 and ((st.[IsTestStore] <> 1 or st.[IsTestStore] is NULL)) " +"left join [chordus].[Market].[ProductCategory] as pc on (pc.product_id = pr.id) and (pc.isprimary = 1) " +"--left join [AuctivaDataMartETL].[dwprd].[listing_category_dimension] as lcd on (lcd.categoryid = pc.category_id) and lcd.current_flag = 'Y' " +"left join [chordus].[dbo].[Market_Category_Breakdown] as lcd with (nolock) on (lcd.id = pc.category_id) " +"left join [chordus].[Market].[Variant] as mv on (pr.id = mv.product_id) " +"where pr.isdeleted = 0 and pr.visibility = 0 and lcd.Level1_Descr != 'Marketplace Test' " +"group by pr.Id, pr.name, pr.price, pr.quantity, lcd.level1_descr, lcd.level2_descr, lcd.level3_descr, " +" st.ID, st.Name, pc.Category_Id " +") " +" " +"select * " +"--into [AuctivaDataMartETL].[oneoffs].[Seller_Assortment_sheet_report_dataset_September_22] " +"from c1 " +"where average_variant_price is not null and average_variant_price !=0 " +"order by Average_variant_price des " /ASSUMEDSTRWIDTH= 255. CACHE. EXECUTE.
===================== 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 |
|
|
Thank you, Alex. I edited out the comments and "/" symbols and it ran successfully. However, I am now trying to run it as an automated job in Utilities>Production Facility and I keep getting this error : "Scarce system resources" and the production job fails. Any ideas? On Fri, Oct 10, 2014 at 10:46 AM, Alexander Wagner <[hidden email]> wrote:
This email may contain confidential information for the sole use of the intended recipient(s). If you are not an intended recipient, please notify the sender and delete all copies immediately. ===================== 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 |