## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## ----setup--------------------------------------------------------------------
library(sqlcaser)

## -----------------------------------------------------------------------------
samp <- system.file("extdata", "sample.csv", package = "sqlcaser")
mapping <- read.csv(samp)
mapping

## -----------------------------------------------------------------------------
statement <- casewhen(samp)

## -----------------------------------------------------------------------------
query <- paste("SELECT id, ", statement, " END AS status "," \nFROM table;")
cat(query)

## -----------------------------------------------------------------------------
# Create sample data with named columns
data <- data.frame(
  status = c("Active", "Inactive", "Pending"),
  display = c("Currently Active", "Not Active", "Under Review")
)

# Use column names instead of positions, add ELSE clause
advanced_case <- casewhen(data, 
                         when_col = "status", 
                         then_col = "display",
                         else_value = "Unknown Status")

## -----------------------------------------------------------------------------
# Create data with numeric values
scores <- data.frame(
  grade = c("A", "B", "C"),
  points = c(90, 80, 70)
)

# Auto-quote mode handles numeric values without quotes
numeric_case <- casewhen(scores, 
                        when_col = "grade", 
                        then_col = "points",
                        quote_type = "auto")

## -----------------------------------------------------------------------------
# Create sample data with duplicates
categories <- data.frame(
  category = c("Sales", "Marketing", "Sales", "IT", "Marketing", "HR")
)

# Remove duplicates automatically
unique_list <- inlist(categories, 
                     value_col = "category",
                     distinct = TRUE)

## -----------------------------------------------------------------------------
# Create sample update data
updates <- data.frame(
  id = c(1, 2, 3),
  name = c("John Doe", "Jane Smith", "Bob Wilson"),
  department = c("Engineering", "Sales", "Marketing"),
  salary = c(75000, 65000, 60000)
)

# Generate UPDATE statements using ID as key
update_statements <- updatetable(updates, 
                                tablename = "employees",
                                key_col = "id")

## -----------------------------------------------------------------------------
samplepath <- system.file("extdata", "sample.csv", package = "sqlcaser")