My data set say data123 has columns Order.No,date,transactions, etc. I am doing a pivot using sqldf for which below is the code
Pivot123=sqldf("Select Order.No,Date,Month,Year,Category from data123")
When running this function, I am getting the below error
Error: near "Order": syntax error
What is the reason? I rechecked but the column name is correct Thanks in advance
The Table.ColumnName convention is biting you here. Since I'm inferring that Order.No is a column name, then change your question to one of:
sqldf("Select [Order.No],Date,Month,Year,Category from data123")
sqldf('Select "Order.No",Date,Month,Year,Category from data123') # note the shift from ' to "
The premise is that you're telling the SQL engine that Order.No is an identifier (complete name), not to be separated by the normal dot-notation format. There are two common ways to quote identifiers: [Order.No] and "Order.No". The single-quote method only identifies string literals, so 'Order.No' does not work (try it and see).
A good example uses a common technique: assign a (typically shorter) alias, especially useful when table names are long:
# the alias is used here
# vvv. vvv. vvv. vvv. vvv.
sqldf("Select tb1.[Order.No], tb1.Date, tb1.Month, tb1.Year, tb1.Category
from data123 tb1")
# ^^^ this is the alias
This is not always necessary, but a time when it is necessary is when you are querying from multiple tables and they share column names.
Lastly, you can opt to "quote" all column names, it does no harm:
sqldf("Select [Order.No], [Date], [Month], [Year], [Category] from data123")