select case when

select sn,status,pn,des,so,sol,loc ,case when username is null then null else (select description from account where ACCOUNT_ID=username) end fabname from 

(

SELECT tt1.serial_number as sn,

    tt2.status as status,

    tt1.part_number as pn,

    tt5.description as des,

    tt4.saleOrder as so,

    tt4.SoLine as sol,

    tt6.location as loc,

    tt6.accountIdOrNull as accountIdOrNull,

    tt6.accountIdOrNull as username

FROM unit tt1,

    (

SELECT t1.unit_key,

    t3.value_1 as status

FROM unit t1,

    dc_unitdetails t3

WHERE t1.unit_key = t3.object_key

    AND t3.linktype = 'Tooling_Status') tt2,

    (

SELECT t1.unit_key,

    t3.value_1 as ownerDes,

    t3.value_2 as username

FROM unit t1,

    dc_unitdetails t3

WHERE t1.unit_key = t3.object_key

    AND t3.linktype = 'Tooling_Owner') tt3,

    (

SELECT t1.unit_key,

    t3.value_1 as saleOrder,

    t3.value_2 as SoLine

FROM unit t1,

    dc_unitdetails t3

WHERE t1.unit_key = t3.object_key

    AND t3.linktype = 'Tooling_Sales_Order') tt4,

    part tt5,    

        (

SELECT t1.unit_key,

    t3.value_1 as location ,

    t3.value_2 as accountIdOrNull

FROM unit t1,

    dc_unitdetails t3

WHERE t1.unit_key = t3.object_key

    AND t3.linktype = 'Tooling_Location') tt6

 

WHERE tt1.unit_key = tt2.unit_key

    AND tt1.unit_key = tt3.unit_key

    AND tt1.unit_key = tt4.unit_key

    AND tt1.part_number = tt5.part_number

    AND tt1.part_revision = tt5.part_revision

    And tt1.UNIT_KEY = tt6.unit_key

    AND tt3.username = 'gavinWen'

    AND (tt2.status='Shipped'

    OR tt2.status='In-Possession')

)