I'm doing this query, and I need to check the cost value of a product using a subquery, because the note table is the same as input and output. Products do not repeat. What sets sales apart and cost. It's the note series. It is returning the cost as null. But if I include a product code manually. It works.
Structure: %code%
%pre%How it works:
%pre%You must specify the resultset fields. Please try the modification below:
%pre%I'm doing this query, and I need to check the cost value of a product using a subquery, because the note table is the same as input and output. Products do not repeat. What sets sales apart and cost. It's the note series. It is returning the cost as null. But if I include a product code manually. It works.
Structure:
mercadoria = varchar(12)
SELECT *,
(select valornota from nota as u inner
join itemnota as j
on u.id_nfcapa = j.id_nfcapa
where j.mercadoria = i.mercadoria and u.serienf = '001') as custo
FROM nota as n
inner join itemnota as i on n.id_nfcapa = i.id_nfcapa
WHERE n.datanf BETWEEN '2017-08-01' AND '2017-08-31'
AND n.serie = '1'
How it works:
SELECT *,
(select valornota from nota as u inner
join itemnota as j
on u.id_nfcapa = j.id_nfcapa
where j.mercadoria = 'PROD1234' and u.serienf = '001') as custo
FROM nota as n
inner join itemnota as i on n.id_nfcapa = i.id_nfcapa
WHERE n.datanf BETWEEN '2017-08-01' AND '2017-08-31'
AND n.serie = '1'
You must specify the resultset fields. Please try the modification below:
SELECT i.mercadoria, custo
(select valornota from nota as u
inner join itemnota as j
on u.id_nfcapa = j.id_nfcapa
where j.mercadoria = u.mercadoria and u.serienf = '001') as custo
FROM nota as n
inner join itemnota as i on n.id_nfcapa = i.id_nfcapa
WHERE n.datanf BETWEEN '2017-08-01' AND '2017-08-31'
AND n.serie = '1'