SQL NULL dönen sonuçların COALESCE ile kontrolü

IS NULL veya IS NOT NULL a alternatif olarak COALESCE kullanımı.

SELECT A.MACHINEID, A.MACHINECODE, A.STARTTIME,  CASE WHEN A.ENDTIME IS NULL THEN A.CANCELTIME 
ELSE A.ENDTIME END AS ENDTIME,
          A.JOBORDER, A.OPRNAME, A.FABRIC_WEIGHT, B.MATERIAL,
                 C.STEXT, A.PROGRAMNOLIST, D.USERNAME + ' ' + D.USERLASTNAME
,D.ACTIVITYDATE AS OPRNAME2 FROM   TELESKOP.OMUR.dbo.BADATA AS A  INNER JOIN PNTFSARJ AS B ON 
 A.JOBORDER = B.PROJECT INNER JOIN IASVAROPTIONX AS C ON  
SUBSTRING( B.VOPTIONS ,  10 ,   3 ) = C.OPTIONKEY AND ( C.VARIANTKEY = 'L' )  
                 LEFT OUTER JOIN VW_TELESKOP_KAZAN_GC_OPERATOR AS D WITH ( NOLOCK ) ON 
                  D.ACTIVITYDATE <= CASE WHEN  COALESCE( A.ENDTIME , 0)=0 THEN A.CANCELTIME 
ELSE A.ENDTIME  END 
AND D.ACTIVITYDATE2 >= CASE WHEN COALESCE(A.ENDTIME , 0)=0 THEN A.CANCELTIME ELSE A.ENDTIME END
AND A.MACHINEID = D.MACHINEID   WHERE (A.JOBORDER LIKE '12120706' ) 
AND (A.STARTTIME >= '12.10.2012 00:00:00' ) AND (A.STARTTIME <= '12.17.2012 15:34:00' ) 
AND (A.MACHINEID LIKE '%' ) AND (A.OPRNAME LIKE '%' ) 
GROUP BY A.MACHINEID, A.MACHINECODE, A.STARTTIME, A.ENDTIME, A.JOBORDER, A.OPRNAME
, A.FABRIC_WEIGHT, B.MATERIAL, C.STEXT, A.PROGRAMNOLIST, D.USERNAME, D.USERLASTNAME,A.CANCELTIME
,D.ACTIVITYDATE 
ORDER BY A.STARTTIME

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir