Posts
The following query (ignore most fields,just pay attention to d1.last_aa)
Q1
select
d1.last_aa,
m.c_ylikoy as c_ylikoy,
n_ylikoy = m.n_ylikoy,
c_typ_ylik=tf.c_typ_ylik,
n_typ_ylik = tf.n_typ_ylik,
tm_mesh=m.tm_mesh,
pt_fpa=m.pt_fpa,
pt_fpa_last= d1.pt_fpa,
end_eis_ex = d.end_eis_ex,
ps_diaqesh = sum(d.ps_diaqesh),
tm_monadas = avg(d.tm_monadas),
c_mon_metr = m.c_mon_metr,
c_typ_gmdn = m.c_typ_gmdn,
c_loga = m.c_loga,
c_cpv = c.c_cpv,
n_cpv = c.n_cpv,
c_prom = h1.c_prom,
c_loga_dls = ss.c_loga_dls,
tm_mm_last = max(decimal (d1.timh_symb /s.periektiko, 16, 4)),
tm_mm_fpa_last = max(( d1.timh_symb /s.periektiko) * (100.0 +d1.pt_fpa) / 100)
from hi_denyl d inner join hi_henyl h on
d.c_entyp = h.c_entyp and
d.last_year = h.last_year and
d.last_aa = h.last_aa inner join grnoe g on
g.c_grnoe = h.c_grnoe inner join hi_mylik m on
m.c_ylikoy = d.c_ylikoy inner join tylik tf on
m.c_typ_ylik = tf.c_typ_ylik left join cpv c on
tf.c_cpv = c.c_cpv left join
(hi_dylpr d1 inner join hi_sylik s on
s.c_ylikoy = d1.c_ylikoy and s.aa_syskeya = d1.aa_syskeya
inner join hi_hylpr h1 on h1.last_aa = d1.last_aa and
h1.c_entyp = d1.c_entyp and h1.last_year = d1.last_year
and h1.end_akyro = '') on
d1.c_ylikoy = d.c_ylikoy
left join (select
c_loga_dls=max(c_loga_dls),c_loga
from nosxe n
where n.y_xrhshs='01/01/2020'
and n.c_forea='01'
group by c_loga
) s on m.c_loga = s.c_loga
where
h.last_year = '1/1/2020'
and h.c_entyp = 'ΥΛ010'
and d.c_ylikoy >= '13'
and d.c_ylikoy <= '13ΩΩΩΩΩΩΩΩ'
and h.d_diaqeshs >= '01/01/2020'
and h.d_diaqeshs <= '31/12/2020'
and h.end_akyro = ''
and ( (d1.last_aa is null)
or (d1.last_aa = (select max(d2.last_aa)
from hi_dylpr d2 where
d1.c_ylikoy = d2.c_ylikoy)))
group by
d1.last_aa,
m.c_ylikoy,
n_ylikoy,
tf.c_typ_ylik,
tf.n_typ_ylik,
m.tm_mesh,
m.pt_fpa,
d1.pt_fpa,
h1.c_prom,
ss.c_loga_dls,
d.end_eis_ex,
end_diax,
m.c_mon_metr,
m.c_typ_gmdn,
m.c_loga,
c.c_cpv,
c.n_cpv
order by m.c_ylikoy
totally ignores the (d1.last_aa is null) condition and just brings the results that qualify with
(d1.last_aa = (select max(d2.last_aa)
from hi_dylpr d2 where
d1.c_ylikoy = d2.c_ylikoy))
Breaking it apart and just issuing d1.last_aa is null
Q2
where
h.last_year = '1/1/2020'
and h.c_entyp = 'ΥΛ010'
and d.c_ylikoy >= '13'
and d.c_ylikoy <= '13ΩΩΩΩΩΩΩΩ'
and h.d_diaqeshs >= '01/01/2020'
and h.d_diaqeshs <= '31/12/2020'
and h.end_akyro = ''
and d1.last_aa is null
group by
d1.last_aa,
m.c_ylikoy,
n_ylikoy,
tf.c_typ_ylik,
tf.n_typ_ylik,
m.tm_mesh,
m.pt_fpa,
d1.pt_fpa,
h1.c_prom,
ss.c_loga_dls,
d.end_eis_ex,
end_diax,
m.c_mon_metr,
m.c_typ_gmdn,
m.c_loga,
c.c_cpv,
c.n_cpv
order by m.c_ylikoy
brings the following results.Note that last_aa column is comprised of NULLs only.
Running the query with the other part
Q3
where
h.last_year = '1/1/2020'
and h.c_entyp = 'ΥΛ010'
and d.c_ylikoy >= '13'
and d.c_ylikoy <= '13ΩΩΩΩΩΩΩΩ'
and h.d_diaqeshs >= '01/01/2020'
and h.d_diaqeshs <= '31/12/2020'
and h.end_akyro = '' and
(d1.last_aa = (select max(d2.last_aa)
from hi_dylpr d2 where
d1.c_ylikoy = d2.c_ylikoy))
group by
d1.last_aa,
m.c_ylikoy,
n_ylikoy,
tf.c_typ_ylik,
tf.n_typ_ylik,
m.tm_mesh,
m.pt_fpa,
d1.pt_fpa,
h1.c_prom,
ss.c_loga_dls,
d.end_eis_ex,
end_diax,
m.c_mon_metr,
m.c_typ_gmdn,
m.c_loga,
c.c_cpv,
c.n_cpv
order by m.c_ylikoy
results in the following, the same as Q1.Note the column last_aa is filled with numbers,no nulls here.
That's all on II 10.2.0 (a64.lnx/100)
Now Q1 on II 10.0.1 (a64.lnx/100)NPTL
brings the correct results of nulls and not nulls,as is.
On II 9.3 the query just doesn't run, some cryptic QEF error of some sort.
I have to say that there's other compatibility issues in 10.2 especially in the ABF part.
Comments