Select Rows That Does Not Contain any Negative Or Missing Value-CodePudding

Assume a database table has a few hundred columns. In SQL statements, how would you select rows/records that do not contain any negative or missing value? Can you do it using the sqldf package for R users?

Here is an example of data frame with 6 rows and 2 columns:

D = data.frame(X = c(23, -24, 35, 12, 34, 41), 
               Y = c(100, 98, 89, NA, 56, 90))

The SQL statement(s) should only return a table containing the rows 1, 3, 5, and 6.

CodePudding user response：

text = "X Y
23  100
-24 98
35  89
12  NA
34  56
41  90"
df = read.table(text=text, header = T)
install.packages("sqldf")
library(sqldf)

conditions = c(">=0","NOT NULL")

columns = colnames(df)

applyConditions <- function(columns,conditions){
  grid = expand.grid(columns,conditions)
  apply(grid, 1, 
   function(x) paste(x, collapse = " ")
  )
  
}
select <- "SELECT * FROM df where "
where <- paste(applyConditions(columns,conditions),collapse = " AND ")

sqldf(paste(select,where))

CodePudding user response：

Unless this is a test or a job interview question the simplest answer would be to use complete:

D[complete(D) , ]