Tom Is there a difference in the justification between your index and your field? Could one be looking left-aligned?
Brian > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Sent: 11 September 2006 14:27 > To: [email protected] > Subject: [U2] Selection Problem - Index? > > Under UniData 6.1 on AIX 5.3, I have an index on our > WAREHOUSE file for the Primary Bin Number (PRIM.BIN.NUM, > V-type, EXTRACT(@RECORD,1,1,0)). I get different results > depending on the order of selection. > > LIST.INDEX shows the following: > > File.................. WAREHOUSE > Alternate key length.. 12 > Node/Block size....... 2K > OV blocks............. 1 (1 in use, 1 overflowed) > Indices............... 3 (2 D-type) Index updates......... > Enabled, No updates pending Index-Name...... F-type K-type > Built Empties Dups In-DICT S/M F-no/VF-expr.... > PRIM.VEND.NUM D Txt Yes No Yes Yes S 69 > VEND.NUM D Num Yes No Yes Yes M 97 > PRIM.BIN.NUM V Txt Yes No Yes Yes S > EXTRACT(@RECORD, > 1,1,0) > > This set of statements returns correct results: > > >SELECT WAREHOUSE WITH PRIM.BIN.NUM <= "139" > 551453 records selected to list 0. > > >SELECT WAREHOUSE WITH PRIM.BIN.NUM >= "131" > 123 records selected to list 0. > > >SORT WAREHOUSE BY PRIM.BIN.NUM BREAK-ON PRIM.BIN.NUM TOTAL ICOUNT > >DET-SUPP > ID-SUPP > Primary Bin Count > 131 15 > 132 9 > 133 19 > 134 13 > 135 8 > 136 9 > 137 12 > 138 13 > 139 25 > ===== > TOTAL 123 > > But this set does not: > > >SELECT WAREHOUSE WITH PRIM.BIN.NUM >= "131" > 59131 records selected to list 0. > > >SELECT WAREHOUSE WITH PRIM.BIN.NUM <= "139" > 2114 records selected to list 0. > > >SORT WAREHOUSE BY PRIM.BIN.NUM BREAK-ON PRIM.BIN.NUM TOTAL ICOUNT > >DET-SUPP > ID-SUPP > Primary Bin Count > 131 15 > 132 9 > 133 19 > 134 13 > 135 8 > 136 9 > 137 12 > 138 13 > 139 25 > 14 2 > 15 17 > 16 32 > 17 26 > 18 24 > 19 15 > 2 23 > 20 21 > 21 25 > 22 23 > 23 20 > 24 20 > 25 16 > 26 13 > 27 15 > 28 21 > 29 36 > 3 35 > 30 33 > 31 8 > 32 5 > 33 6 > 34 6 > 35 23 > 36 35 > 4 30 > 40 12 > 41 26 > 42 17 > 43 13 > 44 18 > 45 13 > 46 7 > 47 15 > 48 15 > 49 14 > 5 31 > 50 18 > 51 10 > 52 8 > 53 13 > 54 18 > 55 12 > 56 13 > 57 9 > 58 11 > 59 13 > 6 28 > 60 12 > 61 5 > 62 8 > 63 15 > 64 22 > 65 4 > 66 19 > 67 10 > 68 12 > 69 6 > 7 26 > 70 21 > 71 30 > 72 26 > 73 12 > 74 11 > 75 12 > 76 16 > 77 18 > 78 47 > 79 37 > 8 29 > 80 39 > 81 38 > 82 54 > 83 54 > 84 61 > 85 16 > 86 25 > 87 41 > 88 27 > 89 46 > 9 28 > 90 48 > 91 58 > 92 45 > 93 48 > 94 22 > 95 15 > 96 16 > 97 17 > 98 16 > 99 15 > ===== > TOTAL 2114 > 2114 records listed > > Normally, we combine these into a single select, but that > doesn't work. > Both of these statements return the incorrect (larger) list: > > >SELECT WAREHOUSE WITH PRIM.BIN.NUM <= "139" AND WITH > PRIM.BIN.NUM >= "131" > >SELECT WAREHOUSE WITH PRIM.BIN.NUM >= "131" AND WITH > PRIM.BIN.NUM <= "139" > > I thought the index might be corrupt, so I tried rebuilding > it, but no luck. > > Any thoughts on how to correct this? I can't separate the > select statements. > > Please cc: me directly on replies, as I'm on digest. Thanks. > > --Tom Pellitieri > Century Equipment > Toledo, Ohio > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
