August 21, 2014 at 5:04 am
Hi
Arithmetic overflow error converting expression to data type datetime.
Create View
CREATE VIEW [dbo].[F0102D0001VKREDIBORCLUTARAMARAPORU] AS SELECT 0 AS SIRA , TBLCARI.IND, (SELECT SUM(BORC-ALACAK) FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR WHERE CARHAR.FIRMANO=TBLCARI.IND ) AS BAKIYE, TCG.ISLEMIND, TBLCARI.SOYADI, TBLCARI.ADI, TBLCARI.SOYADI+' '+TBLCARI.ADI AS SOYADAD, TBLCARI.FIRMAKODU, TBLCARI.FIRMAADI, TBLCARI.FIRMATIPI, TBLCARI.GECIKMEFAIZI, TBLCARI.KOD1, TBLCARI.KOD2, TBLCARI.KOD3, TBLCARI.KOD4, TBLCARI.KOD5, TBLCARI.TELEFON1, TBLCARI.TELEFON2, TBLCARI.TELEFON3, TBLCARI.TELEFON3 AS GSMNO, TBLCARI.KEFIL1, TBLCARI.KEFIL2, TBLCARI.OPSIYON, TBLCARI.SATISYAPILMASIN, CEILING(CAST((GETDATE()- TCG.VADE) AS NUMERIC)/30) AS GECIKMEAY, CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT, CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA, CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA, CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK, CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1, CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2, TBLCARI.RISKLIMITI, TBLCARI.KREDILIMITI, TCG.ISLEMIZAHAT, TCG.BELGEIZAHAT, TCG.PARABIRIMI, TCG.VADE, BASLIK.OZELKOD1, BASLIK.OZELKOD2, BASLIK.OZELKOD3, BASLIK.OZELKOD4, CASE WHEN (TCG.BELGEIZAHAT=12) THEN CARCIKIADE.STATUS ELSE CARGIR.STATUS END AS STATUS, (SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC-ALACAK)/(CASE WHEN ISNULL(KUR,1)=0 THEN 1 ELSE KUR END))),0)) FROM F0102D0001TBLCARIGENELHAREKET WHERE FIRMANO=TCG.FIRMANO AND ISLEMIZAHAT=TCG.ISLEMIZAHAT AND BELGEIZAHAT=TCG.BELGEIZAHAT AND IND=TCG.IND) AS BORC, abs(ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC-TCG2.ALACAK)/(CASE WHEN ISNULL(TCG.KUR,1)=0 THEN 1 ELSE TCG.KUR END)))),0)) AS TAHSILAT, (SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC-ALACAK)/KUR)),0)) FROM F0102D0001TBLCARIGENELHAREKET WHERE FIRMANO=TCG.FIRMANO AND ISLEMIZAHAT=TCG.ISLEMIZAHAT AND BELGEIZAHAT=TCG.BELGEIZAHAT AND IND=TCG.IND)- ISNULL(ABS(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC-TCG2.ALACAK)/(CASE WHEN ISNULL(TCG.KUR,1)=0 THEN 1 ELSE TCG.KUR END))))),0) AS KALAN, CAST(GETDATE()-1 AS INT)-CAST(TCG.VADE AS INT) AS GUN, (abs(ISNULL(DBO.ROUNDYTL((SUM((TCG.BORC-TCG.ALACAK)/(CASE WHEN ISNULL(TCG.KUR,1)=0 THEN 1 ELSE TCG.KUR END)))),0))- abs(ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC-TCG2.ALACAK)/(CASE WHEN ISNULL(TCG.KUR,1)=0 THEN 1 ELSE TCG.KUR END)))),0)))* (ROUND(CAST(GETDATE()-((CAST(TCG.VADE AS INT)+1)) AS FLOAT),0)) AS GECIKME, ROUND((((ABS(ISNULL((SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC-ALACAK)/(CASE WHEN ISNULL(KUR,1)=0 THEN 1 ELSE KUR END))),0)) FROM F0102D0001TBLCARIGENELHAREKET WHERE FIRMANO=TCG.FIRMANO AND ISLEMIZAHAT=TCG.ISLEMIZAHAT AND BELGEIZAHAT=TCG.BELGEIZAHAT AND IND=TCG.IND),0)- (ISNULL(ABS(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC-TCG2.ALACAK)/(CASE WHEN ISNULL(TCG.KUR,1)=0 THEN 1 ELSE TCG.KUR END))))),0)))) *(((ROUND(CAST(GETDATE()-((CAST(TCG.VADE AS INT)+1)) AS FLOAT),0)*TBLCARI.GECIKMEFAIZI))/3000))),0) AS VADEFARKI FROM F0102D0001TBLCARIGENELHAREKET AS TCG LEFT JOIN F0102D0001TBLCARIGENELHAREKET AS TCG2 ON TCG2.TAHSILLINK = TCG.IND LEFT OUTER JOIN F0102D0001TBLCARGIRHAREKET AS CARGIR ON CARGIR.IND = TCG.ISLEMIND AND CARGIR.FIRMANO=TCG.FIRMANO AND CARGIR.EVRAKNO=TCG.BELGEIND AND CARGIR.STATUS NOT IN (2,9,48,49) LEFT JOIN F0102TBLCARI AS TBLCARI ON TBLCARI.IND=TCG.FIRMANO LEFT OUTER JOIN F0102D0001TBLCARCIKIADEHAREKET AS CARCIKIADE ON CARCIKIADE.IND = TCG.ISLEMIND AND CARCIKIADE.IZAHAT=TCG.ISLEMIZAHAT LEFT JOIN F0102D0001TBLBANKAGENELHAREKET AS BH ON BH.BELGELINK=TCG.BELGELINK AND BH.BELGEIZAHAT=74 AND BH.ISLEMIZAHAT=TCG.ISLEMIZAHAT AND BH.ISLEMIZAHAT IN (2,3) LEFT JOIN F0102D0001VBASLIKLAR AS BASLIK ON TCG.BELGEIND=BASLIK.IND AND TCG.FIRMANO=BASLIK.FIRMANO AND TCG.BELGEIZAHAT=BASLIK.BELGETIPI WHERE TCG.BELGEIZAHAT IN (12,13,18,19,119) AND TCG.ISLEMIZAHAT NOT IN (1,4,10,11) AND CARGIR.STATUS NOT IN (2,9,48,49) AND TCG.FIRMANO=TBLCARI.IND AND TBLCARI.IND>=100 GROUP BY TBLCARI.IND, TBLCARI.SOYADI, TBLCARI.ADI, TBLCARI.OPSIYON, TBLCARI.FIRMAKODU, TBLCARI.FIRMAADI, TBLCARI.FIRMATIPI, TBLCARI.GECIKMEFAIZI, TBLCARI.KOD1, TBLCARI.KOD2, TBLCARI.KOD3, TBLCARI.KOD4, TBLCARI.KOD5, TBLCARI.TELEFON1, TBLCARI.TELEFON2, TBLCARI.TELEFON3, TBLCARI.KEFIL1, TBLCARI.KEFIL2, CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)), CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)), CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)), CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)), CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)), CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)), TBLCARI.RISKLIMITI, TBLCARI.KREDILIMITI, TCG.ISLEMIZAHAT, TCG.BELGEIZAHAT, TCG.FIRMANO, TCG.PARABIRIMI, TBLCARI.SATISYAPILMASIN, TCG.VADE, BASLIK.OZELKOD1, BASLIK.OZELKOD2, BASLIK.OZELKOD3, BASLIK.OZELKOD4, CARGIR.TUTAR, TCG.KUR, CARGIR.STATUS,TCG.BELGEIZAHAT,TCG.IND,CARCIKIADE.STATUS,TCG.ISLEMIND UNION SELECT 1 AS SIRA , TBLCARI.IND, (SELECT SUM(BORC-ALACAK) FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR WHERE CARHAR.FIRMANO=TBLCARI.IND ) AS BAKIYE, WS.IND AS ISLEMIND, TBLCARI.SOYADI, TBLCARI.ADI, TBLCARI.SOYADI+' '+ TBLCARI.ADI AS SOYADAD, TBLCARI.FIRMAKODU, TBLCARI.FIRMAADI, TBLCARI.FIRMATIPI, TBLCARI.GECIKMEFAIZI, TBLCARI.KOD1, TBLCARI.KOD2, TBLCARI.KOD3, TBLCARI.KOD4, TBLCARI.KOD5, TBLCARI.TELEFON1, TBLCARI.TELEFON2, TBLCARI.TELEFON3, TBLCARI.TELEFON3 AS GSMNO, TBLCARI.KEFIL1, TBLCARI.KEFIL2, TBLCARI.OPSIYON, TBLCARI.SATISYAPILMASIN, CEILING(CAST((GETDATE()- TARIH) AS NUMERIC)/30) AS GECIKMEAY, CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT, CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA, CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA, CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK, CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1, CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2, TBLCARI.RISKLIMITI, TBLCARI.KREDILIMITI, WS.IZAHAT AS ISLEMIZAHAT, 0 AS BELGEIZAHAT, 'TL' AS PARABIRIMI, WS.TARIH AS VADE, '' AS OZELKOD1, '' AS OZELKOD2, '' AS OZELKOD3, '' AS OZELKOD4, 0 AS STATUS, WS.TUTAR AS BORC, 0.0 AS TAHSILAT, WS.TUTAR AS KALAN, CAST(GETDATE()-1 AS INT)-CAST(TARIH AS INT) AS GUN, ROUND(CAST(GETDATE()-((CAST(TARIH AS INT)+1)) AS FLOAT),0)* WS.TUTAR AS GECIKME, (((ROUND(CAST(GETDATE()-((CAST(TARIH AS INT)+1)) AS FLOAT),0)* WS.TUTAR)*TBLCARI.GECIKMEFAIZI)/3000) AS VADEFARKI FROM F0102D0001TBLWSTAKSITLISATIS AS WS LEFT JOIN F0102TBLCARI AS TBLCARI ON TBLCARI.IND = WS.MUSTERINO WHERE IZAHAT= 100
GO
Desing code
SELECT 0 AS SIRA, TBLCARI.IND,
(SELECT SUM(BORC - ALACAK)
FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR
WHERE CARHAR.FIRMANO = TBLCARI.IND) AS BAKIYE, TCG.ISLEMIND, TBLCARI.SOYADI, TBLCARI.ADI,
TBLCARI.SOYADI + ' ' + TBLCARI.ADI AS SOYADAD, TBLCARI.FIRMAKODU, TBLCARI.FIRMAADI, TBLCARI.FIRMATIPI, TBLCARI.GECIKMEFAIZI,
TBLCARI.KOD1, TBLCARI.KOD2, TBLCARI.KOD3, TBLCARI.KOD4, TBLCARI.KOD5, TBLCARI.TELEFON1, TBLCARI.TELEFON2, TBLCARI.TELEFON3,
TBLCARI.TELEFON3 AS GSMNO, TBLCARI.KEFIL1, TBLCARI.KEFIL2, TBLCARI.OPSIYON, TBLCARI.SATISYAPILMASIN, CEILING(CAST((GETDATE()
- TCG.VADE) AS NUMERIC) / 30) AS GECIKMEAY, CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT,
CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA, CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA,
CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK, CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1,
CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2, TBLCARI.RISKLIMITI, TBLCARI.KREDILIMITI, TCG.ISLEMIZAHAT,
TCG.BELGEIZAHAT, TCG.PARABIRIMI, TCG.VADE, BASLIK.OZELKOD1, BASLIK.OZELKOD2, BASLIK.OZELKOD3, BASLIK.OZELKOD4,
CASE WHEN (TCG.BELGEIZAHAT = 12) THEN CARCIKIADE.STATUS ELSE CARGIR.STATUS END AS STATUS,
(SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC - ALACAK) / (CASE WHEN ISNULL(KUR, 1) = 0 THEN 1 ELSE KUR END))), 0))
FROM F0102D0001TBLCARIGENELHAREKET
WHERE FIRMANO = TCG.FIRMANO AND ISLEMIZAHAT = TCG.ISLEMIZAHAT AND BELGEIZAHAT = TCG.BELGEIZAHAT AND IND = TCG.IND)
AS BORC, abs(ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (CASE WHEN ISNULL(TCG.KUR, 1) = 0 THEN 1 ELSE TCG.KUR END)))),
0)) AS TAHSILAT,
(SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC - ALACAK) / KUR)), 0))
FROM F0102D0001TBLCARIGENELHAREKET
WHERE FIRMANO = TCG.FIRMANO AND ISLEMIZAHAT = TCG.ISLEMIZAHAT AND BELGEIZAHAT = TCG.BELGEIZAHAT AND IND = TCG.IND)
- ISNULL(ABS(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (CASE WHEN ISNULL(TCG.KUR, 1) = 0 THEN 1 ELSE TCG.KUR END))))), 0)
AS KALAN, CAST(GETDATE() - 1 AS INT) - CAST(TCG.VADE AS INT) AS GUN, (abs(ISNULL(DBO.ROUNDYTL((SUM((TCG.BORC - TCG.ALACAK)
/ (CASE WHEN ISNULL(TCG.KUR, 1) = 0 THEN 1 ELSE TCG.KUR END)))), 0)) - abs(ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK)
/ (CASE WHEN ISNULL(TCG.KUR, 1) = 0 THEN 1 ELSE TCG.KUR END)))), 0))) * (ROUND(CAST(GETDATE() - ((CAST(TCG.VADE AS INT) + 1)) AS FLOAT), 0))
AS GECIKME, ROUND((((ABS(ISNULL
((SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC - ALACAK) / (CASE WHEN ISNULL(KUR, 1) = 0 THEN 1 ELSE KUR END))), 0))
FROM F0102D0001TBLCARIGENELHAREKET
WHERE FIRMANO = TCG.FIRMANO AND ISLEMIZAHAT = TCG.ISLEMIZAHAT AND BELGEIZAHAT = TCG.BELGEIZAHAT AND IND = TCG.IND), 0)
- (ISNULL(ABS(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (CASE WHEN ISNULL(TCG.KUR, 1) = 0 THEN 1 ELSE TCG.KUR END))))), 0))))
* (((ROUND(CAST(GETDATE() - ((CAST(TCG.VADE AS INT) + 1)) AS FLOAT), 0) * TBLCARI.GECIKMEFAIZI)) / 3000))), 0) AS VADEFARKI
FROM F0102D0001TBLCARIGENELHAREKET AS TCG LEFT JOIN
F0102D0001TBLCARIGENELHAREKET AS TCG2 ON TCG2.TAHSILLINK = TCG.IND LEFT OUTER JOIN
F0102D0001TBLCARGIRHAREKET AS CARGIR ON CARGIR.IND = TCG.ISLEMIND AND CARGIR.FIRMANO = TCG.FIRMANO AND
CARGIR.EVRAKNO = TCG.BELGEIND AND CARGIR.STATUS NOT IN (2, 9, 48, 49) LEFT JOIN
F0102TBLCARI AS TBLCARI ON TBLCARI.IND = TCG.FIRMANO LEFT OUTER JOIN
F0102D0001TBLCARCIKIADEHAREKET AS CARCIKIADE ON CARCIKIADE.IND = TCG.ISLEMIND AND CARCIKIADE.IZAHAT = TCG.ISLEMIZAHAT LEFT JOIN
F0102D0001TBLBANKAGENELHAREKET AS BH ON BH.BELGELINK = TCG.BELGELINK AND BH.BELGEIZAHAT = 74 AND
BH.ISLEMIZAHAT = TCG.ISLEMIZAHAT AND BH.ISLEMIZAHAT IN (2, 3) LEFT JOIN
F0102D0001VBASLIKLAR AS BASLIK ON TCG.BELGEIND = BASLIK.IND AND TCG.FIRMANO = BASLIK.FIRMANO AND
TCG.BELGEIZAHAT = BASLIK.BELGETIPI
WHERE TCG.BELGEIZAHAT IN (12, 13, 18, 19, 119) AND TCG.ISLEMIZAHAT NOT IN (1, 4, 10, 11) AND CARGIR.STATUS NOT IN (2, 9, 48, 49) AND
TCG.FIRMANO = TBLCARI.IND AND TBLCARI.IND >= 100
GROUP BY TBLCARI.IND, TBLCARI.SOYADI, TBLCARI.ADI, TBLCARI.OPSIYON, TBLCARI.FIRMAKODU, TBLCARI.FIRMAADI, TBLCARI.FIRMATIPI,
TBLCARI.GECIKMEFAIZI, TBLCARI.KOD1, TBLCARI.KOD2, TBLCARI.KOD3, TBLCARI.KOD4, TBLCARI.KOD5, TBLCARI.TELEFON1, TBLCARI.TELEFON2,
TBLCARI.TELEFON3, TBLCARI.KEFIL1, TBLCARI.KEFIL2, CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)),
CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)), CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)),
CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)), CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)),
CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)), TBLCARI.RISKLIMITI, TBLCARI.KREDILIMITI, TCG.ISLEMIZAHAT, TCG.BELGEIZAHAT,
TCG.FIRMANO, TCG.PARABIRIMI, TBLCARI.SATISYAPILMASIN, TCG.VADE, BASLIK.OZELKOD1, BASLIK.OZELKOD2, BASLIK.OZELKOD3,
BASLIK.OZELKOD4, CARGIR.TUTAR, TCG.KUR, CARGIR.STATUS, TCG.BELGEIZAHAT, TCG.IND, CARCIKIADE.STATUS, TCG.ISLEMIND
UNION
SELECT 1 AS SIRA, TBLCARI.IND,
(SELECT SUM(BORC - ALACAK)
FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR
WHERE CARHAR.FIRMANO = TBLCARI.IND) AS BAKIYE, WS.IND AS ISLEMIND, TBLCARI.SOYADI, TBLCARI.ADI,
TBLCARI.SOYADI + ' ' + TBLCARI.ADI AS SOYADAD, TBLCARI.FIRMAKODU, TBLCARI.FIRMAADI, TBLCARI.FIRMATIPI, TBLCARI.GECIKMEFAIZI,
TBLCARI.KOD1, TBLCARI.KOD2, TBLCARI.KOD3, TBLCARI.KOD4, TBLCARI.KOD5, TBLCARI.TELEFON1, TBLCARI.TELEFON2, TBLCARI.TELEFON3,
TBLCARI.TELEFON3 AS GSMNO, TBLCARI.KEFIL1, TBLCARI.KEFIL2, TBLCARI.OPSIYON, TBLCARI.SATISYAPILMASIN, CEILING(CAST((GETDATE()
- TARIH) AS NUMERIC) / 30) AS GECIKMEAY, CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT,
CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA, CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA,
CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK, CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1,
CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2, TBLCARI.RISKLIMITI, TBLCARI.KREDILIMITI, WS.IZAHAT AS ISLEMIZAHAT,
0 AS BELGEIZAHAT, 'TL' AS PARABIRIMI, WS.TARIH AS VADE, '' AS OZELKOD1, '' AS OZELKOD2, '' AS OZELKOD3, '' AS OZELKOD4, 0 AS STATUS,
WS.TUTAR AS BORC, 0.0 AS TAHSILAT, WS.TUTAR AS KALAN, CAST(GETDATE() - 1 AS INT) - CAST(TARIH AS INT) AS GUN, ROUND(CAST(GETDATE()
- ((CAST(TARIH AS INT) + 1)) AS FLOAT), 0) * WS.TUTAR AS GECIKME, (((ROUND(CAST(GETDATE() - ((CAST(TARIH AS INT) + 1)) AS FLOAT), 0)
* WS.TUTAR) * TBLCARI.GECIKMEFAIZI) / 3000) AS VADEFARKI
FROM F0102D0001TBLWSTAKSITLISATIS AS WS LEFT JOIN
F0102TBLCARI AS TBLCARI ON TBLCARI.IND = WS.MUSTERINO
WHERE IZAHAT = 100
August 21, 2014 at 7:29 am
Hi and welcome to the forums. It is nearly impossible for us to debug this for you but we can help guide you in figuring out the problem.
First, let's format this view definition so it is more legible.
SELECT 0 AS SIRA
,TBLCARI.IND
,(
SELECT SUM(BORC - ALACAK)
FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR
WHERE CARHAR.FIRMANO = TBLCARI.IND
) AS BAKIYE
,TCG.ISLEMIND
,TBLCARI.SOYADI
,TBLCARI.ADI
,TBLCARI.SOYADI + ' ' + TBLCARI.ADI AS SOYADAD
,TBLCARI.FIRMAKODU
,TBLCARI.FIRMAADI
,TBLCARI.FIRMATIPI
,TBLCARI.GECIKMEFAIZI
,TBLCARI.KOD1
,TBLCARI.KOD2
,TBLCARI.KOD3
,TBLCARI.KOD4
,TBLCARI.KOD5
,TBLCARI.TELEFON1
,TBLCARI.TELEFON2
,TBLCARI.TELEFON3
,TBLCARI.TELEFON3 AS GSMNO
,TBLCARI.KEFIL1
,TBLCARI.KEFIL2
,TBLCARI.OPSIYON
,TBLCARI.SATISYAPILMASIN
,CEILING(CAST((GETDATE() - TCG.VADE) AS NUMERIC) / 30) AS GECIKMEAY
,CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT
,CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA
,CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA
,CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK
,CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1
,CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2
,TBLCARI.RISKLIMITI
,TBLCARI.KREDILIMITI
,TCG.ISLEMIZAHAT
,TCG.BELGEIZAHAT
,TCG.PARABIRIMI
,TCG.VADE
,BASLIK.OZELKOD1
,BASLIK.OZELKOD2
,BASLIK.OZELKOD3
,BASLIK.OZELKOD4
,CASE
WHEN (TCG.BELGEIZAHAT = 12)
THEN CARCIKIADE.STATUS
ELSE CARGIR.STATUS
END AS STATUS
,(
SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC - ALACAK) / (
CASE
WHEN ISNULL(KUR, 1) = 0
THEN 1
ELSE KUR
END
))), 0))
FROM F0102D0001TBLCARIGENELHAREKET
WHERE FIRMANO = TCG.FIRMANO
AND ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BELGEIZAHAT = TCG.BELGEIZAHAT
AND IND = TCG.IND
) AS BORC
,abs(ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (
CASE
WHEN ISNULL(TCG.KUR, 1) = 0
THEN 1
ELSE TCG.KUR
END
)))), 0)) AS TAHSILAT
,(
SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC - ALACAK) / KUR)), 0))
FROM F0102D0001TBLCARIGENELHAREKET
WHERE FIRMANO = TCG.FIRMANO
AND ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BELGEIZAHAT = TCG.BELGEIZAHAT
AND IND = TCG.IND
) - ISNULL(ABS(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (
CASE
WHEN ISNULL(TCG.KUR, 1) = 0
THEN 1
ELSE TCG.KUR
END
))))), 0) AS KALAN
,CAST(GETDATE() - 1 AS INT) - CAST(TCG.VADE AS INT) AS GUN
,(
abs(ISNULL(DBO.ROUNDYTL((
SUM((TCG.BORC - TCG.ALACAK) / (
CASE
WHEN ISNULL(TCG.KUR, 1) = 0
THEN 1
ELSE TCG.KUR
END
))
)), 0)) - abs(ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (
CASE
WHEN ISNULL(TCG.KUR, 1) = 0
THEN 1
ELSE TCG.KUR
END
)))), 0))
) * (ROUND(CAST(GETDATE() - ((CAST(TCG.VADE AS INT) + 1)) AS FLOAT), 0)) AS GECIKME
,ROUND((
(
(
ABS(ISNULL((
SELECT abs(ISNULL(DBO.ROUNDYTL(SUM((BORC - ALACAK) / (
CASE
WHEN ISNULL(KUR, 1) = 0
THEN 1
ELSE KUR
END
))), 0))
FROM F0102D0001TBLCARIGENELHAREKET
WHERE FIRMANO = TCG.FIRMANO
AND ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BELGEIZAHAT = TCG.BELGEIZAHAT
AND IND = TCG.IND
), 0) - (
ISNULL(ABS(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / (
CASE
WHEN ISNULL(TCG.KUR, 1) = 0
THEN 1
ELSE TCG.KUR
END
))))), 0)
))
) * (((ROUND(CAST(GETDATE() - ((CAST(TCG.VADE AS INT) + 1)) AS FLOAT), 0) * TBLCARI.GECIKMEFAIZI)) / 3000)
)
), 0) AS VADEFARKI
FROM F0102D0001TBLCARIGENELHAREKET AS TCG
LEFT JOIN F0102D0001TBLCARIGENELHAREKET AS TCG2 ON TCG2.TAHSILLINK = TCG.IND
LEFT JOIN F0102D0001TBLCARGIRHAREKET AS CARGIR ON CARGIR.IND = TCG.ISLEMIND
AND CARGIR.FIRMANO = TCG.FIRMANO
AND CARGIR.EVRAKNO = TCG.BELGEIND
AND CARGIR.STATUS NOT IN (
2
,9
,48
,49
)
LEFT JOIN F0102TBLCARI AS TBLCARI ON TBLCARI.IND = TCG.FIRMANO
LEFT JOIN F0102D0001TBLCARCIKIADEHAREKET AS CARCIKIADE ON CARCIKIADE.IND = TCG.ISLEMIND
AND CARCIKIADE.IZAHAT = TCG.ISLEMIZAHAT
LEFT JOIN F0102D0001TBLBANKAGENELHAREKET AS BH ON BH.BELGELINK = TCG.BELGELINK
AND BH.BELGEIZAHAT = 74
AND BH.ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BH.ISLEMIZAHAT IN (
2
,3
)
LEFT JOIN F0102D0001VBASLIKLAR AS BASLIK ON TCG.BELGEIND = BASLIK.IND
AND TCG.FIRMANO = BASLIK.FIRMANO
AND TCG.BELGEIZAHAT = BASLIK.BELGETIPI
WHERE TCG.BELGEIZAHAT IN (
12
,13
,18
,19
,119
)
AND TCG.ISLEMIZAHAT NOT IN (
1
,4
,10
,11
)
AND CARGIR.STATUS NOT IN (
2
,9
,48
,49
)
AND TCG.FIRMANO = TBLCARI.IND
AND TBLCARI.IND >= 100
GROUP BY TBLCARI.IND
,TBLCARI.SOYADI
,TBLCARI.ADI
,TBLCARI.OPSIYON
,TBLCARI.FIRMAKODU
,TBLCARI.FIRMAADI
,TBLCARI.FIRMATIPI
,TBLCARI.GECIKMEFAIZI
,TBLCARI.KOD1
,TBLCARI.KOD2
,TBLCARI.KOD3
,TBLCARI.KOD4
,TBLCARI.KOD5
,TBLCARI.TELEFON1
,TBLCARI.TELEFON2
,TBLCARI.TELEFON3
,TBLCARI.KEFIL1
,TBLCARI.KEFIL2
,CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100))
,CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100))
,CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100))
,CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100))
,CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100))
,CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100))
,TBLCARI.RISKLIMITI
,TBLCARI.KREDILIMITI
,TCG.ISLEMIZAHAT
,TCG.BELGEIZAHAT
,TCG.FIRMANO
,TCG.PARABIRIMI
,TBLCARI.SATISYAPILMASIN
,TCG.VADE
,BASLIK.OZELKOD1
,BASLIK.OZELKOD2
,BASLIK.OZELKOD3
,BASLIK.OZELKOD4
,CARGIR.TUTAR
,TCG.KUR
,CARGIR.STATUS
,TCG.BELGEIZAHAT
,TCG.IND
,CARCIKIADE.STATUS
,TCG.ISLEMIND
UNION
SELECT 1 AS SIRA
,TBLCARI.IND
,(
SELECT SUM(BORC - ALACAK)
FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR
WHERE CARHAR.FIRMANO = TBLCARI.IND
) AS BAKIYE
,WS.IND AS ISLEMIND
,TBLCARI.SOYADI
,TBLCARI.ADI
,TBLCARI.SOYADI + ' ' + TBLCARI.ADI AS SOYADAD
,TBLCARI.FIRMAKODU
,TBLCARI.FIRMAADI
,TBLCARI.FIRMATIPI
,TBLCARI.GECIKMEFAIZI
,TBLCARI.KOD1
,TBLCARI.KOD2
,TBLCARI.KOD3
,TBLCARI.KOD4
,TBLCARI.KOD5
,TBLCARI.TELEFON1
,TBLCARI.TELEFON2
,TBLCARI.TELEFON3
,TBLCARI.TELEFON3 AS GSMNO
,TBLCARI.KEFIL1
,TBLCARI.KEFIL2
,TBLCARI.OPSIYON
,TBLCARI.SATISYAPILMASIN
,CEILING(CAST((GETDATE() - TARIH) AS NUMERIC) / 30) AS GECIKMEAY
,CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT
,CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA
,CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA
,CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK
,CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1
,CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2
,TBLCARI.RISKLIMITI
,TBLCARI.KREDILIMITI
,WS.IZAHAT AS ISLEMIZAHAT
,0 AS BELGEIZAHAT
,'TL' AS PARABIRIMI
,WS.TARIH AS VADE
,'' AS OZELKOD1
,'' AS OZELKOD2
,'' AS OZELKOD3
,'' AS OZELKOD4
,0 AS STATUS
,WS.TUTAR AS BORC
,0.0 AS TAHSILAT
,WS.TUTAR AS KALAN
,CAST(GETDATE() - 1 AS INT) - CAST(TARIH AS INT) AS GUN
,ROUND(CAST(GETDATE() - ((CAST(TARIH AS INT) + 1)) AS FLOAT), 0) * WS.TUTAR AS GECIKME
,(((ROUND(CAST(GETDATE() - ((CAST(TARIH AS INT) + 1)) AS FLOAT), 0) * WS.TUTAR) * TBLCARI.GECIKMEFAIZI) / 3000) AS VADEFARKI
FROM F0102D0001TBLWSTAKSITLISATIS AS WS
LEFT JOIN F0102TBLCARI AS TBLCARI ON TBLCARI.IND = WS.MUSTERINO
WHERE IZAHAT = 100
So how do we go about figuring out the problem. First thing I would do is to break apart the two queries. Will each query run on their own? If not, that is a great place to start. There are so many calculations in these two queries it is hard to say where the problem might be. You may have to comment out several of these columns to figure out which column is causing the issue.
I don't envy you in this process because those table names make me want to take out my eyeballs and pop them. That is going to add some level of challenge here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2014 at 9:34 am
-------------------------------------------------------------------------
-- Your first query has lots of scope for simplification:
-------------------------------------------------------------------------
SELECT 0 AS SIRA
,TBLCARI.IND
,(
SELECT SUM(BORC - ALACAK)
FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR
WHERE CARHAR.FIRMANO = TBLCARI.IND
) AS BAKIYE
,TCG.ISLEMIND
,TBLCARI.SOYADI
,TBLCARI.ADI
,TBLCARI.SOYADI + ' ' + TBLCARI.ADI AS SOYADAD
,TBLCARI.FIRMAKODU
,TBLCARI.FIRMAADI
,TBLCARI.FIRMATIPI
,TBLCARI.GECIKMEFAIZI
,TBLCARI.KOD1
,TBLCARI.KOD2
,TBLCARI.KOD3
,TBLCARI.KOD4
,TBLCARI.KOD5
,TBLCARI.TELEFON1
,TBLCARI.TELEFON2
,TBLCARI.TELEFON3
,TBLCARI.TELEFON3 AS GSMNO
,TBLCARI.KEFIL1
,TBLCARI.KEFIL2
,TBLCARI.OPSIYON
,TBLCARI.SATISYAPILMASIN
,CEILING(CAST((GETDATE() - TCG.VADE) AS NUMERIC) / 30) AS GECIKMEAY
,CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100)) AS ISTIHBARAT
,CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100)) AS ADRESPOSTA
,CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) AS ADRESFATURA
,CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100)) AS ADRESSEVK
,CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100)) AS KEFILADRES1
,CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100)) AS KEFILADRES2
,TBLCARI.RISKLIMITI
,TBLCARI.KREDILIMITI
,TCG.ISLEMIZAHAT
,TCG.BELGEIZAHAT
,TCG.PARABIRIMI
,TCG.VADE
,BASLIK.OZELKOD1
,BASLIK.OZELKOD2
,BASLIK.OZELKOD3
,BASLIK.OZELKOD4
,CASE
WHEN TCG.BELGEIZAHAT = 12 THEN CARCIKIADE.STATUS
ELSE CARGIR.STATUS
END AS STATUS
,x.BORC
,ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / NULLIF(TCG.KUR,0)))), 0) AS TAHSILAT
,MAX(x.BORC) - ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / NULLIF(TCG.KUR,0)))), 0) AS KALAN
,CAST(GETDATE() - 1 AS INT) - CAST(TCG.VADE AS INT) AS GUN -- use DATEDIFF(DAY,X,Y)
,(
abs(ISNULL(DBO.ROUNDYTL((
SUM((TCG.BORC - TCG.ALACAK) / (NULLIF(TCG.KUR,0)))
)), 0)) - ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) /(NULLIF(TCG.KUR,0))), 0))
) * MAX(SomeDays) AS GECIKME
,ROUND((
(
(
ABS(ISNULL(MAX(x.BORC), 0) - (
ISNULL(DBO.ROUNDYTL(ABS(SUM((TCG2.BORC - TCG2.ALACAK) / NULLIF(TCG.KUR,0)))), 0)
))
) * ((MAX(SomeDays) * TBLCARI.GECIKMEFAIZI) / 3000)
)
), 0) AS VADEFARKI
FROM F0102D0001TBLCARIGENELHAREKET AS TCG -- ###
CROSS APPLY (
SELECT BORC = ABS(ISNULL(DBO.ROUNDYTL(
SUM((BORC - ALACAK) / NULLIF(KUR,0))
), 0))
FROM F0102D0001TBLCARIGENELHAREKET -- ###
WHERE FIRMANO = TCG.FIRMANO
AND ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BELGEIZAHAT = TCG.BELGEIZAHAT
AND IND = TCG.IND
) x
CROSS APPLY (
SELECT SomeDays = ROUND(CAST(GETDATE() - ((CAST(TCG.VADE AS INT) + 1)) AS FLOAT), 0)
) y
LEFT JOIN F0102D0001TBLCARIGENELHAREKET AS TCG2 -- ###
ON TCG2.TAHSILLINK = TCG.IND
LEFT JOIN F0102D0001TBLCARGIRHAREKET AS CARGIR
ON CARGIR.IND = TCG.ISLEMIND
AND CARGIR.FIRMANO = TCG.FIRMANO
AND CARGIR.EVRAKNO = TCG.BELGEIND
AND CARGIR.STATUS NOT IN (2,9,48,49)
LEFT JOIN F0102TBLCARI AS TBLCARI
ON TBLCARI.IND = TCG.FIRMANO
LEFT JOIN F0102D0001TBLCARCIKIADEHAREKET AS CARCIKIADE
ON CARCIKIADE.IND = TCG.ISLEMIND
AND CARCIKIADE.IZAHAT = TCG.ISLEMIZAHAT
LEFT JOIN F0102D0001TBLBANKAGENELHAREKET AS BH
ON BH.BELGELINK = TCG.BELGELINK
AND BH.BELGEIZAHAT = 74
AND BH.ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BH.ISLEMIZAHAT IN (2,3)
LEFT JOIN F0102D0001VBASLIKLAR AS BASLIK
ON TCG.BELGEIND = BASLIK.IND
AND TCG.FIRMANO = BASLIK.FIRMANO
AND TCG.BELGEIZAHAT = BASLIK.BELGETIPI
WHERE TCG.BELGEIZAHAT IN (12,13,18,19,119)
AND TCG.ISLEMIZAHAT NOT IN (1,4,10,11)
AND CARGIR.STATUS NOT IN (2,9,48,49)
AND TCG.FIRMANO = TBLCARI.IND
AND TBLCARI.IND >= 100 -- converts LEFT JOIN to INNER JOIN
GROUP BY TBLCARI.IND
,TBLCARI.SOYADI
,TBLCARI.ADI
,TBLCARI.OPSIYON
,TBLCARI.FIRMAKODU
,TBLCARI.FIRMAADI
,TBLCARI.FIRMATIPI
,TBLCARI.GECIKMEFAIZI
,TBLCARI.KOD1
,TBLCARI.KOD2
,TBLCARI.KOD3
,TBLCARI.KOD4
,TBLCARI.KOD5
,TBLCARI.TELEFON1
,TBLCARI.TELEFON2
,TBLCARI.TELEFON3
,TBLCARI.KEFIL1
,TBLCARI.KEFIL2
,CAST(TBLCARI.ISTIHBARAT AS NVARCHAR(100))
,CAST(TBLCARI.ADRESPOSTA AS NVARCHAR(100))
,CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100))
,CAST(TBLCARI.ADRESSEVK AS NVARCHAR(100))
,CAST(TBLCARI.KEFILADRES1 AS NVARCHAR(100))
,CAST(TBLCARI.KEFILADRES2 AS NVARCHAR(100))
,TBLCARI.RISKLIMITI
,TBLCARI.KREDILIMITI
,TCG.ISLEMIZAHAT
,TCG.BELGEIZAHAT
,TCG.FIRMANO
,TCG.PARABIRIMI
,TBLCARI.SATISYAPILMASIN
,TCG.VADE
,BASLIK.OZELKOD1
,BASLIK.OZELKOD2
,BASLIK.OZELKOD3
,BASLIK.OZELKOD4
,CARGIR.TUTAR
,TCG.KUR
,CARGIR.STATUS
,TCG.BELGEIZAHAT
,TCG.IND
,CARCIKIADE.STATUS
,TCG.ISLEMIND
-------------------------------------------------------------------------
-- You could make it a hell of a lot simpler by rewriting it like this,
-- moving the aggregation out of the main query into the APPLY blocks
-------------------------------------------------------------------------
SELECT whatever
FROM F0102TBLCARI AS TBLCARI
INNER JOIN F0102D0001TBLCARIGENELHAREKET AS TCG
ON TBLCARI.IND = TCG.FIRMANO
OUTER APPLY (
SELECT AGGREGATED(whatever)
FROM F0102D0001TBLCARIGENELHAREKET tcg2
WHERE TCG2.TAHSILLINK = TCG.IND
) TCG2
CROSS APPLY (
SELECT BORC = ABS(ISNULL(DBO.ROUNDYTL(
SUM((BORC - ALACAK) / NULLIF(KUR,0))
), 0))
FROM F0102D0001TBLCARIGENELHAREKET -- ###
WHERE FIRMANO = TCG.FIRMANO
AND ISLEMIZAHAT = TCG.ISLEMIZAHAT
AND BELGEIZAHAT = TCG.BELGEIZAHAT
AND IND = TCG.IND
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2014 at 10:27 am
Quick thought, to find the conversion causing the error, get the execution plan XML and look for an implicit cast to datetime. Also any value higher than 2958463 (9999-12-31) will cause this error when using simple mathematical operator with the DATETIME data type.
😎
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply