Subquery in Oracle SQL

Asked

Viewed 544 times

0

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

The code is 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

However, in addition, I wanted to add a field that would return the count of the number of open invoices, regardless of maturity.

The structure of the tables is as follows:

Invoice:
num_doc | num_cli | dta_venc | cod_situ

01 | 12225 | 01-mar-2018 | AB
Client:
cod_client | nom_cli | Cpf | cod_situ
1201 | John | 00000000000 | Judged

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

The code must return the Customer Number; the total of that customer’s open invoices, regardless of the period (this would be the sub select); the code of the open invoice and the expiration of the invoice (these two within the set period).

sql oracle

  • You’ll probably have to make one sub-select. To help you more it is necessary that you put the structure of your tables and if possible even some data to test. The best of worlds would be for you to put your structure and data in sqlfiddle.com and make it available to us. Also, it would be interesting if you put the expected result of query.

  • 1

    as@RobertodeCampossaid, you would have to do a subquery, otherwise you will have to use group by, and group all the fields you have in your select to do in a single query, but without seeing the table structures, difficult to help you

  • I edited the question to insert the structure of the tables.

1 answer

0


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

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

The filter for invoices should stay with the left join, otherwise if you put in the Where, the left join flipped 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

http://sqlfiddle.com/#! 4/a54d70/15

Browser other questions tagged sql oracle

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