Home Forums Programming General need help with CASE statement and table joins RE: need help with CASE statement and table joins

  • First, please write your code using the ANSI-92 style joins, not the ANSI-89 style joins. Putting your join criteria in the FROM clause and your filter criteria in the WHERE clause makes reading your code easier.

    Here is what I think you are attempting to accomplish. If not, you need to give us more detail as to what you are trying to accomplish:

    SELECT

    sa.accountno AS accountno,

    s.saledate AS SaleDate,

    s.saleprice AS saleprice,

    s.improvedflag AS improvedflag,

    s.grantor AS grantor,

    s.grantee AS grantee,

    s.receptionno AS recpno,

    d.deeddescription AS deed,

    CASE

    when s.saleprice = '0' AND s.improvedflag = '0' then 'N/A'

    when s.saleprice > '0' AND s.improvedflag = '0' then 'Vacant'

    when s.saleprice > '0' AND s.improvedflag = '1' then 'Improved'

    else NULL

    end ImprovedFlagDescription

    from

    tblsale s

    inner join tblsaleacct sa

    on (sa.receptionno = s.receptionno)

    inner join tlkpdeedtype d

    on (sa.receptionno = s.receptionno)

    where

    s.verend = '99999999999'

    and sa.verend = '99999999999'

    order by

    sa.accountno,

    s.saledate;