Search for records with Latest date using condition

Navigation
2

I have the following query: 

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 get the query to return the FIELDCODE with the latest PLANTATION_DATE if the column HARVEST_END DATE has no value, if the HARVEST_END_DATE column has register I get FIELDCODE with HARVEST_END_DATE newer as I do, any suggestion ??? because my query only brings me FIELDCODE with PLANTATION_DATE more recent.

    
asked by anonymous 01.12.2016 / 16:29

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'
    
14.12.2016 / 16:07
Delphi - Merge two tables from different databases Update table attribute, via Form? php $ PHP_SELF?