Help Debugging Unexpected Symbol Error

Hello!
I am new to R, I have been able to piece together how to pull data using SQL languange and I can get it work on smaller piece of code. However, I have a big SQL query and it is throwing an error, I can't seem to figure out why. The query works just fine running it in other applications.

Run I try to run it I get this error:

Error: unexpected symbol in "um_ship, start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end, case when substring(sta"

Below is the piece that says is failing and below that is the full code. In addition I have this whole query in one line in the script.

um_ship, start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end, case when substring(sta
con <- dbConnect(odbc::odbc(),
        Driver = "SQL Server",
        server = "DATABASE", 
        Uid = 'USERID',
        Pwd = 'PASSWORD')

test <- dbGetQuery(con,'select worktype_id, a.assign_num,z.UserIDandName, a.client_id, cstnum, machineid, item_num, item_desc, seq_num, machineid, num_case, num_ship, start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end, case when substring(start_slot_id,3,2) <= substring(end_slot_id,3,2) then 'In_Sequence' else 'Not_In_Sequence' end as Picking_Out_Aisle_Sequence, case when Pick_Seq_Start <= Pick_Seq_End then 'In' when seq_num='1' then 'In' else 'Out' end as Picking_Out_of_Sequence from (select * from ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive bWhere B.wh_id in ('WM') and worktype_id in ('CSPICK') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565'))a join (select assign_num, sum(td) as sum_Travel_Distance, count(assign_num) as nbr_rows, sum(td)/count(distinct assign_num) as Assigment_travel from( select assign_num, case when substring(start_slot_id,1,1)<> SUBSTRING(end_slot_id,1,1) then 0 else travel_distance end as td from ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive b Where B.wh_id in ('WM') and worktype_id in ('CSPICK') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565')) a group by assign_num) b on a.assign_num = b.assign_num left outer join (select distinct assign_num, UserIDandName from ARCHIVE.dbo.Src_RP_LM_Detail_ARCHIVE where loc in ('WM') AND report_date>=DATEADD(DAY,-7, GETDATE()))z on z.assign_num=a.assign_num left outer join (select max(pcklin) as Pick_Seq_Start, dftbld+dftscn+right('00'+cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS as Loc from ARCHIVE.dbo.SRC_WITM_ARCHIVE where ARCHIVE_DT >=DATEADD(DAY, -7, GETDATE()) and LOCNBR in('WM')group by dftbld+dftscn+right('00'+cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS)w on w.Loc=Start_slot_id left outer join (select max(pcklin) as Pick_Seq_End, dftbld+dftscn+right('00'+cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS as Loc from ARCHIVE.dbo.SRC_WITM_ARCHIVE where ARCHIVE_DT >=DATEADD(DAY, -7, GETDATE()) and LOCNBR in('WM')group by dftbld+dftscn+right('00'+cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS)x on x.Loc=End_slot_id order by assign_num,seq_num')

Hi,

Welcome to the RStudio community!

First of all, that is one impressive query statement ... :stuck_out_tongue:

Luckily, the answer to your problem is an easy one: You are using quotes in the query messing up the string in R. All you have to do is change the first and last quote of your string to the " type and only use ' within the query and it should be solved. Alternatively, you could use the escape character \ to escape all quotes in the string, but that's more cumbersome in my opinion...

Example:

#Incorrect use of quotes
test <- dbGetQuery(con, 'select * from myTable where name = 'Sara'')

#Correct use of quotes
test <- dbGetQuery(con, "select * from myTable where name = 'Sara'")

#Alternative: escape quotes in string
test <- dbGetQuery(con, 'select * from myTable where name = \'Sara\'')

Hope this helps,
PJ

1 Like

Thank you for your help! Though I have written some massive SQL queries in the past I can't take credit for this one haha. When I tried the correction of changing the first and last apostrophe ' to quotations ", it is now giving me a different error.

Additionally with the the escape quotes need to be used in every instance when I use ' as a variable?

Error: <SQL> 'select worktype_id, a.assign_num,z.UserIDandName, a.client_id, cstnum, machineid, item_num, item_desc, seq_num, machineid, num_case, num_ship, start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end,case when substring(start_slot_id,3,2) <= substring(end_slot_id,3,2) then 'In_Sequence' else 'Not_In_Sequence' end as Picking_Out_Aisle_Sequence, case when Pick_Seq_Start <= Pick_Seq_End then 'In' when seq_num='1' then 'In' else 'Out' end as Picking_Out_of_Sequence from (select * from DW_ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive bWhere B.wh_id in ('WM') and worktype_id in ('CSPICK') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565'))a join (select assign_num, sum(td) as sum_Travel_Distance, count(assign_num) as nbr_rows, sum(td)/count(distinct assign_num) as Assigment_travel from( select assign_num, case when substring(start_slot_id,1,1)<> SU

I tried the escape quotes in string... Is this correct? It is throwing the same SQL error as my last reply.

Error: <SQL> 'select worktype_id, a.assign_num,z.UserIDandName, a.client_id, cstnum, machineid, item_num, item_desc, seq_num, machineid, num_case, num_ship, start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end,case when substring(start_slot_id,3,2) <= substring(end_slot_id,3,2) then 'In_Sequence' else 'Not_In_Sequence' end as Picking_Out_Aisle_Sequence, case when Pick_Seq_Start <= Pick_Seq_End then 'In' when seq_num='1' then 'In' else 'Out' end as Picking_Out_of_Sequence from (select * from DW_ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive bWhere B.wh_id in ('WM') and worktype_id in ('CSPICK') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565'))a join (select assign_num, sum(td) as sum_Travel_Distance, count(assign_num) as nbr_rows, sum(td)/count(distinct assign_num) as Assigment_travel from( select assign_num, case when substring(start_slot_id,1,1)<> SU

test <- dbGetQuery(con,'select worktype_id, a.assign_num,z.UserIDandName, a.client_id, cstnum, machineid, item_num, item_desc, seq_num, machineid, num_case, num_ship, start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end,case when substring(start_slot_id,3,2) <= substring(end_slot_id,3,2) then \'In_Sequence\' else \'Not_In_Sequence\' end as Picking_Out_Aisle_Sequence, case when Pick_Seq_Start <= Pick_Seq_End then \'In\' when seq_num=\'1\' then \'In\' else \'Out\' end as Picking_Out_of_Sequence from (select * from DW_ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive bWhere B.wh_id in (\'WM\') and worktype_id in (\'CSPICK\') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in (\'018555\',\'018565\'))a join (select assign_num, sum(td) as sum_Travel_Distance, count(assign_num) as nbr_rows, sum(td)/count(distinct assign_num) as Assigment_travel from( select assign_num, case when substring(start_slot_id,1,1)<> SUBSTRING(end_slot_id,1,1) then 0 else travel_distance end as td from DW_ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive b Where B.wh_id in (\'WM\') and worktype_id in (\'CSPICK\') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in (\'018555\',\'018565\')) a group by assign_num) b on a.assign_num = b.assign_num left outer join (select distinct assign_num, UserIDandName from DW_ARCHIVE.dbo.Src_RP_LM_Detail_ARCHIVE where loc in (\'WM\') AND report_date>=DATEADD(DAY,-7, GETDATE()))z on z.assign_num=a.assign_num left outer join (select max(pcklin) as Pick_Seq_Start, dftbld+dftscn+right(\'00\'+cast(dftISL as varchar(3)),2)+right(\'000\'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS as Loc from DW_ARCHIVE.dbo.SRC_WITM_ARCHIVE where ARCHIVE_DT >=DATEADD(DAY, -7, GETDATE()) and LOCNBR in(\'WM\')group by dftbld+dftscn+right(\'00\'+cast(dftISL as varchar(3)),2)+right(\'000\'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS)w on w.Loc=Start_slot_id left outer join (select max(pcklin) as Pick_Seq_End, dftbld+dftscn+right(\'00\'+cast(dftISL as varchar(3)),2)+right(\'000\'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS as Loc from DW_ARCHIVE.dbo.SRC_WITM_ARCHIVE where ARCHIVE_DT >=DATEADD(DAY, -7, GETDATE()) and LOCNBR in(\'WM\')group by dftbld+dftscn+right(\'00\'+cast(dftISL as varchar(3)),2)+right(\'000\'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS)x on x.Loc=End_slot_id order by assign_num,seq_num')

Hi,

So just to make sure: this does not work?:

test <- dbGetQuery(con,
"select worktype_id, a.assign_num,z.UserIDandName, a.client_id, cstnum, 
machineid, item_num, item_desc, seq_num, machineid, num_case, num_ship, 
start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, 
travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end, case when 
substring(start_slot_id,3,2) <= substring(end_slot_id,3,2) then 'In_Sequence' else 
'Not_In_Sequence' end as Picking_Out_Aisle_Sequence, case when Pick_Seq_Start <= Pick_Seq_End then 
'In' when seq_num='1' then 'In' else 'Out' end as Picking_Out_of_Sequence from (select * from 
ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive bWhere B.wh_id in ('WM') and worktype_id in ('CSPICK') AND
B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565'))a join (select assign_num, 
sum(td) as sum_Travel_Distance, count(assign_num) as nbr_rows, sum(td)/count(distinct assign_num) 
as Assigment_travel from( select assign_num, case when substring(start_slot_id,1,1)<> SUBSTRING(end_slot_id,1,1) then 
0 else travel_distance end as td from ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive b Where B.wh_id in ('WM') and 
worktype_id in ('CSPICK') AND B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565')) a 
group by assign_num) b on a.assign_num = b.assign_num left outer join (select distinct assign_num, UserIDandName 
from ARCHIVE.dbo.Src_RP_LM_Detail_ARCHIVE where loc in ('WM') AND report_date>=DATEADD(DAY,-7, GETDATE()))z on 
z.assign_num=a.assign_num left outer join (select max(pcklin) as Pick_Seq_Start, dftbld+dftscn+right('00'+
cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS as 
Loc from ARCHIVE.dbo.SRC_WITM_ARCHIVE where ARCHIVE_DT >=DATEADD(DAY, -7, GETDATE()) and LOCNBR in('WM')group by 
dftbld+dftscn+right('00'+cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+
cast(dftLVL as varchar(1))+dftPOS)w on w.Loc=Start_slot_id left outer join (select max(pcklin) as 
Pick_Seq_End, dftbld+dftscn+right('00'+cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+
cast(dftLVL as varchar(1))+dftPOS as Loc from ARCHIVE.dbo.SRC_WITM_ARCHIVE where 
ARCHIVE_DT >=DATEADD(DAY, -7, GETDATE()) and LOCNBR in('WM')group by dftbld+dftscn+right('00'+
cast(dftISL as varchar(3)),2)+right('000'+cast(dftROW as varchar(4)),3)+cast(dftLVL as varchar(1))+dftPOS)x on 
x.Loc=End_slot_id order by assign_num,seq_num")
1 Like

Correct, that does not work. Below is the error (I copied what you sent).

Error: <SQL> 'select worktype_id, a.assign_num,z.UserIDandName, a.client_id, cstnum, 

machineid, item_num, item_desc, seq_num, machineid, num_case, num_ship, 

start_slot_id, end_slot_id, a.report_date, Assigment_travel/12 as Assignment_travel_ft, 

travel_distance/12 as travel_distance_ft, pick_seq_Start, pick_seq_end, case when 

substring(start_slot_id,3,2) <= substring(end_slot_id,3,2) then 'In_Sequence' else 

'Not_In_Sequence' end as Picking_Out_Aisle_Sequence, case when Pick_Seq_Start <= Pick_Seq_End then 

'In' when seq_num='1' then 'In' else 'Out' end as Picking_Out_of_Sequence from (select * from 

ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive bWhere B.wh_id in ('WM') and worktype_id in ('CSPICK') AND

B.report_date>=DATEADD(DAY,-7, GETDATE()) and client_id in ('018555','018565'))a join (select assign_num, 

sum(td) as sum_Travel_Distance, count(assign_num) as nbr_rows, sum(td)/count(distinct assign_num) 

as Assigment_travel from( select assign_num, case when substring(start_slot

Can you tell me the last part of that error. It seems you didn't copy the whole error message...

Also, try and do a simpler query just to test if the quotes are working. Example:

test <- dbGetQuery(con, "SELECT wh_id 
FROM ARCHIVE.dbo.SRC_RP_LM_Discrete_Archive
WHERE wh_id in ('WM')")

PJ

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.