SQL - Use Where value in another Where

Question

SQL - Use Where value in another Where

Asked 3 years, 11 months ago

Viewed 58 times

0

Hello I’m not a programmer, just a curious developing a basic company system reports.

I wonder if I can use the values of a Where (invoiced bills) in another Where (orders made). I am grouping the orders made by sellers in a period and the invoiced notes of that seller in that same period, but this information is from different tables. I use a basic editor that runs within the system itself. I tried to poke around with variable declaration, but apparently it doesn’t accept the "@".

Below follows the procedure:

Select 
a.vendedor,
a.NNotas,
a.VNotas,
b.NPed,
b.VPed
from(select s.vendedor, Count(s.saida_id) NNotas, sum(s.valorproduto) VNotas
from saida s
WHERE extract(month from S.DATA) = :pmes and extract (year from S.DATA) = :pano AND s.ativo='Y'  AND S.natureza=0
group by s.vendedor) as A
Inner Join (select p.vendedor, Count(p.pedido_id) NPed,
sum(p.valorproduto) VPed
From pedido p
WHERE extract(month from P.DATA) = 11 and extract (year from P.DATA) =2020 and (p.tipovenda in ('00001','00004','00005','00011','00013'))
group by p.vendedor) As B On a.vendedor = b.vendedor

This way I get the results , but I need to put the month and the year 2 times...one for billing, another for orders.

I’ll be waiting for you. Thank you

you cannot repeat :pmes and :cloth?

– imex

2020/12/11 at 12:36
I’m sorry ...I pasted the code with the values 11 and 2020...it was a test. In vdd what should have passed had pmes and cloth in the 2 Where. Then when I run...he asks me to put 2x the month and 2x the year.... The user can put different months and years. That for a future comparison may be useful. But at the time I need to load the same month and year

– Marcelo Vinicius

2020/12/11 at 13:01
Which database and version is used?

– imex

2020/12/11 at 13:09

2 answers

Browser other questions tagged sql

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

by Rodrigo Zem • **895** points · Answer 1 · 2020-12-11T13:28:47+00:00

If the database is ORACLE can use DEFINES

https://docs.oracle.com/cd/E18283_01/server.112/e16604/ch_twelve017.htm

Tool I use to manage DB

http://www.sqltools.net/

Example: use v_cod where you need it as below

DEFINE v_cod = 3;
SELECT * 
  FROM
      ( 
        SELECT 1 COD,'PRIMEIRO' DESCR FROM DUAL UNION
        SELECT 2,'SEGUNDO'       FROM DUAL UNION
        SELECT 3,'TERCEIRO'      FROM DUAL UNION
        SELECT 4,'QUARTO'        FROM DUAL UNION
        SELECT 5,'QUINTO'        FROM DUAL
      )
 WHERE COD = &v_cod

by Anderson Alves • 1 point · Answer 2 · 2020-12-11T14:07:24+00:00

What you can do too and use a SELECT within the WHERE. Thus the return of this SELECT will be available for you to use on WHERE.
But it’s like this:

 SELECT first_name, last_name FROM customers WHERE customer_id =                 
 (SELECT customer_id FROM customers WHERE last_name = 'Brown');

Remembering that you will have to limit a little the SELECT of WHERE for can returns only the value of SPINE that you want to use .

Reference: https://imasters.com.br/back-end/como-fazer-subconsultas-um-passo-passo