Unexpected output-For Loop

I Am running a nested for loop in R. I have created a dataframe called dataframe from an SQL query. My data are arranged as groups of 3 databases. The following

CampaignGUID- this has a column by means of which I refer to other columns dataframe

 for (i in length(nrow(dataframe$CampaignGUID))){
  sql1 <- paste("SELECT T.TenantLookupID, T.TenantShortCode, 
  B.BusinessUnitShortCode, C.CampaignShortCode
            from TenantLookup AS T
            JOIN BusinessUnitLookup AS B ON T.TenantLookupID = 
             B.TenantLookupID
            JOIN CampaignLookup AS C ON C.TenantLookupID = B.TenantLookupID 
            where T.TenantID = '611c5d1c_f0bc_4bd4_b1c1_f6800d50fc34' AND 
           B.BusinessUnitID = 0
            AND C.CampaignID =", i,sep="")
           dataframe2<-dbGetQuery(jobdbconn, sql1)

Second part below

      blasttable <- paste("rptcampaignblast_",(dataframe$CampaignGUID))

     blasttable2<-gsub("-", '_', blasttable)
     blasttable3<-sub(' ', '', blasttable2)
    (blasttable3)
    as.data.frame(blasttable3)

    TenantLookupID1 <- dataframe2$TenantLookupID
     TenantShortCode1 <- dataframe2$TenantShortCode
     BusinessUnitShortCode1 <- dataframe2$BusinessUnitShortCode
     CampaignShortCode1 <- dataframe2$CampaignShortCode


    sql2 <- paste("select count(DISTINCT BINARY R1) from ", blasttable3)

    blastcount<-dbGetQuery(reportconn, sql2)

    sql3 <- paste("select count(DISTINCT BINARY R1) from 
   rptcampaignopentransaction where T1 ='", TenantShortCode1, "' AND B1 ='", 
     BusinessUnitShortCode1, "'  AND  C1 ='", CampaignShortCode1, "'")

    opencount<-dbGetQuery(reportconn, sql3)

    sql4 <- paste("select count(DISTINCT BINARY R1) from 
    rptcampaignlinkclickstransaction where T1 ='", TenantShortCode1, "' AND 
    B1 ='", BusinessUnitShortCode1, "'  AND  C1 ='", CampaignShortCode1, 
    "'")

     linkclickcount<-dbGetQuery(reportconn, sql4)

   sql5 <- paste("select count(DISTINCT BINARY R1) from 
   rpt_mailbounces.rptcampaignmailbounce where T1 ='", TenantShortCode1, "' 
  AND B1 ='", BusinessUnitShortCode1, "'  AND  C1 ='", CampaignShortCode1, 
  "'")

  bounchcount<-dbGetQuery(reportconn, sql5)

  delivered <- (blastcount-bounchcount)






     DF2$CampaignGUID1<-CampaignGUID
    DF2$Bounced<-bounchcount
   DF2$delivered=delivered
    DF2$reach= blastcount-bounchcount
  DF2$blastcount=blastcount
  DF2$click=linkclickcount
  DF2$open=opencount




   DF2<-rbind(DF2$Bounced, DF2$delivered, DF2$reach, 
  DF2$blastcount,DF2$click, DF2$open, DF2$CampaignGUID)

  } #for i

The final output of linkclickcount, bounchcount etc should be a list of values. The output is however a single value. I am not sure what the error is.

Solution

It's really hard to understand your code, however, here are a few issues I see:

Your for loop control statement is doing for(i in length(nrow(df))){... You should try to evaluate length(nrow(df)) which returns just a length 1 vector. Therefore i evaluates to 1. To solve this, do:

for(i in 1:nrow(df)){...
You are not assigning your results of the query to a list. If you just re-run each iteration of the loop, it will overwrite your data. You'll need to do something like:

query_results <- list()

query_results[[i]] <- dbGetQuery(jobdbconn, sql1)
Your SQL filter is taking the raw form of i (or just the value of 1) in:

paste(...AND C.CampaignID =", i,sep="")

You'll need to actually set the SQL filter to the campaignGUID by:

paste(...AND C.CampaignID =", dataframe$CampaignGUID[i], ,sep="")

Overall, I would recommend evaluating each section of your code once manually and seeing the outputs before writing a for loop. Good luck