Oracle SQL result ordering

Navigation
4

I've done the following query, which was ordered by the product batch creation date. With the migration from version 11 to Oracle R12, the batch field became the migration date, that is, yesterday. As a workaround, we'll use the lote field, which contains the original creation date. But order by sorts as numeric, not as date. How could in SQL change to this field be sorted as being out of date?

SQL: 

Select
    -- i.item_no Codigo,
    msi.segment1 Codigo,
  -- i.item_desc1 Descricao,
  msi.description Descricao,
    -- l.lot_no,
    mln.lot_number Lote,
  --l.sublot_no,
    mln.parent_lot_number Sublote,
 -- l.lot_desc,
    mln.description Desc_Lote,
 -- l.qc_grade,
    mln.grade_code,
 -- l.expaction_code,
    mln.expiration_action_code,
 -- l.inactive_ind,
    nvl(mln.disable_flag,'1') Ativo, 
 -- l.origination_type,
    mln.origination_type,
 -- l.expaction_date,   
  mln.expiration_action_code,
 -- l.lot_created,
  mln.creation_date Data_Criacao,
 -- l.expire_date,
    mln.expiration_date,
 -- l.retest_date,
  mln.retest_date,
  --  round(sysdate - l.lot_created) Dias,
      round(sysdate - mln.creation_date) Dias,
   -- round(sum(ili.loct_onhand)) qtde
      round(sum(moq.transaction_quantity)) qtde
From  mtl_lot_numbers       mln,
      mtl_system_items      msi,
      mtl_onhand_quantities moq,
      mtl_item_locations    mil

WHERE msi.inventory_item_id = mln.inventory_item_id
      and   msi.organization_id   = mln.organization_id
      and   msi.inventory_item_id = moq.inventory_item_id
      and   msi.organization_id   = moq.organization_id
    --  and   msi.organization_id   = 131 -- SOMENTE PA0
      and   moq.locator_id        = mil.inventory_location_id      
      and   msi.segment1 between '80000' and '99999'    
   -- and   l.lot_id  != 0 -- NAO EXISTE MAIS
   --and    i.item_no >= nvl(CodigoIni,'80000')
   --and    i.item_no <= nvl(CodigoFim,'99999')

group by        msi.segment1,
  msi.description,
    mln.lot_number,
    mln.parent_lot_number,
    mln.description,
    mln.grade_code,
    mln.expiration_action_code,
    nvl(mln.disable_flag,'1'),
    mln.origination_type,
  mln.expiration_action_code,
  mln.creation_date,
    mln.expiration_date,
  mln.retest_date,
      round(sysdate - mln.creation_date)
having  round(sum(moq.transaction_quantity)) > 0
Order by 2 asc,15 asc

Current result:

    
asked by anonymous 13.06.2016 / 16:14

2 answers

5

Quick and dirty solution:

  • Reorder the positions of the intrinsic values of the LOT column (day / month / year) into a format that returns the correct order (year / month / day):

    ORDER BY SUBSTR(TO_CHAR(LOTE), 5, 4) || SUBSTR(TO_CHAR(LOTE), 3, 2) || SUBSTR(TO_CHAR(LOTE), 1, 2)

Slightly better solution:

  • Convert the numeric content of the LOT field to the YYYYMMDD format; refactor your application.

Appropriate solution:

  • Convert the numeric content of the LOT field to a DateTime ; refactor your application.
13.06.2016 / 16:22
2
order by to_date(mln.lot_number,'YYYYMMDD') , 15 ASC

But I would review the solution, better work with dates (type date ).

    
13.06.2016 / 16:25
Change active menu item depending on 'scroll' [closed] Error with default value for datetime field in MySQL