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')
