CODEWITHSUNDEEP
sqlrr-markdown
Cinji18
Cinji18

Reputation: 629

R Markdown: Creating a SQL table then output it as a dataframe

I am learning how to use SQL in R and R Markdown.

A number of sources says that SQL tables can be created with a SQL chunk and that output.var needs to be in the SQL chunk tag to output that table into an R dataframe. But the dataframe comes out as NULL with the below code:

```{r setup, include=FALSE}

install.packages("RSQLite", repos = "http://cran.us.r-project.org")

library(RSQLite)
library(DBI)
library(knitr)

db <- dbConnect(RSQLite::SQLite(), ":memory:")

knitr::opts_chunk$set(echo = TRUE)
knitr::opts_chunk$set(connection = "db")

```

```{sql, connection = db, output.var = "Order_Detail"}

DROP TABLE IF EXISTS Order_Detail;

--create order table and populate records
CREATE TABLE IF NOT EXISTS Order_Detail (
    invoice_id INTEGER NOT NULL,
    invoice_line INTEGER NOT NULL,
    store_id INTEGER NOT NULL,
    time_stamp DATE NOT NULL,
    product VARCHAR(8) NOT NULL,
    units INTEGER NOT NULL,
    sales NUMERIC(7 , 2 ) NOT NULL,
    cogs NUMERIC(5 , 2 ) NOT NULL
);

INSERT INTO Order_Detail(invoice_id,invoice_line,store_id,time_stamp,product,units,sales,cogs) VALUES (1000,312,3,'2018/12/23','30',1,199.99,28.00);

```

> Order_Detail
NULL

Did I forget something?

Thanks.

Upvotes: 2

Views: 1323

Answers (1)

Parfait
Parfait

Reputation: 107687

Simply add a SELECT statement at the end of chunk since right now you only have action queries that do not render a resultset:

...
SELECT * FROM Order_Detail
```

Upvotes: 2

Related Questions