-1

I need to create a function that receives a variable name and an operation and creates the code to run in sql. I wrote a function:

texto <- function(var,opera) {
    paste0(
        opera, '(b.', var, ') as ', var, '_', opera, '_12,', '\n',
        opera, '(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.', var, ' else 0 end) as ', var,'_', opera,'_6,', '\n',
        opera, '(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.', var, ' else 0 end) as ', var,'_', opera,'_3,', '\n',
        opera, '(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.', var, ' else 0 end) as ', var,'_', opera,'_1,'
    )
}

With list of variables and operations:

vars <- list('vl_Saldo_Prom_Deu_Aco', 'vl_Saldo_Prom_Deu_Exc')
ops <- list('sum', 'max')

I run a loop:

for (i in vars) {
    for (j in ops) {
        print(cat(texto(i,j)))
    }
}

And I get:

sum(b.vl_Saldo_Prom_Deu_Aco) as vl_Saldo_Prom_Deu_Aco_sum_12,
sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_sum_6,
sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_sum_3,
sum(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_sum_1,NULL
max(b.vl_Saldo_Prom_Deu_Aco) as vl_Saldo_Prom_Deu_Aco_max_12,
max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_max_6,
max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_max_3,
max(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_max_1,NULL
sum(b.vl_Saldo_Prom_Deu_Exc) as vl_Saldo_Prom_Deu_Exc_sum_12,
sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_sum_6,
sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_sum_3,
sum(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_sum_1,NULL
max(b.vl_Saldo_Prom_Deu_Exc) as vl_Saldo_Prom_Deu_Exc_max_12,
max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_max_6,
max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_max_3,
max(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_max_1,NULL

Which is almost fine, except for the NULL word. I'd like to know why it is being printed and how to avoid it.

bgoldst
  • 34,190
  • 6
  • 38
  • 64
GabyLP
  • 3,649
  • 7
  • 45
  • 66

1 Answers1

1

The cat() function prints text directly to the standard output stream. It does not return anything. Or rather, to be more precise, it always invisibly returns a NULL value, as the documentation states under the Value section. Hence your print() call is printing NULL, which causes an unadorned NULL to be printed to the standard output stream.

Therefore, the NULL text is not part of the SQL that you are generating inside texto(). The appearance of NULL on your console is an artifact of your erroneous print() call, nothing more.

You can solve the problem by removing the print() call entirely. It is not necessary since cat() is already printing the text directly to the standard output.

However, since there is no linefeed character included at the end of the generated SQL, I would suggest you add one to the cat() call by passing it as a second argument to the variadic argument list taken by cat(). Thus we have:

for (i in vars) for (j in ops) cat(texto(i,j),'\n');
## sum(b.vl_Saldo_Prom_Deu_Aco) as vl_Saldo_Prom_Deu_Aco_sum_12,
## sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_sum_6,
## sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_sum_3,
## sum(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_sum_1,
## max(b.vl_Saldo_Prom_Deu_Aco) as vl_Saldo_Prom_Deu_Aco_max_12,
## max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_max_6,
## max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_max_3,
## max(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Aco else 0 end) as vl_Saldo_Prom_Deu_Aco_max_1,
## sum(b.vl_Saldo_Prom_Deu_Exc) as vl_Saldo_Prom_Deu_Exc_sum_12,
## sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_sum_6,
## sum(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_sum_3,
## sum(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_sum_1,
## max(b.vl_Saldo_Prom_Deu_Exc) as vl_Saldo_Prom_Deu_Exc_max_12,
## max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M6 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_max_6,
## max(case when(b.cd_Anio*100+b.cd_Mes)>=a.cd_periodo_observacion_M3 then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_max_3,
## max(case when(b.cd_Anio*100+b.cd_Mes)=a.cd_periodo_observacion then b.vl_Saldo_Prom_Deu_Exc else 0 end) as vl_Saldo_Prom_Deu_Exc_max_1,

For more on R return value visibility, I would recommend reading http://rfunction.com/archives/799, and the two functions invisible() and withVisible().

bgoldst
  • 34,190
  • 6
  • 38
  • 64