Unrealized/ subquery failing

Question

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!

1

Answer 1

Hello

The subquery in SELECT can/should only return 1 record, so you should always put "TOP 1" on it, and an "ORDER BY" clause to tell you what your criteria is for a first record.

"Inner Join TR_LOTE" is obviously also no longer needed, and could generate a Cartesian product, since you are searching the single lot data and exclusively in the Queries.

The "group by R.COD_MATERIAL, M.DESCRICAO" is also no longer required, as our main SELECT no longer contains aggregator functions.

Then it would be:

select
  R.COD_MATERIAL, 
  M.DESCRICAO, 
  (select top 1 L.LOTE from TR_LOTE L where L.LOTE = R.LOTE order by L.DT_VALIDADE) as LOTE,
  (select top 1 L.DT_VALIDADE from TR_LOTE L
   where L.LOTE = R.LOTE order by L.DT_VALIDADE) as DT_VALIDADE,
  (select top 1 R.COD_BARRA from TR_LOTE L
   inner join TR_REL_MATERIAL_LOTE R on R.LOTE = L.LOTE
   order by L.DT_VALIDADE) as COD_BARRA

from TR_REL_MATERIAL_LOTE R
inner join TR_MATERIAL M on R.COD_MATERIAL = M.COD_MATERIAL 
order by R.COD_MATERIAL

This query however was not elegant, because at home subquery we looked again for the same query in TR_LOTE, always ordered by DT_VALIDAE, only for in each case return a different column. Also, in the third subquery, I refer again TR_REL_MATERIAL_LOTE, in a new instance, which is the same table as my main query.

We don’t need to use Queries only in the SELECT clause. There are several other points where we can use them, which can give better solutions.

This problem fits a pattern that often happens, in which we have to find the field of least value (in this case, DT_VALIDAE) and, from there, find several other fields related to this. There is no trivial way to bring it all at once. This type of pattern requires a two-step query break.

A better solution would be:

select
  R.COD_MATERIAL, 
  M.DESCRICAO, 
  R.LOTE,
  MenorDataMaterial.DT_VALIDADE,
  R.COD_BARRA
from (
  select RX.COD_MATERIAL, MIN(L.DT_VALIDADE) [DT_VALIDADE]
  from TR_REL_MATERIAL RX
  inner join TR_LOTE L on L.LOTE = RX.LOTE
  group by RX.COD_MATERIAL
) MenorDataMaterial
inner join TR_REL_MATERIAL_LOTE R on R.COD_MATERIAL = MenorDataMaterial.COD_MATERIAL
inner join TR_MATERIAL M on R.COD_MATERIAL = M.COD_MATERIAL 
order by R.COD_MATERIAL

It got a lot simpler, didn’t it? There are still other possible solutions.