pythontr.com
Ana hesaplar ve alt hesaplarla birlikte gruplayan örnek çalışma
select * from ( SELECT * from ( SELECT HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD, ( SELECT COALESCE(SUM(HSP_FIS_DET_BRC_ANA - HSP_FIS_DET_ALCK_ANA),0) FROM HSP_FIS_DET A JOIN HSP_FIS B ON A.HSP_FIS_RECNO = B.HSP_FIS_RECNO WHERE COALESCE(B.HSP_FIS_DLT,0)=0 AND B.HSP_FIS_TRH < '01.01.2014' AND A.HSP_FIS_DET_HSP_KOD = HSP_PLN.HSP_PLN_HSP_KOD )AS DVR, Sum(HSP_FIS_DET.HSP_FIS_DET_BRC_ANA) as ToplamBorc, Sum(HSP_FIS_DET.HSP_FIS_DET_ALCK_ANA) as ToplamAlacak FROM HSP_PLN --LEFT JOIN HSP_FIS_DET ON HSP_PLN.HSP_PLN_HSP_KOD LIKE HSP_FIS_DET.HSP_FIS_DET_HSP_KOD || '%' --LEFT JOIN HSP_FIS_DET ON (HSP_PLN.HSP_PLN_HSP_KOD LIKE HSP_FIS_DET.HSP_FIS_DET_HSP_KOD || '%' OR HSP_PLN.HSP_PLN_HSP_KOD LIKE SUBSTR(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD,1,3)) LEFT JOIN HSP_FIS_DET ON --HSP_PLN.HSP_PLN_HSP_KOD = regexp_substr(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD, '^(\d|)+(\.|)+(\d|)+(\d|)+(\.|)+(\d|)+(\.|)+(\d|)+') OR --HSP_PLN.HSP_PLN_HSP_KOD = regexp_substr(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD, '^(\d|)+(\.|)+(\d|)+(\d|)+(\.|)+(\d|)+') OR --HSP_PLN.HSP_PLN_HSP_KOD = regexp_substr(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD, '^(\d|)+(\.|)+(\d|)+(\d|)+') OR HSP_PLN.HSP_PLN_HSP_KOD LIKE HSP_FIS_DET.HSP_FIS_DET_HSP_KOD || '%' JOIN HSP_FIS ON HSP_FIS.HSP_FIS_RECNO = HSP_FIS_DET.HSP_FIS_RECNO WHERE HSP_FIS.HSP_FIS_TRH between '01.01.2014' AND '31.12.2014' AND COALESCE(HSP_PLN_DLT,0)=0 AND COALESCE(HSP_FIS_DLT,0)=0 AND COALESCE(HSP_FIS_DET_DLT,0)=0 GROUP BY HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD HAVING (((HSP_PLN_HSP_KOD) between '770' and '770.6.003')) ORDER BY HSP_PLN_HSP_KOD ) UNION select * from ( SELECT HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD, ( SELECT COALESCE(SUM(HSP_FIS_DET_BRC_ANA - HSP_FIS_DET_ALCK_ANA),0) FROM HSP_FIS_DET A JOIN HSP_FIS B ON A.HSP_FIS_RECNO = B.HSP_FIS_RECNO WHERE COALESCE(B.HSP_FIS_DLT,0)=0 AND B.HSP_FIS_TRH < '01.01.2014' AND A.HSP_FIS_DET_HSP_KOD = HSP_PLN.HSP_PLN_HSP_KOD )AS DVR, Sum(HSP_FIS_DET.HSP_FIS_DET_BRC_ANA) as ToplamBorc, Sum(HSP_FIS_DET.HSP_FIS_DET_ALCK_ANA) as ToplamAlacak FROM HSP_PLN LEFT JOIN HSP_FIS_DET ON HSP_PLN.HSP_PLN_HSP_KOD = regexp_substr(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD, '^(\d|)+(\.|)+(\d|)+(\.|)+(\d|)+') JOIN HSP_FIS ON HSP_FIS.HSP_FIS_RECNO = HSP_FIS_DET.HSP_FIS_RECNO WHERE HSP_FIS.HSP_FIS_TRH between '01.01.2014' AND '31.12.2014' AND COALESCE(HSP_PLN_DLT,0)=0 AND COALESCE(HSP_FIS_DLT,0)=0 AND COALESCE(HSP_FIS_DET_DLT,0)=0 GROUP BY HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD HAVING (((HSP_PLN_HSP_KOD) between '770' and '770.6.003')) ORDER BY HSP_PLN_HSP_KOD ) UNION select * from ( SELECT HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD, ( SELECT COALESCE(SUM(HSP_FIS_DET_BRC_ANA - HSP_FIS_DET_ALCK_ANA),0) FROM HSP_FIS_DET A JOIN HSP_FIS B ON A.HSP_FIS_RECNO = B.HSP_FIS_RECNO WHERE COALESCE(B.HSP_FIS_DLT,0)=0 AND B.HSP_FIS_TRH < '01.01.2014' AND A.HSP_FIS_DET_HSP_KOD = HSP_PLN.HSP_PLN_HSP_KOD )AS DVR, Sum(HSP_FIS_DET.HSP_FIS_DET_BRC_ANA) as ToplamBorc, Sum(HSP_FIS_DET.HSP_FIS_DET_ALCK_ANA) as ToplamAlacak FROM HSP_PLN LEFT JOIN HSP_FIS_DET ON HSP_PLN.HSP_PLN_HSP_KOD = regexp_substr(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD, '^(\d|)+(\.|)+(\d|)+') JOIN HSP_FIS ON HSP_FIS.HSP_FIS_RECNO = HSP_FIS_DET.HSP_FIS_RECNO WHERE HSP_FIS.HSP_FIS_TRH between '01.01.2014' AND '31.12.2014' AND COALESCE(HSP_PLN_DLT,0)=0 AND COALESCE(HSP_FIS_DLT,0)=0 AND COALESCE(HSP_FIS_DET_DLT,0)=0 GROUP BY HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD HAVING (((HSP_PLN_HSP_KOD) between '770' and '770.6.003')) ORDER BY HSP_PLN_HSP_KOD ) UNION select * from ( SELECT HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD, ( SELECT COALESCE(SUM(HSP_FIS_DET_BRC_ANA - HSP_FIS_DET_ALCK_ANA),0) FROM HSP_FIS_DET A JOIN HSP_FIS B ON A.HSP_FIS_RECNO = B.HSP_FIS_RECNO WHERE COALESCE(B.HSP_FIS_DLT,0)=0 AND B.HSP_FIS_TRH < '01.01.2014' AND A.HSP_FIS_DET_HSP_KOD = HSP_PLN.HSP_PLN_HSP_KOD )AS DVR, Sum(HSP_FIS_DET.HSP_FIS_DET_BRC_ANA) as ToplamBorc, Sum(HSP_FIS_DET.HSP_FIS_DET_ALCK_ANA) as ToplamAlacak FROM HSP_PLN LEFT JOIN HSP_FIS_DET ON HSP_PLN.HSP_PLN_HSP_KOD = regexp_substr(HSP_FIS_DET.HSP_FIS_DET_HSP_KOD, '^(\d|)+') JOIN HSP_FIS ON HSP_FIS.HSP_FIS_RECNO = HSP_FIS_DET.HSP_FIS_RECNO WHERE HSP_FIS.HSP_FIS_TRH between '01.01.2014' AND '31.12.2014' AND COALESCE(HSP_PLN_DLT,0)=0 AND COALESCE(HSP_FIS_DLT,0)=0 AND COALESCE(HSP_FIS_DET_DLT,0)=0 GROUP BY HSP_PLN_HSP_KOD, HSP_PLN_HSP_AD HAVING (((HSP_PLN_HSP_KOD) between '770' and '770.6.003')) ORDER BY HSP_PLN_HSP_KOD ))
Yorumlar