I have a SP that contains a complex SELECT.

(The SELECT is enclosed at the end of the message, but there is no
use in trying to understand the logics behind it: mine is a general
question).

God knows how long it took me to have the source code compiled into
FB, not to tell how long it took me to test the code!

Usually you don't write complex programs: you divide complex programs
in short, manageable chunks that you can be tested separately.

Then you build the program as a main procedure that calls several
different procedures and you get a short, clear program.

Going back to my SELECT, some parts of the code could well be written
as different SPs, but I cannot use SPs in a SELECT, can I ?

Is there a way to take a complex SELECT and make it shorter and
clearer ?

Thank you

Duilio Foschi



select distinct a.mat,a.nome from tb_per a, tb_emq b
where
a.mat=b.mat
and
a.turni_sn<>"N"
and
b.cod_qua=:cod_qua
and
a.cod_dpt=:cod_dpt
and
(
not exists
(select * from a_ava c where c.mat=a.mat
and c.data=:data)
)
and
(
not exists (
select * from s_turni d where d.mat=a.mat
and d.data=:data
and d.turno=:turno
and d.pr_d<>:pr_d
)
)
and
(
not exists
(
select * from s_turni e where e.mat=a.mat
and e.data=:data
and e.pr_d<>:pr_d
)
or exists
(
select * from tb_dop f where f.mat=a.mat
and f.data=:data
)
)
and
(
(
select count(*) from s_turni e where e.mat=a.mat
and e.data=:data
and e.pr_d<>:pr_d
)<2
)
and
(
not exists
(
select * from s_turni g where g.mat=a.mat
and g.data+1=:data
and (select ora_ini from tb_tur where
tb_tur.turno=g.turno)>=:sera
and (select ora_ini from tb_tur where
tb_tur.turno=g.turno)>
(select ora_fin from tb_tur where tb_tur.turno=g.turno)
and (select ora_ini from tb_tur where
tb_tur.turno=:turno)
-
(select ora_fin from tb_tur where tb_tur.turno=g.turno)
<:riposo_minimo
)
)
order by progr