SQL Select query not working using sqldf package in R

Question

This is what the head of my data frame looks like

head(d7)

Jurisdiction  %_TOT2030 %_ENR2030  %_RE2030  %_OFF2030  %_OTH2030  %_HH2030
1 Baltimore City  7.6667589         0  7.773109  7.5530587  7.6866764  4.783365
2 Baltimore City  8.0038573         0  8.193277  7.9555175  7.9553903  6.583851
3 Baltimore City 35.4085603         0 37.037037 35.3146853 35.3608247 20.008643
4 Baltimore City  0.5071851         0  1.030928  0.4424779  0.6410256 28.629032
5 Baltimore City  7.5310559         0  8.108108  7.4790458  7.4634938  9.679767
6 Baltimore City 11.8785976         0 12.043011 11.8718593 11.8460613  9.690331

The datatype of the data frame is as below str(d7)

'data.frame':   1588 obs. of  7 variables:
 $ Jurisdiction: Factor w/ 59 levels "Accomack County",..: 7 7 7 7 7 7 7 7 7 7 ...
 $ %_TOT2030   : num  7.667 8.004 35.409 0.507 7.531 ...
 $ %_ENR2030   : num  0 0 0 0 0 0 0 0 0 0 ...
 $ %_RE2030    : num  7.77 8.19 37.04 1.03 8.11 ...
 $ %_OFF2030   : num  7.553 7.956 35.315 0.442 7.479 ...
 $ %_OTH2030   : num  7.687 7.955 35.361 0.641 7.463 ...
 $ %_HH2030    : num  4.78 6.58 20.01 28.63 9.68 ...

When I run the below query, it is just returning me d7 without doing any changes and I can't understand why!

d8 <- sqldf("Select * from d7 where '%_TOT2030' > 10")

HubertL · Accepted Answer · 2017-03-07 01:28:09Z

4

In SQL queries you can use double quotes to enclose field names that contain spaces or special characters, while single quotes means text.

Here the text '%_TOT2030' is compared to '10'. This is always TRUE so you get all lines from your table.

To compare values of field %_TOT2030 you have to write "%_TOT2030" > 10, therefore I suggest you simply inverse all simple and double quotes like this:

d8 <- sqldf('Select * from d7 where "%_TOT2030" > 10')

answered Mar 7, 2017 at 1:28

HubertL

19.6k3 gold badges38 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2017-03-07 01:40:05Z

1

In SQLite the default dialect of sqldf, you can also use square brackets to enclose table/column aliases to escape spaces, special characters, and reserved words.

d8 <- sqldf('Select * from d7 where [%_TOT2030] > 10')

answered Mar 7, 2017 at 1:40

Parfait

108k19 gold badges103 silver badges138 bronze badges

Collectives™ on Stack Overflow

SQL Select query not working using sqldf package in R

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related