TechQA.

FirebirdSQL union duplicate records

199 views Asked by At

Using FirebirdSQL 3.0.2. I am trying to pull out a report with fixed rows in it. Meaning, I would like to have certain number of rows each time even. If certain row does not exists in result set, I would like to display that missing row having values of zeros.

For that I have a dummy table RAPOR_EK with all possible row combinations included as rows. I select that table using UNION in my main report SQL.

My problem is about duplicate records. Where there is a record selected in my main report SQL, I have one additional record selected from dummy table.

Table structures, dummy table records, report SQL and report SQL output is included below. You can see duplicate records in row numbers 1-2, 5-6, 7-8 and so on in report SQL output.

I appreciate any help.

Raw data table DDL

CREATE TABLE TABLO_MEDAS (
ID BIGINT NOT NULL,
T_GERIBILDIRIMNO INTEGER,
T_ILANTARIHI TIMESTAMP,
T_KODNO INTEGER,
T_KADEME SMALLINT,
T_IL VARCHAR(15),
T_ILCE VARCHAR(20),
T_SEBEKEUNSURU VARCHAR(220),
T_KESINTINEDENIILISKIACIKLAMA VARCHAR(250),
TKS_KAYNAGAGORE VARCHAR(20),
TKS_SUREYEGORE VARCHAR(10),
TKS_SEBEBEGORE VARCHAR(20),
T_BILDIRIMEGORE VARCHAR(15),
T_BASLAMATARIHI TIMESTAMP,
T_SONAERMETARIHI TIMESTAMP,
T_KESINTISURESI DOUBLE PRECISION,
TEKS_IMARALANICIOG DOUBLE PRECISION,
TEKS_IMARALANICIAG DOUBLE PRECISION,
TEKS_IMARALANDISIOG DOUBLE PRECISION,
TEKS_IMARALANDISIAG DOUBLE PRECISION,
TES_IMARALANICIOG DOUBLE PRECISION,
TES_IMARALANICIAG DOUBLE PRECISION,
TES_IMARALANDISIOG DOUBLE PRECISION,
TES_IMARALANDISIAG DOUBLE PRECISION,
T_ISLETME VARCHAR(50),
T_SURE VARCHAR(25),
CONSTRAINT PK_TABLO_MEDAS PRIMARY KEY (ID)
);

Dummy table DDL

CREATE TABLE RAPOR_EK (
    KAYNAK VARCHAR(30),
    SEBEP VARCHAR(30)
) ;

Report SQL

