Search code examples
intersystems-cache

# in Caché between columns

I have a SQL query and I would like to insert a hashtag between one column and another to be able to reference in Excel, using an import option in fields delimited by #. Anyone have an idea how to do it? A query is as follows:

SELECT FC.folha,  folha->folhames,folha->folhaano, folha->folhaseq, folha->folhadesc, folha->TipoCod as Tipo_Folha, 
 folha->FolhaFechFormatado as Folha_Fechada,  folha->DataPagamentoFormatada as Data_Pgto,  
Servidor->matricula, Servidor->nome,  FC.rubrica, 
FC.Rubrica->Codigo, FC.Rubrica->Descricao, FC.fator, FC.TipoRubricaFormatado as TipoRubrica, 
FC.ValorFormatado,FC.ParcelaAtual, FC.ParcelaTotal
FROM RHFolCalculo FC WHERE folha -> FolhaFech = 1
AND folha->folhaano = 2018
and folha->folhames = 06
 and folha->TipoCod->codigo in (1,2,3,4,6,9)
Solution

  • You are generating delimited output from the query, so the first row should be a header row, with all following rows the data rows. You will really only have one column due to concat. So remove the alias from the columns, output the first row like so (using the alias here) . . .

    SELECT 'folha#folhames#folhaano#folhaseq#folhadesc#Tipo_Folha# Folha_Fechada#Data_Pgto# matricula#nome#rubrica# Codigo#Descricao#fator#TipoRubrica# ValorFormatado#ParcelaAtual#ParcelaTotal'

    UNION

    SELECT FC.folha || '#' || folha->folhames || '#' || folha->folhaano . . .

    The UNION will give the remaining rows. Note some conversion may be necessary on the columns data if not all strings.