Union problem in mysql

0

I am trying to perform a query and is giving the following error:

  

Unknown column 'FILIAL' in 'field list'

The query is as follows:

SELECT TICKET, DATAHORA, TIPO, ESTADO, CRITICIDADE, LOGIN, PARECER, INFOPEND, PROP, DATAPROP, PREV, SRD, PROT, REAB,FILIAL
FROM (
SELECT 
      TC.TN TICKET, TC.CREATE_TIME DATAHORA,  TT.NAME TIPO, TS.NAME ESTADO, TP.NAME CRITICIDADE, US.LOGIN LOGIN,
      CASE  
        WHEN TH.NAME LIKE  '%PARECER%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PARECER,
      CASE  
        WHEN TH.NAME LIKE  '%INFORMACOESPENDENTES%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END INFOPEND,
      CASE  
        WHEN AR.A_SUBJECT LIKE  '%ATUALIZAÇÃO PROPRIETÁRIO%' THEN LEFT(AR.A_FROM, POSITION('<' IN (AR.A_FROM))-1)
        ELSE ''
        END PROP,
      CASE  
        WHEN AR.A_SUBJECT LIKE  '%ATUALIZAÇÃO PROPRIETÁRIO%' THEN (AR.CREATE_TIME)
        ELSE ''
        END DATAPROP,
      CASE  
        WHEN TH.NAME LIKE  '%PREVISAOATENDIMENTO%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PREV,
      CASE  
        WHEN TH.NAME LIKE  '%SRD%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END SRD,
      CASE  
        WHEN TH.NAME LIKE  '%PROTOCOLO%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PROT,
      CASE  
        WHEN TS.NAME LIKE  '%REABERTO' THEN (TS.NAME)
        ELSE ''
        END REAB

   FROM OTRS2.TICKET TC
   INNER JOIN OTRS2.TICKET_TYPE TT ON TC.TYPE_ID = TT.ID
   INNER JOIN OTRS2.TICKET_STATE TS ON TC.TICKET_STATE_ID = TS.ID
   INNER JOIN OTRS2.TICKET_PRIORITY TP ON TC.TICKET_PRIORITY_ID=TP.ID
   INNER JOIN OTRS2.USERS US ON TC.CREATE_BY=US.ID
   INNER JOIN OTRS2.TICKET_HISTORY TH ON TC.ID=TH.TICKET_ID 
   INNER JOIN OTRS2.ARTICLE AR ON TC.ID=AR.TICKET_ID
   UNION 
        SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CUSTOMER.LOGIN FILIAL FROM OTRS2.CUSTOMER_USER CUSTOMER
 ) A

PS: Until I put UNION , the query was working fine and returning the results.

    
asked by anonymous 17.07.2018 / 14:49

2 answers

7

That's exactly what you're talking about in the message, the selects need to have the same number of columns.

In case you tried to bring only the last column FILIAL in the last select with the OTRS2.CUSTOMER_USER table.

For a possible solution, you have to fill in the fields with default values for the other columns that are not in the OTRS2.CUSTOMER_USER table. In addition, fill a default value for the FILIAL column in the other selects.

In the example below, consider CAMPO_4 as its FILIAL field:

select CAMPO_1, CAMPO_2, CAMPO_3, null as CAMPO_4
  from TABELA_1
 union
select null as CAMPO_1, null as CAMPO_2, null as CAMPO_3, CAMPO_4
  from TABELA_2
    
17.07.2018 / 15:25
-1

I was missing put null branch on the first select and give the alias to each of the nulls of the second select! Thanks to those who helped me!

    
18.07.2018 / 01:48