Son Haberler
Anasayfa / Yazılım / SQL / ISNULL ve COALESCE Deyimleri

ISNULL ve COALESCE Deyimleri

Daha önceki yazılarımı takip etti iseniz bahsetmiştim. String verileri birbiri ile toplarken dikkat etmemiz gereken bir nokta vardı. Buna göre eğer bir alan içindeki veri NULL ise toplama işlemi sonucu geriye her ne olursa olsun NULL dönerdi. Yani diyelim ki iki alandan string verileri toplamak istiyorsunuz yani birleştirmek istiyorsunuz. Ad alanı bazı satırlarda dolu ama bazılarında NULL, dikkat boşluk karakteri demiyorum NULL diyorum ikisi farklı şeyler. Boşluk karakteri NULL değildir, bir ASCII karşılığı vardır. Neyse dediğim gibi ad alanı bazılarında dolu bazılarında NULL, ama soyadların hepsi dolu. Ve siz Ad ve Soyadı birleştirdiğiniz de normal şartlar altında ad içeriği NULL olanlar birleşim sonucu her ne kadar soyad içeriği dolu olsa da geriye NULL döner. Bunun örneklemesini yapmıştık.

Şimdi bu yazımda ISNULL ve COALESCE deyimleri ile içeriğinde NULL verilerin olabildiği bir alanda string verileri toplarken geriye NULL dönmesini nasıl önleriz, bunu öğreneceğiz.

ISNULL deyimi şöyle kullanılır, iki parametre alır ve bunların ikisini de kullanmak zorunludur, öncelikle kontrol etmek istediğimiz kolon adı sonra NULL olanların içeriğini değiştirmek istediğimiz veri..

COALESCE deyiminin çalışması ISNULL’dan biraz farklıdır. Herhangi bir sayıda parametre alabilir ve ilk NULL olmayan değeri döner. T-SQL programlama geliştiricilerin tercihi genellikle COALESCE deyimidir. Çünkü COALESCE deyimi ANSI standartlarını karşılar, ISNULL karşılamaz. (ANSI – American National Standart Institues ve yazılımın temelinde standartları belirlemiştir) Ayrıca COALESCE çokyönlüdür. Şimdi kullanımlarını örnekleyeyim : (AdventureWorks veritabanı üzerinde çalışıyorum)

SELECT BusinessEntityID, FirstName + ' ' +
ISNULL(MiddleName,'') + ' ' + LastName
AS "Ad Soyad" FROM Person.Person

Person.Person tablosundan BusinessEntityID alanını ve FirstName, MiddleName, Lastname alanlarını birleştirerek aldım. MiddleName alanı için ISNULL ile kontrol yaptım, eğer NULL veri varsa bunu boşlukla değiştir dedim ve daha sonra bu 3 alanı Ad Soyad kolonu altında göster dedim.

311204

Diğer kullanımı :

SELECT BusinessEntityID, FirstName +
ISNULL(' ' + MiddleName,'') + ' ' +
LastName AS "Ad Soyad" FROM Person.Person

Şimdi ikincisinin farkı ne? Bir üsttekinde eğer MiddleName alanı NULL ise FirstName’den hemen sonra ve LastName’den hemen önce boşluk verdirdim. Bu şuna sebep oluyor, eğer MiddleName alanı NULL ise FirstName ve LastName arasında iki boşluk oluyor. Bunu önlemek için bu sefer ilk boşluğu ISNULL içine aldım. Böylelikle eğer MiddleName içeriği NULL olursa ilk boşluk karakteri elenecek ve ekstra bir boşluk olmayacaktır.

Üstteki sonuç resmine ve alttaki sonuç resmine özellikle 3. satırlara dikkat edin. Farkı göreceksiniz.

311205

Şimdi aynı işlemi bir de COALESCE kullanarak yapalım.

SELECT BusinessEntityID, FirstName + 
COALESCE(' ' + MiddleName,'') + ' ' +
LastName AS "Ad Soyad" 
FROM Person.Person

311205

