sql like statement not recognized in RStudio

General
1

dpo <- dpo[dpo$SERVICE_DATE >= sDate & dpo$SERVICE_DATE <= eDate, ]
qry <- paste("SELECT * FROM its_ltdb.dbo.apc_correlated WHERE (CONVERT(varchar(10), transit_date_time,121)) >= ", "'",sDate, "'"," AND (CONVERT(varchar(10), transit_date_time,121)) <= ", "'",eDate, "'",sep ="")
dbQ <- sqlQuery(orb, qry)

dpo <- dpo[!is.na(dpo$VEHICLE), ]
dpo <- dpo[dpo$BLOCK %like% '%-%', ] ###Why is the %like% function not recognized.
dpo <- dpo[dpo$LDATE > 0, ]
dbQ <- dbQ[dbQ$current_route_id > 0, ]
##dbQ <- dbQ[dbQ$ons > 20, ]
dbQ <- dbQ[dbQ$vehicle_id < 9999, ]

Thanks for any assistance.

2

I am trying to query all blocks that have a dash in the string

3

How can I query all dpo$BLOCK that have a dash?

4

reprex()
Rendering reprex...
Rendered reprex is on the clipboard.

dir <- "C:\Users\rbaker\Documents\R\R-3.5.1\bin\x64"

#####################
sDate <- as.character(Sys.Date()-7)
eDate <- as.character(Sys.Date()-2)
#####################
mms <- odbcConnect("mms_31LIVE", uid="report_plan", pwd="report_plan", believeNRows=FALSE);
orb <- odbcConnect("orbcad", uid="tfms", pwd="tfms");

dpo <- sqlFetch(mms, "CMTA.VW_DAILY_PULLOUT")
dpo$SERVICE_DATE <- as.character(dpo$SERVICE_DATE)

dpo <- dpo[dpo$SERVICE_DATE >= sDate & dpo$SERVICE_DATE <= eDate, ]
qry <- paste("SELECT * FROM its_ltdb.dbo.apc_correlated WHERE (CONVERT(varchar(10), transit_date_time,121)) >= ", "'",sDate, "'"," AND (CONVERT(varchar(10), transit_date_time,121)) <= ", "'",eDate, "'",sep ="")
dbQ <- sqlQuery(orb, qry)

dpo <- dpo[!is.na(dpo$VEHICLE), ]
##dpo <- dpo[dpo$VEHICLE < 9100, ]
dpo <- dpo[dpo$BLOCK %like% '%-%', ]
Error in dpo$BLOCK %like% "%-%" : could not find function "%like%"
dpo <- dpo[dpo$LDATE > 0, ]
dbQ <- dbQ[dbQ$current_route_id > 0, ]
##dbQ <- dbQ[dbQ$ons > 20, ]
dbQ <- dbQ[dbQ$vehicle_id < 9999, ]

5

The %like% operator is not implemented in R so you can't use it like if it was SQL, to give you an alternative solution we would need a proper REPRoducible EXample (reprex) illustrating your issue.

6

How can I pull only BLOCKS with DASH (-) from "dpo" data frame below? Can you give me an alternative way of pulling only BLOCKS that contain a dash into my data set?

Rendering reprex...
Rendered reprex is on the clipboard.

