Decode function e (+)

2

Problem:

Resolution:

SELECTNOME,DECODE(COUNT(CODIGO_LIVRO),0,'NENHUM',COUNT(CODIGO_LIVRO))"NR LIVROS"
FROM AUTORES A,LIVROS L
WHERE A.CODIGO_AUTOR=L.CODIGO_AUTOR(+)
GROUP BY NOME
ORDER BY 1;

Tables:

You can not reply to this topic

Why do I need the second COUNT(CODIGO_LIVRO) within the function DECODE and what does (+) mean? If I'm not mistaken, (+) is a join but I do not understand how it works and why it's needed there ...

    
asked by anonymous 17.12.2017 / 19:05

1 answer

1

Good afternoon Droopy!

The (+) symbol indicates an outer join. As it is next to the field of the BOOKS table, it means that it will bring the composite record of the join even if there is no book of that author. For example, if you register my name as an Author and do not book any books, my name will appear in the result with the total of "NONE". If you take the "(+)" and make the select again, my name will not come out in the selection.

As for DECODE, as I understand it, it is being used only to not return 0 when there are no books. When that happens it returns "NONE", but when it finds some it just puts the number.

    
26.12.2017 / 19:37