A strange query result.
IBM Informix Dynamic Server Version 9.40.HC5
HPUX 11iV1
I have a very weird issue with a very complex set of views. Here is
what I'm seeing:
SELECT
bucket, id
FROM
lu_ar_bucket_vw
WHERE
bucket=5
;
bucket id
--------- -------
8 2058987
8 2078673
8 2059319
8 2062939
* snip *
Which is rather crazy. I'm not sure how to distill this down to
something simpler, here is the lu_ar_bucket_vw :
create view informix.lu_ar_bucket_vw (bucket,id )asSELECT
CASE
when (deggrp like 'W/D%') then 10
when (deggrp like 'SAB%') then 9
when ((SELECT count(*)=0 FROM ctc_rec WHERE
ctc_rec.id=lu_ar_prog_vw.id
and ctc_rec.resrc='ISIR' and ctc_rec.stat='C' and
ctc_rec.tick in ('FY06', 'FY07') )) then 8
when ((SELECT(count(A.ctc_no)>0) and (count(B.ctc_no)>0) FROM
id_rec left join ctc_rec A on A.id=id_rec.id and
(A.tick='FY06'AND A.resrc='NRF7')
left join ctc_rec B on B.id=id_rec.id AND
(B.tick='FY07' AND B.resrc='NRF8')
WHERE id_rec.id=lu_ar_prog_vw.id)) then 7
when ((SELECT(count(A.ctc_no)>0) and (count(B.ctc_no)>0) FROM
id_rec left join ctc_rec A on A.id=id_rec.id and
(A.tick='FY06'AND A.resrc='FAC7')
left join ctc_rec B on B.id=id_rec.id AND
(B.tick='FY07' AND B.resrc='FAC8')
WHERE id_rec.id=lu_ar_prog_vw.id)) then 6
WHEN ((SELECT COUNT(A.ctc_no)>0 AND COUNT(sec.bbay_entry_id)>0
FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND
(A.tick='FY06' AND A.resrc='FAC7')
LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND
sec_tl.ay_slot=2
JOIN lu_bbay_entry_rec sec ON
sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date > TODAY +30
WHERE id_rec.id=lu_ar_prog_vw.id)) then 5
WHEN ((SELECT COUNT(A.ctc_no)>0 AND COUNT(sec.bbay_entry_id)>0
AND COUNT(ISIR.ctc_no)=0
FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND
(A.tick='FY06' AND A.resrc='FAC7')
LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND
sec_tl.ay_slot=2
JOIN lu_bbay_entry_rec sec ON
sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date <= TODAY +30
LEFT JOIN ctc_rec ISIR on ISIR.id=id_rec.id AND
ISIR.resrc='ISIR' AND ISIR.stat='C' AND ISIR.tick = 'FY07'
WHERE id_rec.id=lu_ar_prog_vw.id)) then 4
WHEN ((SELECT COUNT(A.ctc_no)>0 AND COUNT(sec.bbay_entry_id)>0
AND COUNT(ISIR.ctc_no)>0
FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND
(A.tick='FY06' AND A.resrc='FAC7')
LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND
sec_tl.ay_slot=2
JOIN lu_bbay_entry_rec sec ON
sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date <= TODAY +30
LEFT JOIN ctc_rec ISIR on ISIR.id=id_rec.id AND
ISIR.resrc='ISIR' AND ISIR.stat='C' AND ISIR.tick = 'FY07'
WHERE id_rec.id=lu_ar_prog_vw.id)) then 3
WHEN ((SELECT COUNT(A.ctc_no)=0 AND COUNT(sec.bbay_entry_id)>0
AND COUNT(ANFORM.ctc_no)>0
FROM id_rec LEFT JOIN ctc_rec A ON A.id=id_rec.id AND
(A.tick='FY06' AND A.resrc='FAC7')
LEFT JOIN lu_bbay_tl_rec sec_tl ON sec_tl.id=id_rec.id AND
sec_tl.ay_slot=2
JOIN lu_bbay_entry_rec sec ON
sec_tl.bbay_entry_id=sec.bbay_entry_id AND sec.beg_date > TODAY +30
left join ctc_rec ANFORM on ANFORM.tick='FY07' AND
ANFORM.resrc in ('ANFORM', 'ANFBBY') and ANFORM.stat='C' and
ANFORM.id=id_rec.id
WHERE id_rec.id=lu_ar_prog_vw.id)) then 2
else 1
END as BUCKET,
lu_ar_prog_vw.id
FROM
lu_ar_prog_vw
;
*****************************
lu_ar_prog_vw is another view constructed as follows:
CREATE VIEW informix.lu_ar_prog_vw
(
id,
bal_act,
debit_flag,
prog,
deggrp,
enr_date,
last_class_date
)
AS
SELECT suba_rec.suba_no AS id,
suba_rec.bal_act bal_act,
suba_rec.bal_act>0 debit_flag,
prog_first.prog,
prog_first.deggrp,
prog_first.enr_date,
( SELECT MAX(sec_rec.end_date)
FROM cw_rec
LEFT JOIN sec_rec
ON sec_rec.crs_no=cw_rec.crs_no
AND sec_rec.sec_no=cw_rec.sec
AND sec_rec.cat=cw_rec.cat
AND sec_rec.sess=cw_rec.sess
AND sec_rec.yr=cw_rec.yr
WHERE cw_rec.stat='R'
AND cw_rec.id=suba_rec.suba_no
)
AS last_Class_date
FROM suba_rec
JOIN prog_enr_rec prog_first
ON suba_rec.id=prog_first.id
JOIN prog_enr_rec prog_sec
ON suba_rec.id=prog_sec.id
WHERE subs = 'S/A'
AND bal_act != 0
AND prog_first.subprog IN ('EC',
'NG',
'NU')
GROUP BY 1,
2,
3,
4,
5,
6,
7
HAVING prog_first.enr_date=MAX(prog_sec.enr_date);
Any idea's on this whatsoever? Other than cursing the day this entered
your inbox that is :)
--
--------------------------------------------
Chris Salch