-1
Using Sub-Query, make a query that returns the active materials, with the Description, Batch, Barcode Cod and Validity only of Dt Smaller validity of each Material.
My code so far:
select R.COD_MATERIAL,
M.DESCRICAO,
(select L.LOTE from TR_LOTE L
where L.DT_VALIDADE = (select min(L.DT_VALIDADE) from TR_LOTE L
where L.LOTE = (select L.LOTE from TR_LOTE L))) as LOTE,
(select R.COD_BARRA from TR_REL_MATERIAL_LOTE R
where R.LOTE = NULL) as COD_BARRAS,
min(L.DT_VALIDADE)
from TR_REL_MATERIAL_LOTE R
inner join TR_MATERIAL M on R.COD_MATERIAL = M.COD_MATERIAL
inner join TR_LOTE L on R.LOTE = L.LOTE
group by R.COD_MATERIAL, M.DESCRICAO
order by R.COD_MATERIAL
I’m not getting back the expected, I don’t know if it was for lack of interpretation or I have no idea how to do it.
But I need specifically return the Batch of each Material on their respective expiry date (shorter expiry date) in addition to your barcode, following the example of the return of this query below:
select R.COD_MATERIAL,
M.DESCRICAO,
min(L.DT_VALIDADE)
from TR_REL_MATERIAL_LOTE R
inner join TR_MATERIAL M on R.COD_MATERIAL = M.COD_MATERIAL
inner join TR_LOTE L on R.LOTE = L.LOTE
group by R.COD_MATERIAL, M.DESCRICAO
order by R.COD_MATERIAL
The attached image shows how the tables are structured. Thanks to those who can help!