okay. so I have a list of db as a data frame with one column called db name, and I want to select a db from each list of db, and build SPC_TABLE for each tables in a db. It does this for all db. so two procedures.
At the end of script run, I want to see a list of SPC_TABLE for each db and for all db.
so i ran a 'for loop' twice, first 'for loop' calls the db, 2nd 'for loop' runs SPC_TABLE for each db.
The 2nd 'for loop' for SPC_TABLE works fine , but the first 'for loop' to call a db has been problematic. please assist me and see script below:
for(j in 1:nrow(DB_LIST)){
w=DB_LIST[j,1]
x="INFORMATION_SCHEMA.TABLES"
y=print(paste(w,x, sep = "."))
z= "SELECT * FROM"
p=print(paste(z,y))
TABLE_LIST[j]=sqlQuery(con,p[j])
# SELECTING DB
A=TABLE_LIST[j][,1] #LIST OF TABLE_CATALOGUE
B=TABLE_LIST[j][,2] # LIST OF DBO
C=TABLE_LIST[j][,3] #LIST OF TABLE_NAME
D=print(paste(A,B,C, sep = ".")) # MERGE ALL THREE WITH DOT
E= "SELECT * FROM" # PRINT SELECT * FROM
F=print(paste(E,D)) # MERGE 'SELECT * FROM' WITH 'A.B.C'
SPC_TABLE[j,1]= TABLE_LIST[j,1][-c(1,2,4)]
SPC_TABLE[j,1]$Count_of_row <- 0
SPC_TABLE[j,1]$Count_of_column <- 0
SPC_TABLE[j,1]$Percentage_of_null=0
SPC_TABLE[j,1]$Run_date=0
for (i in 1:nrow(SPC_TABLE[j])){
SPC_TABLE[j,1][i, "Count_of_row"] = nrow(sqlQuery(con,F[i]));
SPC_TABLE[j,1][i, "Count_of_column"] = ncol(sqlQuery(con,F[i]));
SPC_TABLE[j,1][i,"Percentage_of_null"]=100*length(which(is.na(sqlQuery(con,F[i]))))/nrow(sqlQuery(con,F[i]))*ncol(sqlQuery(con,F[i]));
SPC_TABLE[j,1][,"Run_date"]=Sys.Date()
}
}
Return SPC_ANALYTICS= list(SPC_TABLE[j,1])