Como usar o UNION ALL?

[josnell]

Olá, pessoal!

Estou tentando adaptar 2 consultas para colocar na mesma consulta usando o UNION.

Está assim:

SELECT CLIENTE.COD_CLI  AS CODIGO,        CLIENTE.NOM_CLI AS NOME,     CABECSAI.REF_LSC AS LANC,
       CABECSAI.DES_LSC AS DESTINATARIO,  CABECSAI.DC1_LSC AS DOC1,    CABECSAI.DC2_LSC AS DOC2,
       CABECSAI.DEM_LSC AS EMISSAO,       CABECSAI.NAT_LSC AS NATOP,   CLIENTE.CGC_CLI  AS CNPJ,    
       DADOSSAI.BAS_LSV AS BASECALC,      DADOSSAI.IMP_LSV AS IMPOSTO, DADOSSAI.ISE_LSV AS ISENTAS,
       DADOSSAI.OUT_LSV AS OUTRAS,        DADOSSAI.ODE_LSV AS OUTCRED, DADOSSAI.TIP_LSV AS TIPO,
       CABECSAI.VAL_LSC AS VALCONT,       CABECSAI.OB1_LSC AS VALOBS1,
       CABECSAI.OB2_LSC AS VALOBS2,       CABECSAI.OB3_LSC AS VALOBS3,
       CABECSAI.OB4_LSC AS VALOBS4,       CABECSAI.OB5_LSC AS VALOBS5,
       CABECSAI.BAS_LSC AS BASESUBST,     CABECSAI.ICS_LSC AS ICMSSUBST
FROM "C:\Fiscal\TABELAS\030\TbCli.db" CLIENTE LEFT JOIN
     "C:\Fiscal\TABELAS\030\TbLsc" CABECSAI ON (CLIENTE.COD_CLI   = CABECSAI.DES_LSC) JOIN
     "C:\Fiscal\TABELAS\030\TbLsv" DADOSSAI ON (DADOSSAI.RCB_LSV  = CABECSAI.REF_LSC)
WHERE (Extract(year from CABECSAI.DEM_LSC) = 2010) and (extract(month from CABECSAI.DEM_LSC) = 11)
UNION
SELECT COD_CLI AS CODIGO,                 NOM_CLI AS NOME,             CAST('' AS CHAR) AS LANC,
       CAST('' AS CHAR) AS DESTINATARIO,  CAST('' AS CHAR) AS DOC1,    CAST('' AS CHAR) AS DOC2,
       CAST('' AS CHAR) AS EMISSAO,       CAST('' AS CHAR) AS NATOP,   CLIENTE.CGC_CLI  AS CNPJ,    
       CAST(0 AS FLOAT) AS BASECALC,      CAST(0 AS FLOAT) AS IMPOSTO, CAST(0 AS FLOAT) AS ISENTAS,
       CAST(0 AS FLOAT) AS OUTRAS,        CAST(0 AS FLOAT) AS OUTCRED, CAST('' AS CHAR) AS TIPO,
       CAST(0 AS FLOAT) AS VALCONT,       CAST(0 AS FLOAT) AS VALOBS1,
       CAST(0 AS FLOAT) AS VALOBS2,       CAST(0 AS FLOAT) AS VALOBS3,
       CAST(0 AS FLOAT) AS VALOBS4,       CAST(0 AS FLOAT) AS VALOBS5,
       CAST(0 AS FLOAT) AS BASESUBST,     CAST(0 AS FLOAT) AS ICMSSUBST
FROM     "C:\Fiscal\Tabelas\030\TBISS" ISS JOIN
         "C:\Fiscal\TABELAS\030\TBCLI.DB" CLI ON ISS.DES_ISS = CLI.COD_CLI 
WHERE    (Extract(year from ISS.DAT_ISS) = 2010) and (extract(month from ISS.DAT_ISS) = 11)
ORDER BY CODIGO, LANC, TIPO

Mas o seguinte erro é retornado: "Type Mismatch in expression"

Se não estou enganado, este erro é quando a sintaxe do comando está errada.

O que sei da sintaxe do comando UNION é que os campos nas 2 consultas devem ser os mesmos (nome) e estar na mesma orde. Todos os campos que estiverem acima devem estar na consulta de baixo.

Como não quero os mesmos dados das duas consultas, anulei alguns campos usando o CAST.

[josnell]

Pra tirar uma base. tenho aqui uma consulta onde esse comando é utilizado e funciona bem

SELECT DEN_LEC AS DATA,
       VAL_LEC            AS VLCONT_ENT,
       CAST("0" AS FLOAT) AS VLCONT_SAI
FROM "TbLec.DB"
WHERE (DEN_LEC BETWEEN :DT1_AGC AND :DT2_AGC)
UNION ALL
SELECT DEM_LSC AS DATA,
       CAST("0" AS FLOAT) AS VLCONT_ENT,
       VAL_LSC            AS VLCONT_SAI
FROM "TbLsc"
WHERE (DEM_LSC BETWEEN :DT1_AGC AND :DT2_AGC)
ORDER BY DATA

Alguém sabe porque a primeira consulta postada não funciona?

[gandalfnho]

O nome não precisa ser o mesmo, pode remover os AS no segundo SELECT. O que importa é que devem estar na mesma ordem e serem do mesmo tipo de dados e tamanho. Verifique campo a campo para ver se seu equivalente no outro SELECT tem o mesmo tipo de dados e tamanho.