Problems in a SQL query [closed]

1

Hello,

In the following query, an error is encountered in the execution:

select 
    fto.id_taxon, 
    fto.cd_sexo as fto_cd_sexo, 
    fto.cd_idade as fto_idade, 
    x.ftv_cd_sexo, 
    x.ftv_cd_idade, 
    x.id_fv 
from 
    tb_foto_ocorrencia fto 
    left join (
        select 
            ftv.id_taxon as id_t, 
            ftv.cd_sexo as ftv_cd_sexo, 
            ftv.cd_idade as ftv_cd_idade, 
            ftv.id_foto_ocorrencia as id_fo, 
            ftv.id_foto_validacao as id_fv, 
            sum(
                case when ftv.id_taxon <> fto.id_taxon then 1 else 0 end
            ) 
        from 
            tb_foto_validacao ftv 
        group by 
            id_fo
    ) x on fto.id_foto_ocorrencia = x.id_fo 
WHERE 
    fto.fl_validado = 'n' 
ORDER BY 
    'fto'.'id_foto_ocorrencia' ASC

The error displayed is as follows:

  

1054 - Unknown column 'fto.id_taxon' in 'field list'

But as you can see in the query, the fields are defined.

    
asked by anonymous 23.12.2015 / 15:37

2 answers

0

When trying to rename the table to a temporary name one should use AS, that is, AS may be missing between the actual and temporary table name, just after From.

select 
fto.id_taxon, 
fto.cd_sexo as fto_cd_sexo, 
fto.cd_idade as fto_idade, 
x.ftv_cd_sexo, 
x.ftv_cd_idade, 
x.id_fv from 
tb_foto_ocorrencia as fto 
left join (
    select 
        ftv.id_taxon as id_t, 
        ftv.cd_sexo as ftv_cd_sexo, 
        ftv.cd_idade as ftv_cd_idade, 
        ftv.id_foto_ocorrencia as id_fo, 
        ftv.id_foto_validacao as id_fv, 
        sum(
            case when ftv.id_taxon <> fto.id_taxon then 1 else 0 end
        ) 
    from 
        tb_foto_validacao as ftv 
    group by 
        id_fo
) x on fto.id_foto_ocorrencia = x.id_fo WHERE 
fto.fl_validado = 'n' ORDER BY 
'fto'.'id_foto_ocorrencia' ASC'
    
14.07.2016 / 00:07
0

Elliott,

The error occurs because you are trying to access data from an external query within a sub-select. To correct the error, you should remove fto.id_taxon from Sum() in Sub Select.

I see that the sum column is not being selected, its sum being unnecessary. In any case, the following query is corrected, with the sum being selected in the main select.

select 
    fto.id_taxon, 
    fto.cd_sexo as fto_cd_sexo, 
    fto.cd_idade as fto_idade, 
    x.ftv_cd_sexo, 
    x.ftv_cd_idade, 
    x.id_fv,
    sum(case when x.id_t <> fto.id_taxon then 1 else 0 end) Soma
from 
    tb_foto_ocorrencia fto 
    left join (
        select 
            ftv.id_taxon as id_t, 
            ftv.cd_sexo as ftv_cd_sexo, 
            ftv.cd_idade as ftv_cd_idade, 
            ftv.id_foto_ocorrencia as id_fo, 
            ftv.id_foto_validacao as id_fv 
        from 
            tb_foto_validacao ftv 
        group by 
            id_fo
    ) x on fto.id_foto_ocorrencia = x.id_fo 
WHERE 
    fto.fl_validado = 'n' 
ORDER BY 
    'fto'.'id_foto_ocorrencia' ASC
    
14.07.2016 / 02:54