Optimize SQL, same line multiple times in CASE
<p>I've written a SQL Servre stored procedure (SQL Server 2014) that looks at a certain column in a certain table, if the result is null I want to take the value from an other column.</p>

<p>I've ended up with writing this line twice:</p>

<pre><code>(Select t_EC 
 from TTCIB 
 where rtrim(ltrim(t_item)) = rtrim(ltrim(t1.t_item)) 
   and t_comp = '50') 
</code></pre>

<p>Once to check if it is null.</p>

<p>And the second time to actually add the value to my result if it wasn't null.</p>

<p>Is there a way to optimize this? So that the system doesn't need to run this line twice? (it has ltrim function twice and rtrim function twice so it would be useful)</p>

<pre><code>SELECT DISTINCT TOP 15 
    (SELECT t_EC 
     FROM TTCIB 
     WHERE rtrim(ltrim(t_item)) = rtrim(ltrim(t1.t_item)) 
       AND t_comp = '18') as EC18,
    CASE 
       WHEN (SELECT t_EC FROM TTCIB 
             WHERE rtrim(ltrim(t_item)) = rtrim(ltrim(t1.t_item)) 
               AND t_comp = '50') IS NULL
          THEN (SELECT t_EC 
                FROM TTCIB 
                WHERE rtrim(ltrim(t_item)) = rtrim(ltrim(t1.t_item)) 
                  AND t_comp = '51')
          ELSE (SELECT t_EC 
                FROM TTCIB 
                WHERE rtrim(ltrim(t_item)) = rtrim(ltrim(t1.t_item)) 
                  AND t_comp = '50') 
    END AS EC50,
    RTRIM(t1.t_sern) AS SerialNumber, t1.t_item AS Item, 
    RTRIM(t1.t_desc) AS ItemDesc, t1.t_ofbp AS SoldToBP,    
    RTRIM(t2.t_nama) AS BPName, t1.t_cwte AS Warranty, 
    t1.t_dltm AS DeliveryTime, t_optm AS InstallationTime,   
    t_clst AS Cluster, ISNULL(t3.t_endt,'1970-01-01') AS EndDate,
    t4.t_csig as ItemSignalCode, t5.t_dsca as ItemSignalCodeDesc  
FROM 
    FnGetSerialNos(@SerialNumber, @MasterCompany) t1  
INNER JOIN 
    ttccom100900 t2 ON t1.t_ofbp = t2.t_bpid
LEFT OUTER JOIN 
    ttsctm120900 t3 ON t1.t_term = t3.t_term
LEFT OUTER JOIN 
    ttcibd001900 t4 ON ltrim(t1.t_item) = ltrim(t4.t_item)
LEFT OUTER JOIN 
    ttcmcs018900 t5 ON t4.t_csig = t5.t_csig
</code></pre>