Bir sonraki yazıda görüşmek üzere..

3 yorum

  1. Cok guzel anlatim, teşekkür ederim

  2. Adnan Fırtınaz

    Konu : “Divide by zero error encountered”

    Alınan yer : Mikro V15 – Menü sorgu yönetimi

    Sorguda yapılan uygulamalar : NULIF KULLANIMI YAPILDI. SORUN ÇÖZÜLMEDİ

    İstenen : Sorgudaki hesaplamadan doğan sıfırları, sıfır kabul etsin.

    Sıfır sonuç getirsin.

    Bunu aşağıdaki sorguda ki hesap alanlarına nasıl eklerim.

    SORGU:

    DECLARE @Proje_Kodu VARCHAR(100)

    SET @Proje_Kodu = @P1;

    SELECT s.sth_stok_kod AS kod, SUM(ISNULL(NULLIF(s.sth_miktar,0),0)) AS miktar

    INTO #tmpTableProjeDeposunaGidenler

    FROM dbo.STOK_HAREKETLERI s

    WHERE s.sth_tip IN(0, 2)

    –AND s.sth_proje_kodu = @Proje_Kodu

    AND s.sth_giris_depo_no IN

    (SELECT d.dep_no

    FROM dbo.depolar d

    WHERE d.dep_proje_kodu = @Proje_Kodu)

    GROUP BY s.sth_stok_kod

    SELECT s.sth_stok_kod AS kod, SUM(ISNULL(NULLIF(s.sth_miktar,0),0)) AS miktar

    INTO #tmpTableProjeDeposuHaricindeGidenler

    FROM dbo.STOK_HAREKETLERI s

    WHERE s.sth_tip IN(2)

    AND s.sth_proje_kodu = @Proje_Kodu

    AND s.sth_evrakno_seri IN (‘C’, ‘M’, ‘C ‘)

    AND s.sth_giris_depo_no NOT IN

    (SELECT d.dep_no

    FROM dbo.depolar d

    WHERE d.dep_proje_kodu = @Proje_Kodu)

    GROUP BY s.sth_stok_kod

    SELECT s.sth_stok_kod AS kod, SUM(ISNULL(NULLIF(s.sth_miktar,0),0)) AS miktar

    INTO #tmpTableIrsaliyeIleGidenler

    FROM dbo.STOK_HAREKETLERI s

    WHERE s.sth_tip IN(1)

    AND s.sth_proje_kodu = @Proje_Kodu

    AND s.sth_evrakno_seri IN (‘C’, ‘M’, ‘C ‘)

    AND sth_evraktip = 1

    GROUP BY s.sth_stok_kod

    SELECT f.kod [Stok Kodu], dbo.fn_StokIsmi(f.kod) [Stok], ISNULL(SUM(NULLIF(F.miktar,0)), 0) AS [Gerekleen Sevk Miktar]

    INTO #tmpTableStokSevkEdilenMiktarlari

    FROM

    (

    SELECT DISTINCT *

    FROM #tmpTableProjeDeposunaGidenler

    UNION

    SELECT *

    FROM #tmpTableProjeDeposuHaricindeGidenler

    UNION

    SELECT *

    FROM #tmpTableIrsaliyeIleGidenler

    ) f

    GROUP BY f.kod

    ORDER BY f.kod

    SELECT DISTINCT f.[Stok Kodu], dbo.fn_StokIsmi(f.[Stok Kodu]) [Stok], ISNULL(SUM(NULLIF(F.[Planlanan Sevk Miktar],0)), 0) AS [Planlanan Sevk Miktar]

    INTO #tmpTableStokSevkEdilecekMiktarlari

    FROM

    (

    SELECT DISTINCT

    ssip_stok_kod AS [Stok Kodu],

    ISNULL(NULLIF(ssip_miktar,0),0)AS [Planlanan Sevk Miktar]

    FROM dbo.DEPOLAR_ARASI_SIPARISLER d

    WHERE d.ssip_projekodu = @Proje_Kodu

    UNION

    SELECT DISTINCT

    s.sip_stok_kod AS [Stok Kodu],

    ISNULL(NULLIF(sip_miktar,0),0)AS [Planlanan Sevk Miktar]

    FROM dbo.SIPARISLER s

    WHERE (s.sip_evrakno_seri = ‘SEVK’ OR(s.sip_evrakno_seri != ‘SEVK’ AND LEFT(sip_stok_kod, 2) NOT IN(’08’, ’17’, ’15’, ’16’, ’06’))) AND s.sip_projekodu = @Proje_Kodu

    AND s.sip_tip = 0

    ) f

    GROUP BY [Stok Kodu]

    ;WITH cte AS(

    SELECT

    COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) [Stok Kodu],

    COALESCE(x.Stok, Y.Stok) [Stok],

    ISNULL((

    SELECT SUM(ISNULL(NULLIF(S2.stl_miktari,0),0))

    FROM dbo.SATINALMA_TALEPLERI AS s2

    –LEFT OUTER JOIN dbo.SIPARISLER AS s3 ON s2.stl_RECno = s3.sip_stalRecId_RECno

    WHERE s2.stl_Stok_kodu = COALESCE(x.[Stok Kodu], Y.[Stok Kodu])

    AND s2.stl_OnaylayanKulNo > 0 — onaylayan kullanc var olan

    AND stl_projekodu = @Proje_Kodu

    ), 0) AS [Satn Alma Talep Miktar],

    ISNULL((

    SELECT SUM(ISNULL(NULLIF(sip_miktar,0),0))

    FROM dbo.SIPARISLER AS s

    WHERE s.sip_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu])

    AND s.sip_tip= 1

    AND s.sip_projekodu = @Proje_Kodu

    AND s.sip_OnaylayanKulNo > 0

    ), 0) AS [Sipari Miktar],

    ISNULL((

    SELECT SUM(ISNULL(NULLIF(s.sth_miktar,0),0))

    FROM dbo.STOK_HAREKETLERI AS s

    WHERE s.sth_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu])

    AND S.sth_evraktip = 13

    AND s.sth_proje_kodu = @Proje_Kodu

    ), 0) AS [Gelen Sipari Miktar],

    ISNULL(x.[Planlanan Sevk Miktar], 0) [Planlanan Sevk Miktar],

    ISNULL(y.[Gerekleen Sevk Miktar], 0) [Gerekleen Sevk Miktar],

    COALESCE

    (

    (

    SELECT SUM((CASE WHEN x2.sth_har_doviz_cinsi != 0 THEN (x2.sth_tutar – x2.sth_iskonto1) * x2.sth_har_doviz_kuru ELSE (x2.sth_tutar – x2.sth_iskonto1) END)) /

    SUM(ISNULL(NULLIF(x2.sth_miktar,0),0))

    FROM dbo.STOK_HAREKETLERI x2

    WHERE x2.sth_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) AND x2.sth_evraktip = 3 AND x2.sth_cins != 9 AND x2.sth_proje_kodu = @Proje_Kodu

    ),

    (

    SELECT SUM((CASE WHEN x2.sth_har_doviz_cinsi != 0 THEN (x2.sth_tutar – x2.sth_iskonto1) * x2.sth_har_doviz_kuru ELSE (x2.sth_tutar – x2.sth_iskonto1) END)) /

    SUM(ISNULL(NULLIF(x2.sth_miktar,0),0))

    FROM dbo.STOK_HAREKETLERI x2

    WHERE x2.sth_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) AND x2.sth_evraktip = 13 AND x2.sth_proje_kodu = @Proje_Kodu

    ),

    (

    select top 1 (dbo.fn_KurBul(sas_evrak_tarih, sas_doviz_cinsi, 2) * sas_satis_fiyat)

    sas_evrak_tarih

    from SATINALMA_SARTLARI

    where

    (sas_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) and (sas_depo_no=0 or sas_depo_no=0 or sas_depo_no is null) and

    sas_basla_tarih=getdate() or sas_bitis_tarih<='19101231'))

    order by sas_evrtipi desc, sas_depo_no desc, sas_basla_tarih desc, sas_create_date desc, sas_bitis_tarih desc

    ),

    (

    select top 1

    s2.sth_tutar / ISNULL(NULLIF(sth_miktar,0),0)

    from dbo.STOK_HAREKETLERI as s2

    where s2.sth_cins = 11

    and s2.sth_evraktip = 12

    and s2.sth_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu])

    )

    ) [Birim Deeri],

    COALESCE

    (

    (

    SELECT SUM((CASE WHEN x2.sth_har_doviz_cinsi != 2 THEN (x2.sth_tutar – x2.sth_iskonto1) / ISNULL(NULLIF(x2.sth_alt_doviz_kuru,0),0) ELSE (x2.sth_tutar – x2.sth_iskonto1) END)) /

    SUM(ISNULL(NULLIF(x2.sth_miktar,0),0))

    FROM dbo.STOK_HAREKETLERI x2

    WHERE x2.sth_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) AND x2.sth_evraktip = 3 AND x2.sth_cins != 9 AND x2.sth_proje_kodu = @Proje_Kodu

    ),

    (

    SELECT SUM((CASE WHEN x2.sth_har_doviz_cinsi != 2 THEN (x2.sth_tutar – x2.sth_iskonto1) / ISNULL(NULLIF(x2.sth_alt_doviz_kuru,0),0) ELSE (x2.sth_tutar – x2.sth_iskonto1) END)) /

    SUM(ISNULL(NULLIF(x2.sth_miktar,0),0))

    FROM dbo.STOK_HAREKETLERI x2

    WHERE x2.sth_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) AND x2.sth_evraktip = 13 AND x2.sth_proje_kodu = @Proje_Kodu

    ),

    (

    select top 1 (dbo.fn_KurBul(sas_evrak_tarih, sas_doviz_cinsi, 2) * sas_satis_fiyat) / ISNULL(NULLIF(dbo.fn_KurBul(sas_evrak_tarih, 2, 2),0),0)

    from SATINALMA_SARTLARI

    where

    (sas_stok_kod = COALESCE(x.[Stok Kodu], Y.[Stok Kodu]) and (sas_depo_no=0 or sas_depo_no=0 or sas_depo_no is null) and

    sas_basla_tarih=getdate() or sas_bitis_tarih 0 THEN [Birim Deeri] / ISNULL(NULLIF([E Birim Deeri],0),0) ELSE 0 END Kur,

    ISNULL([E Birim Deeri], 0) [Euro Birim Deeri],

    ISNULL([E Birim Deeri], 0) * [Planlanan Sevk Miktar] [Euro Planlanan Sevk Deeri],

    ISNULL([E Birim Deeri], 0) * [Gerekleen Sevk Miktar] [Euro Toplam Sevk Deeri]

    FROM cte c

    INNER JOIN dbo.STOKLAR AS s ON c.[Stok Kodu] = s.sto_kod

    LEFT OUTER JOIN dbo.STOK_ANA_GRUPLARI AS s2 ON s.sto_anagrup_kod = s2.san_kod

    LEFT OUTER JOIN dbo.STOK_ALT_GRUPLARI AS s3 ON s.sto_altgrup_kod = s3.sta_kod

    DROP TABLE #tmpTableProjeDeposunaGidenler

    DROP TABLE #tmpTableProjeDeposuHaricindeGidenler

    DROP TABLE #tmpTableIrsaliyeIleGidenler

    DROP TABLE #tmpTableStokSevkEdilenMiktarlari

    DROP TABLE #tmpTableStokSevkEdilecekMiktarlari

    bu hatayı mikrodaki menü sorgu ekranında alıyorum.

    sql managment da hata almıyorum

    bazı proje kodlu kayıtlar için alıyorum.

    bu hatayı her peoje kodunda almıyorum.

    sorgumu düzenlememde bana yardım edebilecek biri var mı acaba

Bir cevap yazın

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