select 
  '2015-01-01' AS "TARIH",
  10 AS "RAPOR_TURU",
  M.TKS_KAYNAGAGORE AS "KAYNAK", 
  M.TKS_SEBEBEGORE AS "SEBEP", 
  (sum(M.TEKS_IMARALANICIOG)/(select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_OG",
  (sum(M.TEKS_IMARALANICIAG)/(select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_AG",
  ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG)) / ((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IAIK_TOPLAM",
  (sum(M.TEKS_IMARALANDISIOG)/(select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_OG",
  (sum(M.TEKS_IMARALANDISIAG)/(select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_AG",
  ((sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG)) / ((select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IADK_TOPLAM",
  ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG) + (sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG))) / ((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "GNLTOPLAM" 
from TABLO_MEDAS M
where 
  M.T_ILCE = 'CİHANBEYLİ' 
  and T_BILDIRIMEGORE = 'Bildirimsiz'
  and (T_BASLAMATARIHI >= '2015-01-01' and T_BASLAMATARIHI <= '2015-01-31 23:59:59')
group by 
  M.TKS_KAYNAGAGORE,
  M.TKS_SEBEBEGORE

UNION

SELECT 
  '2015-01-01' AS "TARIH",
  10 AS "RAPOR_TURU",
  KAYNAK,
  SEBEP,
  0,
  0,
  0,
  0,
  0,
  0,
  0  
FROM
  RAPOR_EK

Dummy table records

KAYNAK  SEBEP
Dağıtım-AG  Dışsal
Dağıtım-AG  Şebeke işletmecisi
Dağıtım-AG  Mücbir Sebep
Dağıtım-AG  Güvenlik
Dağıtım-OG  Dışsal
Dağıtım-OG  Şebeke işletmecisi
Dağıtım-OG  Mücbir Sebep
Dağıtım-OG  Güvenlik
İletim Sistemi  Şebeke işletmecisi
İletim Sistemi  Mücbir Sebep
İletim Sistemi  Dışsal
İletim Sistemi  Güvenlik

Report SQL output

TARIH   RAPOR_TURU  KAYNAK  SEBEP   IAIK_OG IAIK_AG IAIK_TOPLAM IADK_OG IADK_AG IADK_TOPLAM GNLTOPLAM
2015-01-01  10  Dağıtım-AG  Dışsal  0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-AG  Dışsal  0   0.00046546808634433004  0.000462962962962963    0   0.003875217981011432    0.0028260562385191467   0.0011495668596296753
2015-01-01  10  Dağıtım-AG  Güvenlik    0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-AG  Mücbir Sebep    0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-AG  Şebeke işletmecisi  0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-AG  Şebeke işletmecisi  0   0.0402048059579915  0.03998842592592593 0   0.09726797132338694 0.07093401158683058 0.04897975941207866
2015-01-01  10  Dağıtım-OG  Dışsal  0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-OG  Dışsal  0   0.052365159713737126    0.052083333333333336    0.042275574112734866    0   0.011445527766002543    0.04027589604631112
2015-01-01  10  Dağıtım-OG  Güvenlik    0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-OG  Güvenlik    0.16129032258064516 0.0005818351079304125   0.0014467592592592592   0   0   0   0.00102639898181221
2015-01-01  10  Dağıtım-OG  Mücbir Sebep    0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-OG  Şebeke işletmecisi  0   0   0   0   0   0   0
2015-01-01  10  Dağıtım-OG  Şebeke işletmecisi  0.6881720430107527  0.8316751032757317  0.8309027777777778  2.8491649269311066  1.3673706645998838  1.768545994065282   1.1033378494888533
2015-01-01  10  İletim Sistemi  Dışsal  0   0   0   0   0   0   0
2015-01-01  10  İletim Sistemi  Güvenlik    0   0   0   0   0   0   0
2015-01-01  10  İletim Sistemi  Mücbir Sebep    0   0   0   0   0   0   0
2015-01-01  10  İletim Sistemi  Şebeke işletmecisi  0   0   0   0   0   0   0

Edit: Using below SQL returns rows only present in TABLO_MEDAS.

SELECT 
  '2015-01-01' AS "TARIH",
  10 AS "RAPOR_TURU",
  RAPOR_EK.KAYNAK, 
  RAPOR_EK.SEBEP, 
  (sum(M.TEKS_IMARALANICIOG)/(select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_OG",
  (sum(M.TEKS_IMARALANICIAG)/(select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_AG",
  ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG)) / ((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IAIK_TOPLAM",
  (sum(M.TEKS_IMARALANDISIOG)/(select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_OG",
  (sum(M.TEKS_IMARALANDISIAG)/(select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_AG",
  ((sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG)) / ((select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IADK_TOPLAM",
  ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG) + (sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG))) / ((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "GNLTOPLAM" 
FROM
  RAPOR_EK
LEFT JOIN TABLO_MEDAS AS M ON M.TKS_KAYNAGAGORE = RAPOR_EK.KAYNAK AND M.TKS_SEBEBEGORE = RAPOR_EK.SEBEP
where 
  M.T_ILCE = 'CİHANBEYLİ' 
  and T_BILDIRIMEGORE = 'Bildirimsiz'
  and (T_BASLAMATARIHI >= '2015-01-01' and T_BASLAMATARIHI <= '2015-01-31 23:59:59')
group by 
  KAYNAK,
  SEBEP

Working SQL is as follows:

SELECT 
  RAPOR_EK.KAYNAK, 
  RAPOR_EK.SEBEP, 
  COALESCE(DT.IAIK_OG, 0) AS "IAIK_OG",
  COALESCE(DT.IAIK_AG, 0) AS "IAIK_AG",
  COALESCE(DT.IAIK_TOPLAM, 0) AS "IAIK_TOPLAM",
  COALESCE(DT.IADK_OG, 0) AS "IADK_OG",
  COALESCE(DT.IADK_AG, 0) AS "IADK_AG",
  COALESCE(DT.IADK_TOPLAM, 0) AS "IADK_TOPLAM",
  COALESCE(DT.GNLTOPLAM, 0) AS "GNLTOPLAM"
FROM RAPOR_EK
LEFT JOIN (
  SELECT 
    M.TKS_KAYNAGAGORE AS "KAYNAK", 
    M.TKS_SEBEBEGORE AS "SEBEP", 
    (sum(M.TES_IMARALANICIOG)/(select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IAIK_OG",
    (sum(M.TES_IMARALANICIAG)/(select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IAIK_AG",
    ((sum(M.TES_IMARALANICIOG) + sum(M.TES_IMARALANICIAG)) / ((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))*60) AS "IAIK_TOPLAM",
    (sum(M.TES_IMARALANDISIOG)/(select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IADK_OG",
    (sum(M.TES_IMARALANDISIAG)/(select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IADK_AG",
    ((sum(M.TES_IMARALANDISIOG) + sum(M.TES_IMARALANDISIAG)) / ((select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))*60) AS "IADK_TOPLAM",
    ((sum(M.TES_IMARALANICIOG) + sum(M.TES_IMARALANICIAG) + (sum(M.TES_IMARALANDISIOG) + sum(M.TES_IMARALANDISIAG))) / ((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')  + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))*60) AS "GNLTOPLAM" 
  FROM TABLO_MEDAS M
  WHERE 
    M.T_ILCE = 'CİHANBEYLİ' 
    AND M.T_BILDIRIMEGORE = 'Bildirimsiz'
    AND M.TKS_SUREYEGORE = 'Uzun'
    AND (M.T_BASLAMATARIHI >= '2015-01-01' AND T_BASLAMATARIHI <= '2015-01-31 23:59:59')
  GROUP BY 
    M.TKS_KAYNAGAGORE,
    M.TKS_SEBEBEGORE
) DT USING (KAYNAK, SEBEP)
ORDER BY KAYNAK, SEBEP
sql duplicates union firebird-3.0
Original Q&A
1

There are 1 answers

2
Mark Rotteveel On BEST ANSWER

Given the complexity of your data, I have created a simplified schema to demonstrate a solution:

create table report_source (
  REPORT_ID_1 VARCHAR(25) NOT NULL,
  REPORT_ID_2 VARCHAR(25) NOT NULL,
  CONSTRAINT PK_REPORT_SOURCE PRIMARY KEY (REPORT_ID_1, REPORT_ID_2)
);

create table report_data (
  REPORT_ID_1 VARCHAR(25) NOT NULL,
  REPORT_ID_2 VARCHAR(25) NOT NULL,
  VALUE_1 INT,
  VALUE_2 INT
);

insert into report_source (report_id_1, report_id_2) values ('a', 'a');
insert into report_source (report_id_1, report_id_2) values ('a', 'b');
insert into report_source (report_id_1, report_id_2) values ('b', 'a');
insert into report_source (report_id_1, report_id_2) values ('b', 'b');

insert into report_data (report_id_1, report_id_2, value_1, value_2) values ('a', 'a', 1, 2);
insert into report_data (report_id_1, report_id_2, value_1, value_2) values ('b', 'b', 3, 4);

Where report_source has the same role as your rapor_ek, and report_data that of tablo_medas.

Now to get the data you need, we select all rows from report_source, and left join to report_data, replacing null values with a default value:

select src.report_id_1, src.report_id_2, 
  coalesce(dt.value_1, 0) as value_1, 
  coalesce(dt.value_2, 0) as value_2
from report_source src
left join report_data dt using (report_id_1, report_id_2)

Which yields:

a   a   1   2
a   b   0   0
b   a   0   0
b   b   3   4

However your code isn't that simple, because you also have conditions in your where-clause that transform the left join to an inner join. To get your report data with the defaults, that query needs to take the place of report_data, in other words:

select src.report_id_1, src.report_id_2, 
  coalesce(dt.value_1, 0) as value_1, 
  coalesce(dt.value_2, 0) as value_2
from report_source src
left join (
    select *
    from report_data
    where <conditions>
    <group by, etc>
) dt using (report_id_1, report_id_2)

You may also need to replace the join using (report_id_1, report_id_2) with an on dt.report_id_1 = src.report_id_1 and dt.report_id_1 = src.report_id_2

Related Questions in SQL

Related Questions in DUPLICATES

Related Questions in UNION

Related Questions in FIREBIRD-3.0

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions