View in SQL ERROR

Asked

Viewed 22 times

0

I’m trying to ride a SQL to use in the methodology view. But when executing, it produces an error.

I need as a result the amount of items that are in the 'Released' condition and the amount of items that are in the 'In Use' condition. So I tried to do this way below, but it presented error.

Is there any other way I can get these results?

SQL - CODE

SELECT
    c1.id_ativo_externo,
    c1.id_obra,
    c2.codigo_obra,
    c2.endereco,
    c2.endereco_cidade,
    c1.condicao,
    c1.situacao,
    IF c1.condicao = 'Liberado' THEN count(*) as total_liberado ELSE count(*) as total_emuso END IF
FROM ativo_externo AS c1
JOIN obra AS c2 ON c2.id_obra=c1.id_obra

ERROR - CODE

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'c1.condicao = 'Liberado' THEN count(*) as total_liberado ELSE count(*) as tot...' at line 9

I researched the terminology IF THEN ELSE, but I was unsuccessful.

Modification: Removed the WHERE.

mysql

  • If you have the filter WHERE c1.condicao = 'Liberado', why you need the condition IF c1.condicao = 'Liberado' since it will always be TRUE?

  • 1

    Your SQL is wrong in general, especially on WHERE no longer need to use this if that even does the same thing only changes in terms of the label, despite the if ta wrong. IE, ta all wrong.

  • In fact, the query I need to mount is to return all that are released and all that are in use... That’s why I had Where Released, but we had to take it off.

1 answer

0

Is there any other way I can get these results?

Yes, another way to bring this result would be by using sub-consultations as an example below:

SELECT
    c1.id_ativo_externo,
    c1.id_obra,
    c2.codigo_obra,
    c2.endereco,
    c2.endereco_cidade,
    c1.condicao,
    c1.situacao,
    (Select count(*) 
       from ativo_externo a 
       where a.id_ativo_externo = c1.id_ativo_externo 
       and c1.condicao = 'Liberado')  as total_liberado,
    (Select count(*) 
       from ativo_externo a 
       where a.id_ativo_externo = c1.id_ativo_externo 
       and c1.condicao = 'Em uso')  as total_emuso        
FROM ativo_externo AS c1
JOIN obra AS c2 ON c2.codigo_obra=c1.id_obra

It was necessary to make a correction in the original query, because in the line JOIN obra AS c2 ON c2.id_obra=c1.id_obra mentions c2.id_obra but in the fields of consultation we have Select ... c2.codigo_obra, so it is necessary to correct to JOIN obra AS c2 ON c2.codigo_obra=c1.id_obra, for example.

In the suggested query, a sub-consultation is being carried out to generate the result total_liberado and another sub-allocation to generate the result total_liberado.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.