Sql Mizan Örneği

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
))