head(dpo)
SERVICE_DATE DIVISIONABBR SEQ SIGN_ON PULL_OUT PULL_IN BLOCK VEHICLE DURATION
73870 2020-04-16 NOPSBUS 1 2020-04-16 04:34:00 2020-04-16 04:49:00 2020-04-16 1000089 2553 3.06667
73871 2020-04-16 NOPSBUS 2 2020-04-16 04:45:00 2020-04-16 05:00:00 2020-04-16 1000082 2718 7.00000
73872 2020-04-16 NOPSBUS 3 2020-04-16 04:56:00 2020-04-16 05:11:00 2020-04-16 801-01 5017 18.41667
73875 2020-04-16 NOPSBUS 6 2020-04-16 05:06:00 2020-04-16 05:21:00 2020-04-16 383-01 2716 16.51667
73876 2020-04-16 NOPSBUS 7 2020-04-16 05:07:00 2020-04-16 05:22:00 2020-04-17 801-03 5018 18.71667
73877 2020-04-16 NOPSBUS 8 2020-04-16 05:11:00 2020-04-16 05:26:00 2020-04-16 801-02 5006 18.50000
NOT_DONE EFF_DATE SIGNID LDATE DIVISIONID SERVICEGROUPID LINEGROUPID BUSTYPEID SPLITNUM
73870 2020-04-23 06:33:12 -1 20200416 5 NA NA NA 0
73871 2020-04-23 06:33:12 -1 20200416 5 NA NA NA 0
73872 2020-04-23 06:33:12 133 20200416 5 5 12115 24 0
73875 2020-04-23 06:33:12 133 20200416 5 5 12063 10 0
73876 2020-04-23 06:33:12 133 20200416 5 5 12115 24 0
73877 2020-04-23 06:33:12 133 20200416 5 5 12115 24 0
dpo <- dpo[dpo$BLOCK %like% '%-%', ]
Error in dpo$BLOCK %like% "%-%" : could not find function "%like%"

7

Trying to only pull "BLOCK" that contain a dash. See "BLOCK" field below.
Thanks for any assistance.

data.frame(

  • stringsAsFactors = FALSE,
  •      row.names = c("73870", "73871", "73872", "73875", "73876"),

  •   SERVICE_DATE = c("2020-04-16","2020-04-16",

  •                    "2020-04-16","2020-04-16","2020-04-16"),

  •            SEQ = c(1L, 2L, 3L, 6L, 7L),

  •        SIGN_ON = c("2020-04-16 04:34:00",

  •                    "2020-04-16 04:45:00","2020-04-16 04:56:00",

  •                    "2020-04-16 05:06:00","2020-04-16 05:07:00"),

  •       PULL_OUT = c("2020-04-16 04:49:00",

  •                    "2020-04-16 05:00:00","2020-04-16 05:11:00",

  •                    "2020-04-16 05:21:00","2020-04-16 05:22:00"),

  •        PULL_IN = c("2020-04-16","2020-04-16",

  •                    "2020-04-16","2020-04-16","2020-04-17"),

  •        VEHICLE = c(2553L, 2718L, 5017L, 2716L, 5018L),

  •       DURATION = c(3.06667, 7, 18.41667, 16.51667, 18.71667),

  •       EFF_DATE = c("2020-04-23 06:33:12",

  •                    "2020-04-23 06:33:12","2020-04-23 06:33:12",

  •                    "2020-04-23 06:33:12","2020-04-23 06:33:12"),

  •         SIGNID = c(-1L, -1L, 133L, 133L, 133L),

  •          LDATE = c(20200416L, 20200416L, 20200416L, 20200416L, 20200416L),

  •     DIVISIONID = c(5L, 5L, 5L, 5L, 5L),

  • SERVICEGROUPID = c(NA, NA, 5L, 5L, 5L),

  •    LINEGROUPID = c(NA, NA, 12115L, 12063L, 12115L),

  •      BUSTYPEID = c(NA, NA, 24L, 10L, 24L),

  •       SPLITNUM = c(0L, 0L, 0L, 0L, 0L),

  •   DIVISIONABBR = as.factor(c("NOPSBUS",

  •                              "NOPSBUS","NOPSBUS","NOPSBUS","NOPSBUS")),

  •          BLOCK = as.factor(c("1000089",

  •                              "1000082","801-01","383-01","801-03")),

  •       NOT_DONE = as.factor(c(NA, NA, NA, NA, NA))
  • )
8

Seems like you're having a little trouble producing a reprex. You are much more likely to get help if you post your question and data in any easy to use form. Please take a look at this guide that should help you be able to post your data in a format that we can copy and paste:

9

I'm sorry, I cannot get the reprex command to copy to clipboard. I get the error below.

reprex::reprex()
#> No input provided and clipboard is not available.
#> Rendering reprex...

Created on 2020-06-01 by the reprex package (v0.3.0)

10

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