Subquery in Oracle SQL

Question

Subquery in Oracle SQL

Navigation

#1 by (0 votes)

0

I have a code that aims to return open invoices from a customer in a specific time period. But I want to add a field that displays the total number of invoices, regardless of the debit period.

The code looks like this:

SELECT
    fatura.num_cli "Cliente", -- Código do cliente
    fatura.num_doc "Número do documento", -- Número da NF
    fatura.dta_venc "Vencimento", -- Data de vencimento
    cliente.cod_situ "Situação" -- Situação do cliente, está em outra tabela
FROM fatura
LEFT OUTER JOIN cliente ON fatura.num_cli = cliente.cod_cliente
WHERE fatura.cod_situ_com = 'AB' -- Situação da fatura - aberto
AND trunc(fatura.dta_venc) between '01-mai-2018' and '31-mai-2018' -- Período de vencimento

But in addition, I wanted to add a field that would return the count of the number of open invoices, regardless of the due date. The structure of the tables is as follows:

In this case, each customer can have multiple invoices, and each invoice is linked to a customer.

The code should return the Customer Number; the total open invoices of this customer, regardless of the period (this would be the sub select); the open invoice code and the invoice maturity (both of these within the established period).

sql oracle

asked by anonymous 24.09.2018 / 14:21

1 answer

Filling select with latitude, longitude and city according to Json Export too large table

score 0 · Accepted Answer

I've changed your logic a bit to get the result. I imagine you can have client without invoices, so the base of your SQL is the CLIENTE table.

From it I made a left join to bring the invoices in the period and I did the sub-select to bring the total invoices.

The filter for invoices should be enclosed with left join , otherwise if you put in the where, left join becomes inner join .

select
  b.NOM_CLI,
  a.NUM_CLI "Cliente",   
  a.NUM_DOC "Número do documento",
  a.DTA_VENC "Vencimento",
  b.COD_SITU "Situação",
  (select count(*) from FATURA c where c.NUM_CLI = b.COD_CLIENTE) TotalFatura
from
  CLIENTE b
  left join FATURA a on 
     b.COD_CLIENTE = a.NUM_CLI 
     and a.COD_SITU = 'AB' 
     and a.DTA_VENC between TO_DATE('01/01/2018', 'dd/MM/yyyy') and TO_DATE('31/12/2018', 'dd/MM/yyyy')

Practical example

link