Nothing
library("testthat")
# For debugging: force reload of patterns:
# rJava::J('org.ohdsi.sql.SqlTranslate')$setReplacementPatterns('inst/csv/replacementPatterns.csv')
expect_equal_ignore_spaces <- function(string1, string2) {
string1 <- gsub("([;()'+-/|*\n])", " \\1 ", string1)
string2 <- gsub("([;()'+-/|*\n])", " \\1 ", string2)
string1 <- gsub(" +", " ", string1)
string2 <- gsub(" +", " ", string2)
expect_equivalent(string1, string2)
}
expect_match_ignore_spaces <- function(string1, regexp) {
string1 <- gsub(" +", " ", string1)
expect_match(string1, regexp)
}
test_that("translate sql server -> RedShift VARCHAR(MAX)", {
sql <- translate("VARCHAR(MAX)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "VARCHAR(MAX)")
})
test_that("translate sql server -> Redshift create table if not exists", {
sql <- translate("IF OBJECT_ID('cohort', 'U') IS NULL\n CREATE TABLE cohort\n(cohort_definition_id INT);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE IF NOT EXISTS cohort\n (cohort_definition_id INT)\nDISTSTYLE ALL;"
)
})
test_that("translate sql server -> redshift datefromparts", {
sql <- translate("SELECT DATEFROMPARTS(year,month,day) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT TO_DATE(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM'), 'YYYY-MM-DD') FROM table"
)
})
test_that("translate sql server -> Redshift select random row", {
sql <- translate("SELECT column FROM (SELECT column, ROW_NUMBER() OVER (ORDER BY RAND()) AS rn FROM table) tmp WHERE rn <= 1",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT column FROM (SELECT column, ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn FROM table) tmp WHERE rn <= 1"
)
})
test_that("translate sql server -> Redshift select random row using hash", {
sql <- translate("SELECT column FROM (SELECT column, ROW_NUMBER() OVER (ORDER BY HASHBYTES('MD5',CAST(person_id AS varchar))) tmp WHERE rn <= 1",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT column FROM (SELECT column, ROW_NUMBER() OVER (ORDER BY MD5(CAST(person_id AS varchar))) tmp WHERE rn <= 1"
)
})
test_that("translate sql server -> RedShift SELECT CONVERT(VARBINARY, @a, 1)", {
sql <- translate("SELECT ROW_NUMBER() OVER CONVERT(VARBINARY, val, 1) rn WHERE rn <= 1",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT ROW_NUMBER() OVER STRTOL(LEFT(val, 15), 16) rn WHERE rn <= 1"
)
})
test_that("translate sql server -> redshift hint distribute_on_key", {
sql <- translate("--HINT DISTRIBUTE_ON_KEY(row_id)\nSELECT * INTO #my_table FROM other_table;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_KEY(row_id)\nCREATE TABLE #my_table\nDISTKEY(row_id)\nAS\nSELECT\n * \nFROM\n other_table;"
)
})
test_that("translate sql server -> redshift hint distribute_on_key", {
sql <- translate("--HINT DISTRIBUTE_ON_KEY(row_id)\nCREATE TABLE my_table (row_id INT);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_KEY(row_id)\nCREATE TABLE my_table (row_id INT)\nDISTKEY(row_id);"
)
})
test_that("translate sql server -> redshift hint distribute_on_random", {
sql <- translate("--HINT DISTRIBUTE_ON_RANDOM\nSELECT * INTO #my_table FROM other_table;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_RANDOM\nCREATE TABLE #my_table\nDISTSTYLE EVEN\nAS\nSELECT\n * \nFROM\n other_table;"
)
})
test_that("translate sql server -> redshift hint distribute_on_random", {
sql <- translate("--HINT DISTRIBUTE_ON_RANDOM\nCREATE TABLE my_table (row_id INT);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_RANDOM\nCREATE TABLE my_table (row_id INT)\nDISTSTYLE EVEN;"
)
})
test_that("translate sql server -> redshift natural log", {
sql <- translate("SELECT LOG(number) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT LN(CAST((number) AS REAL)) FROM table")
})
test_that("translate sql server -> redshift log base 10", {
sql <- translate("SELECT LOG10(number) FROM table;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT LOG(CAST((number) AS REAL)) FROM table;")
})
test_that("translate sql server -> redshift log any base", {
sql <- translate("SELECT LOG(number, base) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT (LN(CAST((number) AS REAL))/LN(CAST(( base) AS REAL))) FROM table"
)
})
test_that("translate sql server -> RedShift DATEADD dd", {
sql <- translate("SELECT DATEADD(dd, 30, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(day, CAST(30 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD mm", {
sql <- translate("SELECT DATEADD(mm, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(month, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD m", {
sql <- translate("SELECT DATEADD(m, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(month, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD yyyy", {
sql <- translate("SELECT DATEADD(yyyy, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(year, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD yy", {
sql <- translate("SELECT DATEADD(yy, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(year, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD qq", {
sql <- translate("SELECT DATEADD(qq, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(quarter, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD q", {
sql <- translate("SELECT DATEADD(q, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(quarter, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD wk", {
sql <- translate("SELECT DATEADD(wk, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(week, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD ww", {
sql <- translate("SELECT DATEADD(ww, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(week, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD hh", {
sql <- translate("SELECT DATEADD(hh, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(hour, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD mi", {
sql <- translate("SELECT DATEADD(mi, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(minute, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD ss", {
sql <- translate("SELECT DATEADD(ss, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(second, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEADD mcs", {
sql <- translate("SELECT DATEADD(mcs, 3, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEADD(microsecond, CAST(3 as int), drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF dd", {
sql <- translate("SELECT DATEDIFF(dd, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(day, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF year", {
sql <- translate("SELECT DATEDIFF(YEAR,drug_era_start_date,drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(YEAR,drug_era_start_date,drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF m", {
sql <- translate("SELECT DATEDIFF(m, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(month, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF mm", {
sql <- translate("SELECT DATEDIFF(mm, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(month, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF yyyy", {
sql <- translate("SELECT DATEDIFF(yyyy, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(year, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF yy", {
sql <- translate("SELECT DATEDIFF(yy, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(year, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF qq", {
sql <- translate("SELECT DATEDIFF(qq, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(quarter, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF q", {
sql <- translate("SELECT DATEDIFF(q, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(quarter, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF wk", {
sql <- translate("SELECT DATEDIFF(wk, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(week, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF ww", {
sql <- translate("SELECT DATEDIFF(ww, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(week, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF hh", {
sql <- translate("SELECT DATEDIFF(hh, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(hour, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF mi", {
sql <- translate("SELECT DATEDIFF(mi, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(minute, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF n", {
sql <- translate("SELECT DATEDIFF(n, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(minute, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF ss", {
sql <- translate("SELECT DATEDIFF(ss, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(second, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF mcs", {
sql <- translate("SELECT DATEDIFF(mcs, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(microsecond, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG dd", {
sql <- translate("SELECT DATEDIFF_BIG(dd, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(day, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG m", {
sql <- translate("SELECT DATEDIFF_BIG(m, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(month, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG mm", {
sql <- translate("SELECT DATEDIFF_BIG(mm, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(month, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG yyyy", {
sql <- translate("SELECT DATEDIFF_BIG(yyyy, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(year, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG yy", {
sql <- translate("SELECT DATEDIFF_BIG(yy, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(year, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG qq", {
sql <- translate("SELECT DATEDIFF_BIG(qq, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(quarter, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG q", {
sql <- translate("SELECT DATEDIFF_BIG(q, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(quarter, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG wk", {
sql <- translate("SELECT DATEDIFF_BIG(wk, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(week, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG ww", {
sql <- translate("SELECT DATEDIFF_BIG(ww, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(week, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG hh", {
sql <- translate("SELECT DATEDIFF_BIG(hh, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(hour, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG mi", {
sql <- translate("SELECT DATEDIFF_BIG(mi, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(minute, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG n", {
sql <- translate("SELECT DATEDIFF_BIG(n, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(minute, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG ss", {
sql <- translate("SELECT DATEDIFF_BIG(ss, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(second, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEDIFF_BIG mcs", {
sql <- translate("SELECT DATEDIFF_BIG(mcs, drug_era_start_date, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT DATEDIFF(microsecond, drug_era_start_date, drug_era_end_date) FROM drug_era;"
)
})
test_that("translate sql server -> RedShift DATEPART dd", {
sql <- translate("SELECT DATEPART(dd, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(day, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART m", {
sql <- translate("SELECT DATEPART(m, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(month, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART mm", {
sql <- translate("SELECT DATEPART(mm, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(month, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART yyyy", {
sql <- translate("SELECT DATEPART(yyyy, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(year, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART yy", {
sql <- translate("SELECT DATEPART(yy, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(year, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART qq", {
sql <- translate("SELECT DATEPART(qq, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(quarter, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART q", {
sql <- translate("SELECT DATEPART(q, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(quarter, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART wk", {
sql <- translate("SELECT DATEPART(wk, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(week, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART ww", {
sql <- translate("SELECT DATEPART(ww, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(week, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART hh", {
sql <- translate("SELECT DATEPART(hh, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(hour, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART mi", {
sql <- translate("SELECT DATEPART(mi, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(minute, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART n", {
sql <- translate("SELECT DATEPART(n, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(minute, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART ss", {
sql <- translate("SELECT DATEPART(ss, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(second, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATEPART mcs", {
sql <- translate("SELECT DATEPART(mcs, drug_era_end_date) FROM drug_era;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "SELECT DATEPART(microsecond, drug_era_end_date) FROM drug_era;")
})
test_that("translate sql server -> RedShift DATETIMEFROMPARTS", {
sql <- translate("SELECT DATETIMEFROMPARTS(year,month,day,hour,minute,second,millisecond) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CAST(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM')||' '||TO_CHAR(hour,'00FM')||':'||TO_CHAR(minute,'00FM')||':'||TO_CHAR(second,'00FM')||'.'||TO_CHAR(millisecond,'000FM') as TIMESTAMP) FROM table"
)
})
test_that("translate sql server -> RedShift EOMONTH", {
sql <- translate("SELECT EOMONTH(date) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT LAST_DAY(date) FROM table")
})
test_that("translate sql server -> RedShift VARIANCE", {
sql <- translate("SELECT VAR(a) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT VARIANCE(a) FROM table")
})
test_that("translate sql server -> RedShift SQUARE", {
sql <- translate("SELECT SQUARE(a + b) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT ((a + b) * (a + b)) FROM table")
})
test_that("translate sql server -> RedShift NEWID", {
sql <- translate("SELECT NEWID()", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT MD5(RANDOM()::TEXT || GETDATE()::TEXT)")
})
test_that("translate sql server -> RedShift BOOL TYPE", {
sql <- translate("CREATE TABLE table ( col BIT not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col BOOLEAN not null)")
})
test_that("translate sql server -> RedShift MONEY TYPE", {
sql <- translate("CREATE TABLE table ( col MONEY not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col DECIMAL(19, 4) not null)")
})
test_that("translate sql server -> RedShift SMALLMONEY TYPE", {
sql <- translate("CREATE TABLE table ( col SMALLMONEY not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col DECIMAL(10, 4) not null)")
})
test_that("translate sql server -> RedShift TINYINT TYPE", {
sql <- translate("CREATE TABLE table ( col TINYINT not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col SMALLINT not null)")
})
test_that("translate sql server -> RedShift FLOAT TYPE", {
sql <- translate("CREATE TABLE table ( col FLOAT(@s) not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col FLOAT not null)")
})
test_that("translate sql server -> RedShift DATETIME2 TYPE with precision specified", {
sql <- translate("CREATE TABLE table ( col DATETIME2(@p) not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col TIMESTAMP not null)")
})
test_that("translate sql server -> RedShift DATETIME2 TYPE", {
sql <- translate("CREATE TABLE table ( col DATETIME2 not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col TIMESTAMP not null)")
})
test_that("translate sql server -> RedShift DATETIME TYPE", {
sql <- translate("CREATE TABLE table ( col DATETIME not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col TIMESTAMP not null)")
})
test_that("translate sql server -> RedShift SMALLDATETIME TYPE", {
sql <- translate("CREATE TABLE table ( col SMALLDATETIME not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col TIMESTAMP not null)")
})
test_that("translate sql server -> RedShift DATETIMEOFFSET TYPE with precision specified", {
sql <- translate("CREATE TABLE table ( col DATETIMEOFFSET(@p) not null)",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col TIMESTAMPTZ not null)")
})
test_that("translate sql server -> RedShift DATETIMEOFFSET TYPE", {
sql <- translate("CREATE TABLE table ( col DATETIMEOFFSET not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col TIMESTAMPTZ not null)")
})
test_that("translate sql server -> RedShift TEXT TYPE", {
sql <- translate("CREATE TABLE table ( col TEXT not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col VARCHAR(max) not null)")
})
test_that("translate sql server -> RedShift NTEXT TYPE", {
sql <- translate("CREATE TABLE table ( col NTEXT not null)", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col VARCHAR(max) not null)")
})
test_that("translate sql server -> RedShift UNIQUEIDENTIFIER TYPE", {
sql <- translate("CREATE TABLE table ( col UNIQUEIDENTIFIER not null)",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "CREATE TABLE table ( col CHAR(36) not null)")
})
test_that("translate sql server -> RedShift STDEV POP", {
sql <- translate("SELECT STDEVP(col) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT STDDEV_POP(col) FROM table")
})
test_that("translate sql server -> RedShift VAR POP", {
sql <- translate("SELECT VARP(col) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT VAR_POP(col) FROM table")
})
test_that("translate sql server -> RedShift DATETIME2FROMPARTS", {
sql <- translate("SELECT DATETIME2FROMPARTS(year,month,day,hour,minute,seconds, 0, 0) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CAST(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM')||' '||TO_CHAR(hour,'00FM')||':'||TO_CHAR(minute,'00FM')||':'||TO_CHAR(seconds,'00FM') as TIMESTAMP) FROM table"
)
})
test_that("translate sql server -> RedShift DATETIME2FROMPARTS with fractions", {
sql <- translate("SELECT DATETIME2FROMPARTS(year,month,day,hour,minute,seconds,fractions,precision) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CAST(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM')||' '||TO_CHAR(hour,'00FM')||':'||TO_CHAR(minute,'00FM')||':'||TO_CHAR(seconds,'00FM')||'.'||TO_CHAR(fractions,repeat('0', precision) || 'FM') as TIMESTAMP) FROM table"
)
})
test_that("translate sql server -> RedShift DATETIMEOFFSETFROMPARTS", {
sql <- translate("SELECT DATETIMEOFFSETFROMPARTS(year,month,day,hour,minute,seconds, 0,h_offset,m_offset, 0) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CAST(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM')||' '||TO_CHAR(hour,'00FM')||':'||TO_CHAR(minute,'00FM')||':'||TO_CHAR(seconds,'00FM')||case when h_offset >= 0 then '+' else '-' end ||TO_CHAR(ABS(h_offset),'00FM')||':'||TO_CHAR(ABS(m_offset),'00FM') as TIMESTAMPTZ) FROM table"
)
})
test_that("translate sql server -> RedShift DATETIMEOFFSETFROMPARTS with fractions", {
sql <- translate("SELECT DATETIMEOFFSETFROMPARTS(year,month,day,hour,minute,seconds,fractions,h_offset,m_offset,precision) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CAST(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM')||' '||TO_CHAR(hour,'00FM')||':'||TO_CHAR(minute,'00FM')||':'||TO_CHAR(seconds,'00FM')||'.'||TO_CHAR(fractions,repeat('0',precision) || 'FM')||case when h_offset >= 0 then '+' else '-' end ||TO_CHAR(ABS(h_offset),'00FM')||':'||TO_CHAR(ABS(m_offset),'00FM') as TIMESTAMPTZ) FROM table"
)
})
test_that("translate sql server -> RedShift GETUTCDATE", {
sql <- translate("SELECT GETUTCDATE();", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT CURRENT_TIMESTAMP;")
})
test_that("translate sql server -> RedShift SMALLDATETIMEFROMPARTS", {
sql <- translate("SELECT SMALLDATETIMEFROMPARTS(year,month,day,hour,minute) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CAST(TO_CHAR(year,'0000FM')||'-'||TO_CHAR(month,'00FM')||'-'||TO_CHAR(day,'00FM')||' '||TO_CHAR(hour,'00FM')||':'||TO_CHAR(minute,'00FM') as TIMESTAMP) FROM table"
)
})
test_that("translate sql server -> RedShift SYSUTCDATETIME", {
sql <- translate("SELECT SYSUTCDATETIME();", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT CURRENT_TIMESTAMP;")
})
test_that("translate sql server -> RedShift ATN2", {
sql <- translate("SELECT ATN2(a, b) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT ATAN2(a, b) FROM table")
})
test_that("translate sql server -> RedShift TRUNCATION OF NUMBER", {
sql <- translate("SELECT ROUND(expression,length,trunc) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT case when trunc = 0 then ROUND(CAST(expression AS FLOAT),length) else TRUNC(CAST(expression AS FLOAT),length) end FROM table"
)
})
test_that("translate sql server -> RedShift CHARINDEX from position", {
sql <- translate("SELECT CHARINDEX('test',column, 3) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT case when CHARINDEX('test', SUBSTRING(column, 3)) > 0 then (CHARINDEX('test', SUBSTRING(column, 3)) + 3 - 1) else 0 end FROM table"
)
})
test_that("translate sql server -> RedShift QUOTENAME", {
sql <- translate("SELECT QUOTENAME(a) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT QUOTE_IDENT(a) FROM table")
})
test_that("translate sql server -> RedShift SPACE", {
sql <- translate("SELECT SPACE(n) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT REPEAT(' ',n) FROM table")
})
test_that("translate sql server -> RedShift STUFF", {
sql <- translate("SELECT STUFF(expression, start, length, replace) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT SUBSTRING(expression, 0, start)|| replace||SUBSTRING(expression, start + length) FROM table"
)
})
test_that("translate sql server -> RedShift CONCAT", {
sql <- translate("SELECT CONCAT(p1,p2,p3,p4,p5,p6,p7) FROM table", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT CONCAT(p1,CONCAT(p2,CONCAT(p3,CONCAT(p4,CONCAT(p5,CONCAT(p6,p7)))))) FROM table"
)
})
test_that("translate sql server -> RedShift CONCAT", {
sql <- translate("SELECT CONCAT('Condition occurrence record observed during long_term_days on or prior to cohort index: ', CAST((p1.covariate_id-101)/1000 AS VARCHAR), '-', CASE WHEN c1.concept_name IS NOT NULL THEN c1.concept_name ELSE 'Unknown invalid concept' END) FROM table",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"SELECT CONCAT('Condition occurrence record observed during long_term_days on or prior to cohort index: ',CONCAT(CAST((p1.covariate_id-101)/1000 AS VARCHAR),CONCAT('-',CASE WHEN c1.concept_name IS NOT NULL THEN c1.concept_name ELSE 'Unknown invalid concept' END))) FROM table"
)
})
test_that("translate sql server -> RedShift CTAS TEMP WITH CTE person_id", {
sql <- translate("WITH a AS b SELECT person_id, col1, col2 INTO #table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE #table \nDISTKEY(person_id)\nAS\nWITH\n a \nAS\n b \nSELECT\n person_id , col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS TEMP WITH CTE person_id at the end", {
sql <- translate("WITH a AS b SELECT col1, col2, person_id INTO #table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE #table \nDISTKEY(person_id)\nAS\nWITH\n a \nAS\n b \nSELECT\n col1, col2, person_id\nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS WITH CTE person_id", {
sql <- translate("WITH a AS b SELECT person_id, col1, col2 INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(person_id)\nAS\nWITH\n a \nAS\n b \nSELECT\n person_id , col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS WITH CTE person_id with alias", {
sql <- translate("WITH a AS b SELECT person_id as dist, col1, col2 INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nWITH\n a \nAS\n b \nSELECT\n person_id as dist, col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS WITH CTE person_id with alias at the end", {
sql <- translate("WITH a AS b SELECT col1, col2, person_id as dist INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nWITH\n a \nAS\n b \nSELECT\n col1, col2, person_id as dist \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS WITH CTE person_id with alias (no 'as')", {
sql <- translate("WITH a AS b SELECT col1, person_id dist, col2 INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nWITH\n a \nAS\n b \nSELECT\n col1, person_id dist, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS WITH CTE person_id with alias (no 'as') at the end", {
sql <- translate("WITH a AS b SELECT col1, col2, person_id dist INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nWITH\n a \nAS\n b \nSELECT\n col1, col2, person_id dist \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS TEMP person_id", {
sql <- translate("SELECT person_id, col1, col2 INTO #table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE #table \nDISTKEY(person_id)\nAS\nSELECT\n person_id , col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS person_id", {
sql <- translate("SELECT person_id, col1, col2 INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(person_id)\nAS\nSELECT\n person_id , col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS person_id with alias", {
sql <- translate("SELECT person_id as dist, col1, col2 INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nSELECT\n person_id as dist, col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS person_id with alias at the end", {
sql <- translate("SELECT col1, col2, person_id as dist INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nSELECT\n col1, col2, person_id as dist \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS person_id with alias (no 'as')", {
sql <- translate("SELECT person_id dist, col1, col2 INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nSELECT\n person_id dist, col1, col2 \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CTAS person_id with alias (no 'as') at the end", {
sql <- translate("SELECT col1, col2, person_id dist INTO table FROM person;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table \nDISTKEY(dist)\nAS\nSELECT\n col1, col2, person_id dist \nFROM\n person;"
)
})
test_that("translate sql server -> RedShift CREATE TABLE person_id", {
sql <- translate("CREATE TABLE [dbo].[drug_era] ([drug_era_id] bigint NOT NULL, [person_id] bigint NOT NULL, [drug_concept_id] bigint NOT NULL, [drug_era_start_date] date NOT NULL, [drug_era_end_date] date NOT NULL, [drug_exposure_count] int NULL, [gap_days] int NULL);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE [dbo].[drug_era] ([drug_era_id] bigint NOT NULL, [person_id] bigint NOT NULL, [drug_concept_id] bigint NOT NULL, [drug_era_start_date] date NOT NULL, [drug_era_end_date] date NOT NULL, [drug_exposure_count] int NULL, [gap_days] int NULL)\nDISTKEY(person_id);"
)
})
test_that("translate sql server -> RedShift ISDATE", {
sql <- translate("SELECT * FROM table WHERE ISDATE(col) = 1", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT * FROM table WHERE REGEXP_INSTR(col, '^(\\\\d{4}[/\\-]?[01]\\\\d[/\\-]?[0123]\\\\d)([ T]([0-1][0-9]|[2][0-3]):([0-5][0-9])(:[0-5][0-9](.\\\\d+)?)?)?$') = 1"
)
})
test_that("translate sql server -> RedShift ISNUMERIC", {
sql <- translate("SELECT * FROM table WHERE ISNUMERIC(col) = 1", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT * FROM table WHERE REGEXP_INSTR(col, '^[\\-\\+]?(\\\\d*\\\\.)?\\\\d+([Ee][\\-\\+]?\\\\d+)?$') = 1"
)
})
test_that("translate sql server -> RedShift PATINDEX", {
sql <- translate("SELECT PATINDEX(pattern,expression) FROM table;", targetDialect = "redshift")
expect_equal_ignore_spaces(
sql,
"SELECT REGEXP_INSTR(expression, case when LEFT(pattern,1)<>'%' and RIGHT(pattern,1)='%' then '^' else '' end||TRIM('%' FROM REPLACE(pattern,'_','.'))||case when LEFT(pattern,1)='%' and RIGHT(pattern,1)<>'%' then '$' else '' end) FROM table;"
)
})
test_that("translate sql server -> RedShift SELECT INTO temp table with CTE and default hashing (DISTSTYLE ALL)", {
sql <- translate(paste("WITH cte(a1) AS (SELECT a1 FROM table_a)",
"SELECT *",
"INTO #table",
"FROM cte;",
sep = " "
), targetDialect = "redshift")
expect_equal_ignore_spaces(sql, paste("CREATE TABLE #table DISTSTYLE ALL",
"AS",
"WITH",
" cte(a1) ",
"AS",
" (SELECT a1 FROM table_a) ",
"SELECT",
" * ",
"FROM",
" cte;",
sep = "\n"
))
})
test_that("translate sql server -> RedShift SELECT INTO permanent table with CTE and default hashing (DISTSTYLE ALL)", {
sql <- translate(paste("WITH cte(a1) AS (SELECT a1 FROM table_a)",
"SELECT *",
"INTO table",
"FROM cte;",
sep = " "
), targetDialect = "redshift")
expect_equal_ignore_spaces(sql, paste("CREATE TABLE table DISTSTYLE ALL",
"AS",
"WITH",
" cte(a1) ",
"AS",
" (SELECT a1 FROM table_a) ",
"SELECT",
" * ",
"FROM",
" cte;",
sep = "\n"
))
})
test_that("translate sql server -> RedShift SELECT INTO temp table with default hashing (DISTSTYLE ALL)", {
sql <- translate(paste("SELECT *", "INTO #table", "FROM another_table;", sep = " "),
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, paste("CREATE TABLE #table DISTSTYLE ALL",
"AS",
"SELECT",
" * ",
"FROM",
" another_table;",
sep = "\n"
))
})
test_that("translate sql server -> RedShift SELECT INTO permanent table with default hashing (DISTSTYLE ALL)", {
sql <- translate(paste("SELECT *", "INTO table", "FROM another_table;", sep = " "),
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, paste("CREATE TABLE table DISTSTYLE ALL",
"AS",
"SELECT",
" * ",
"FROM",
" another_table;",
sep = "\n"
))
})
test_that("translate sql server -> RedShift SELECT value INTO temp table with default hashing (DISTSTYLE ALL)", {
sql <- translate("SELECT a INTO #table;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE #table DISTSTYLE ALL\nAS\nSELECT\n a ;")
})
test_that("translate sql server -> RedShift SELECT value INTO permanent table with default hashing (DISTSTYLE ALL)", {
sql <- translate("SELECT a INTO table;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE table DISTSTYLE ALL\nAS\nSELECT\n a ;")
})
test_that("translate sql server -> RedShift create temp table with default hashing (DISTSTYLE ALL)", {
sql <- translate("CREATE TABLE #table (id int not null, col varchar(max));",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE #table (id int not null, col varchar(max))\nDISTSTYLE ALL;"
)
})
test_that("translate sql server -> RedShift create permanent table with default hashing (DISTSTYLE ALL)", {
sql <- translate("CREATE TABLE table (id int not null, col varchar(max));",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"CREATE TABLE table (id int not null, col varchar(max))\nDISTSTYLE ALL;"
)
})
test_that("translate sql server -> RedShift CREATE TABLE IF NOT EXISTS with hashing", {
sql <- translate(paste("IF OBJECT_ID('dbo.heracles_results', 'U') IS NULL",
"CREATE TABLE dbo.heracles_results",
"(",
"cohort_definition_id int,",
"analysis_id int,",
"stratum_1 varchar(255),",
"stratum_2 varchar(255),",
"stratum_3 varchar(255),",
"stratum_4 varchar(255),",
"stratum_5 varchar(255),",
"count_value bigint,",
"last_update_time datetime",
");",
sep = "\n"
), targetDialect = "redshift")
expect_equal_ignore_spaces(sql, paste("CREATE TABLE IF NOT EXISTS dbo.heracles_results",
"(cohort_definition_id int,",
"analysis_id int,",
"stratum_1 varchar(255),",
"stratum_2 varchar(255),",
"stratum_3 varchar(255),",
"stratum_4 varchar(255),",
"stratum_5 varchar(255),",
"count_value bigint,",
"last_update_time TIMESTAMP",
")",
"DISTKEY(analysis_id);",
sep = "\n"
))
})
test_that("translate sql server -> RedShift DISTINCT + TOP", {
sql <- translate("SELECT DISTINCT TOP 100 * FROM table WHERE a = b;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT TOP 100 DISTINCT * FROM table WHERE a = b;")
})
test_that("RedShift XOR operator", {
sql <- translate("select a ^ b from c where a = 1;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "select a # b from c where a = 1;")
})
test_that("translate sql server -> redshift hint DISTKEY + SORTKEY on CTAS + CTE", {
sql <- translate("--HINT DISTRIBUTE_ON_KEY(row_id) SORT_ON_KEY(COMPOUND:start_date)\nWITH cte(row_id, start_date) AS (select * from basetable)\nSELECT * INTO #my_table FROM cte;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_KEY(row_id) SORT_ON_KEY(COMPOUND:start_date)\nCREATE TABLE #my_table\nDISTKEY(row_id)\nCOMPOUND SORTKEY(start_date)\nAS\nWITH cte(row_id, start_date) AS (select * from basetable)\nSELECT\n * \nFROM\n cte;"
)
})
test_that("translate sql server -> redshift hint SORTKEY on CTAS + CTE", {
sql <- translate("--HINT SORT_ON_KEY(COMPOUND:start_date)\nWITH cte(row_id, start_date) AS (select * from basetable)\nSELECT * INTO #my_table FROM cte;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT SORT_ON_KEY(COMPOUND:start_date)\nCREATE TABLE #my_table\nCOMPOUND SORTKEY(start_date)\nAS\nWITH cte(row_id, start_date) AS (select * from basetable)\nSELECT\n * \nFROM\n cte;"
)
})
test_that("translate sql server -> redshift hint DISTKEY + SORTKEY on CTAS", {
sql <- translate("--HINT DISTRIBUTE_ON_KEY(row_id) SORT_ON_KEY(:start_date, end_date)\nSELECT * INTO #my_table FROM other_table;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_KEY(row_id) SORT_ON_KEY(:start_date, end_date)\nCREATE TABLE #my_table\nDISTKEY(row_id)\nSORTKEY(start_date, end_date)\nAS\nSELECT\n*\nFROM\n other_table;"
)
})
test_that("translate sql server -> redshift hint SORTKEY on CTAS", {
sql <- translate("--HINT SORT_ON_KEY(:start_date, end_date)\nSELECT * INTO #my_table FROM other_table;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT SORT_ON_KEY(:start_date, end_date)\nCREATE TABLE #my_table\nSORTKEY(start_date, end_date)\nAS\nSELECT\n * \nFROM\n other_table;"
)
})
test_that("translate sql server -> redshift hint DISTKEY + SORTKEY on CREATE TABLE", {
sql <- translate("--HINT DISTRIBUTE_ON_KEY(row_id) SORT_ON_KEY(INTERLEAVED:start_date)\nCREATE TABLE cdm.my_table (row_id INT, start_date);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT DISTRIBUTE_ON_KEY(row_id) SORT_ON_KEY(INTERLEAVED:start_date)\nCREATE TABLE cdm.my_table (row_id INT, start_date)\nDISTKEY(row_id)\nINTERLEAVED SORTKEY(start_date);"
)
})
test_that("translate sql server -> redshift hint SORTKEY on CREATE TABLE", {
sql <- translate("--HINT SORT_ON_KEY(INTERLEAVED:start_date)\nCREATE TABLE cdm.my_table (row_id INT, start_date);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"--HINT SORT_ON_KEY(INTERLEAVED:start_date)\nCREATE TABLE cdm.my_table (row_id INT, start_date)\nINTERLEAVED SORTKEY(start_date);"
)
})
test_that("translate sql server -> redshift CONVERT to DATE", {
sql <- translate("select CONVERT(DATE, start_date) from my_table;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "select CAST(start_date as DATE) from my_table;")
})
test_that("translate sql server -> redshift CONVERT to TIMESTAMPTZ", {
sql <- translate("select CONVERT(TIMESTAMPTZ, start_date) from my_table;",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select CONVERT(TIMESTAMP WITH TIME ZONE, start_date) from my_table;"
)
})
test_that("translate sql server -> Redshift partition window function sorted descending", {
sql <- translate("select sum(count(person_id)) over (PARTITION BY procedure_concept_id order by prc_cnt desc) as count_value",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select sum(count(person_id)) OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt DESC ROWS UNBOUNDED PRECEDING) as count_value"
)
})
test_that("translate sql server -> Redshift partition window function sorted ascending", {
sql <- translate("select sum(count(person_id)) over (PARTITION BY procedure_concept_id order by prc_cnt asc) as count_value",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select sum(count(person_id)) OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt ASC ROWS UNBOUNDED PRECEDING) as count_value"
)
})
test_that("translate sql server -> Redshift partition window function no sort specified", {
sql <- translate("select sum(count(person_id)) over (PARTITION BY procedure_concept_id order by prc_cnt) as count_value",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select sum(count(person_id)) OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt ROWS UNBOUNDED PRECEDING) as count_value"
)
})
test_that("translate sql server -> Redshift partition window function with specified frame", {
sql <- translate("select MAX(start_ordinal) OVER (PARTITION BY groupid ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select MAX(start_ordinal) OVER (PARTITION BY groupid ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal"
)
})
test_that("translate sql server -> Redshift partition window function ROW_NUMBER no sort specified", {
sql <- translate("select ROW_NUMBER() over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select ROW_NUMBER() OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function CUME_DIST no sort specified", {
sql <- translate("select CUME_DIST() over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select CUME_DIST() OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function DENSE_RANK no sort specified", {
sql <- translate("select DENSE_RANK() over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select DENSE_RANK() OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function PERCENT_RANK no sort specified", {
sql <- translate("select PERCENT_RANK() over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select PERCENT_RANK() OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function RANK no sort specified", {
sql <- translate("select RANK() over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select RANK() OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function LAG no sort specified", {
sql <- translate("select LAG(mycol) over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select LAG(mycol) OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function LEAD no sort specified", {
sql <- translate("select LEAD(mycol) over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select LEAD(mycol) OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift partition window function NTILE no sort specified", {
sql <- translate("select NTILE(4) over (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select NTILE(4) OVER (PARTITION BY procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function sorted descending without partition by clause", {
sql <- translate("select sum(count(person_id)) over (order by prc_cnt desc) as count_value",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select sum(count(person_id)) OVER (ORDER BY prc_cnt DESC ROWS UNBOUNDED PRECEDING) as count_value"
)
})
test_that("translate sql server -> Redshift window function sorted ascending without partition by clause", {
sql <- translate("select sum(count(person_id)) over (order by prc_cnt asc) as count_value",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select sum(count(person_id)) OVER (ORDER BY prc_cnt ASC ROWS UNBOUNDED PRECEDING) as count_value"
)
})
test_that("translate sql server -> Redshift window function no sort specified without partition by clause", {
sql <- translate("select sum(count(person_id)) over (order by prc_cnt) as count_value",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select sum(count(person_id)) OVER (ORDER BY prc_cnt ROWS UNBOUNDED PRECEDING) as count_value"
)
})
test_that("translate sql server -> Redshift window function ROW_NUMBER no sort specified without PARTITION BY clause", {
sql <- translate("select ROW_NUMBER() over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select ROW_NUMBER() OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function CUME_DIST no sort specified without PARTITION BY clause", {
sql <- translate("select CUME_DIST() over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select CUME_DIST() OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function DENSE_RANK no sort specified without PARTITION BY clause", {
sql <- translate("select DENSE_RANK() over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select DENSE_RANK() OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function PERCENT_RANK no sort specified without PARTITION BY clause", {
sql <- translate("select PERCENT_RANK() over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select PERCENT_RANK() OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function RANK no sort specified without PARTITION BY clause", {
sql <- translate("select RANK() over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select RANK() OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function LAG no sort specified without PARTITION BY clause", {
sql <- translate("select LAG(mycol) over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select LAG(mycol) OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function LEAD no sort specified without PARTITION BY clause", {
sql <- translate("select LEAD(mycol) over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select LEAD(mycol) OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift window function NTILE no sort specified without PARTITION BY clause", {
sql <- translate("select NTILE(4) over (procedure_concept_id ORDER BY prc_cnt) as num",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(
sql,
"select NTILE(4) OVER (procedure_concept_id ORDER BY prc_cnt) as num"
)
})
test_that("translate sql server -> Redshift clustered index not supported", {
sql <- translate("CREATE CLUSTERED INDEX idx_raw_4000 ON #raw_4000 (cohort_definition_id, subject_id, op_start_date);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "-- redshift does not support indexes")
})
test_that("translate sql server -> Redshift index not supported", {
sql <- translate("CREATE INDEX idx_raw_4000 ON #raw_4000 (cohort_definition_id, subject_id, op_start_date);",
targetDialect = "redshift"
)
expect_equal_ignore_spaces(sql, "-- redshift does not support indexes")
})
test_that("translate sql server -> Redshift analyze table", {
sql <- translate("UPDATE STATISTICS results_schema.heracles_results;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "ANALYZE results_schema.heracles_results;")
})
test_that("translate sql server -> redshift DATETIME and DATETIME2", {
sql <- translate("CREATE TABLE x (a DATETIME2, b DATETIME);", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "CREATE TABLE x (a TIMESTAMP, b TIMESTAMP)\nDISTSTYLE ALL;")
})
test_that("translate sql server -> redshift DROP TABLE IF EXISTS", {
sql <- translate("DROP TABLE IF EXISTS test;", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "DROP TABLE IF EXISTS test;")
})
test_that("translate sql server -> redshift drvd()", {
sql <- translate("SELECT
TRY_CAST(name AS VARCHAR(MAX)) AS name,
TRY_CAST(speed AS FLOAT) AS speed
FROM ( VALUES ('A', 1.0), ('B', 2.0)) AS drvd(name, speed);", targetDialect = "redshift")
expect_equal_ignore_spaces(sql, "SELECT\n CAST(name AS VARCHAR(MAX)) AS name,\n CAST(speed AS FLOAT) AS speed\n FROM (SELECT NULL AS name, NULL AS speed WHERE (0 = 1) UNION ALL SELECT 'A', 1.0 UNION ALL SELECT 'B', 2.0) AS values_table;")
})
# rJava::J('org.ohdsi.sql.SqlTranslate')$setReplacementPatterns('inst/csv/replacementPatterns.csv')
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.