A strange query result.

A strange query result.

Post by Chris Salc » Sat, 10 Nov 2007 04:26:40


BM 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 )as
SELECT

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
;

******
 
 
 

1. Strange result from query, '>' gives same result as '>='

2. Strange Query Results When Search Results Return 0 Hits

Hi group,
I recently added a large number of filters to our query results.
Unfortunately, if a user gets zero hits off of a search, the Results
page looks something like this:

No documents matched the query " (sldkfjlskdjf) AND NOT #filename
*.jar AND NOT #filename *.btr AND NOT #filename *.cnf AND NOT
#filename *.ds AND NOT #filename *.log AND NOT #filename *.ftp AND NOT
#filename *.class AND NOT #vpath *\cfide* AND NOT #vpath */_vti* AND
NOT #filename *.hhs AND NOT #filename *.css AND NOT #filename *.dll
AND NOT #filename *.mdb AND NOT #filename *.def AND NOT #vpath
*\classes* AND NOT #filename *.old AND NOT #filename footer.cfm AND
NOT #filename *.js AND NOT #filename application.cfm ".

That's BAD. Any way I can get rid of it?

Thanks!
DTS

3. Displaying Results Sub-Query Result in Another Query

4. Return query results based on results of another field query

5. Query Results in Access GUI <> Query Results in ADO

6. Date math in query - strange results

7. Combined Query - Strange Results

8. Strange Query Results

9. Strange results from "not in" query

10. Strange query result with Order by

11. strange results from JDBC query

12. Very strange query result...

13. Strange Result from a query

14. Strange Result from a query (correct version)

15. Strange icon in query results