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.
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.
Şimdi aynı işlemi bir de COALESCE kullanarak yapalım.
SELECT BusinessEntityID, FirstName + COALESCE(' ' + MiddleName,'') + ' ' + LastName AS "Ad Soyad" FROM Person.Person
Bir sonraki yazıda görüşmek üzere..
faydalı bir makale,teşekkürler.
http://www.serkancamur.com
Cok guzel anlatim, teşekkür ederim
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