Subquery returning null value

Question

I am doing this query, and I need to query the cost value of a product using a subquery, because the note table is the same as input and output. The products do not repeat. What differentiates sale and cost. It is the series of the note. It is returning the cost as null. But if I include a product code manually. It works.

Dunghill: 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'

That’s 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'

Answer 1

You must specify which fields of the resultset are. Try with 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'