Search Records with Latest Date Using Condition

Asked

Viewed 50 times

2

I have the following appointment:

SELECT 
    fm.FIELDCODE, 
    fm.BLOCK_CODE, 
    bl.BLOCK_DESC, 
    cat.CATEGORY_CODE, 
    cat.CATEGORY_ABBREV, 
    fm.FIELD_AREA, 
    fm.PLANTATION_DATE, 
    fm.HARVEST_END_DATE 

FROM 
    FM_BLOCK bl, 
    FM_FIELD_CATEGORY cat, 
    FM_FIELDMASTER_DETAIL fm  
WHERE 
    fm.PLANTATION_DATE = (  
        SELECT MAX(fm.PLANTATION_DATE) 
            FROM FM_FIELDMASTER_DETAIL fm where fm.FIELDCODE = '102'
    )
    and fm.BLOCK_CODE = bl.BLOCK_CODE
    and fm.DPTCOD = bl.DPTCOD 
    and fm.CATEGORY_CODE = cat.CATEGORY_CODE 
    and fm.FIELDCODE = '102'

How do I consult to return the FIELDCODE with PLANTATION_DATE most recent case by column HARVEST_END DATE no value if the column HARVEST_END_DATE have record return me FIELDCODE with HARVEST_END_DATE more recent as I do, any suggestions??? because my consultation only brings me the FIELDCODE with PLANTATION_DATE most recent.

sql

  • PLANTATION_DATE or HARVEST_END DATE : @Kennel Rafael ?

  • Friend, which bank is the query? And you have how to simplify your doubt? Ta very confusing..

  • Have you looked at how the case when?

  • Yes ja, now it works with Code

1 answer

1

Coalesce can solve:

SELECT 
 fm.FIELDCODE, 
 fm.BLOCK_CODE, 
 bl.BLOCK_DESC, 
 cat.CATEGORY_CODE, 
 cat.CATEGORY_ABBREV, 
 fm.FIELD_AREA, 
 fm.PLANTATION_DATE, 
 fm.HARVEST_END_DATE 
FROM 
 FM_BLOCK bl, 
 FM_FIELD_CATEGORY cat, 
 FM_FIELDMASTER_DETAIL fm  
WHERE 
 fm.PLANTATION_DATE = (  
    SELECT MAX(coalesce(fm2.HARVEST_END_DATE, fm2.PLANTATION_DATE))
        FROM FM_FIELDMASTER_DETAIL fm2 where fm2.FIELDCODE = '102'
 )
 and fm.BLOCK_CODE = bl.BLOCK_CODE
 and fm.DPTCOD = bl.DPTCOD 
 and fm.CATEGORY_CODE = cat.CATEGORY_CODE 
 and fm.FIELDCODE = '102'

  • This also works perfectly, thank you very much for the answers, solved my problem

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.