RE: [U2] Selection Problem - Index?
>David A. Green <[EMAIL PROTECTED]> wrote on 09/11/2006 10:50:40 AM: > > Also try using BETWEEN like this: > :SELECT WAREHOUSE WITH PRIM.BIN.NUM BETWEEN "131" "139" > >Also a problem in ECLTYPE P - the specified strings are NOT included with >the BETWEEN keyword. Not if you lowercase your command: select WAREHOUSE WITH PRIM.BIN.NUM LIKE "3N" AND PRIM.BIN.NUM BETWEEN "131" "139" Thanks, David A. Green DAG Consulting --Tom Pellitieri Century Equipment --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Selection Problem - Index?
Thomas Derwin <[EMAIL PROTECTED]> wrote on 09/11/2006 10:18:29 AM: > The index says the "K-type" for your PRIM.BIN.NUM is "Txt", so looks > like the dict it's based on is left-justified (e.g. "3L") rather than > right-justified (e.g. "3R"). Colin Alfke <[EMAIL PROTECTED]> wrote on 09/11/2006 10:37:43 AM: > Make sure the dict for PRIM.BIN.NUM is set to "R#" ... Unfortunately, our "normal" Bin Numbers are alphanumeric (e.g., "A 6 B 2"), so the Text format is required. These "non-standard" bin numbers were inherited when we merged with another company. Even so, as I said in an earlier reply, why would this fail? >SELECT WAREHOUSE WITH PRIM.BIN.NUM >= "131" AND WITH PRIM.BIN.NUM <= "139" Whether you look at the data numerically or alphabetically, I shouldn't see bin numbers that don't begin with 13. == Allen Egerton <[EMAIL PROTECTED]> wrote on 09/11/2006 10:49:05 AM: > 3) If you want the combined select, I'd try: > SELECT WAREHOUSE WITH PRIM.BIN.NUM > 130 AND WITH PRIM.BIN.NUM < 140 We're in ECLTYPE P, which requires the quotes. Also, my users wouldn't understand why "13DWG" would be included in this selection. == David A. Green <[EMAIL PROTECTED]> wrote on 09/11/2006 10:50:40 AM: > Also try using BETWEEN like this: > :SELECT WAREHOUSE WITH PRIM.BIN.NUM BETWEEN "131" "139" Also a problem in ECLTYPE P - the specified strings are NOT included with the BETWEEN keyword. --Tom Pellitieri Century Equipment --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Selection Problem - Index?
Looks like PRIM.BIN.NUM is left justified instead of right? Try making the dictionary Right justified or pad the data with zeros. Also try using BETWEEN like this: :SELECT WAREHOUSE WITH PRIM.BIN.NUM BETWEEN "131" "139" 123 records selected to list 0. Thanks, David A. Green DAG Consulting -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sent: Monday, September 11, 2006 6:27 AM To: u2-users@listserver.u2ug.org 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 TxtYes No Yes Yes S 69 VEND.NUM D NumYes No Yes Yes M 97 PRIM.BIN.NUM V TxtYes 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 BinCount 131 15 1329 133 19 134 13 1358 1369 137 12 138 13 139 25 = TOTAL123 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 BinCount 131 15 1329 133 19 134 13 1358 1369 137 12 138 13 139 25 14 2 1517 1632 1726 1824 1915 2 23 2021 2125 2223 2320 2420 2516 2613 2715 2821 2936 3 35 3033 31 8 32 5 33 6 34 6 3523 3635 4 30 4012 4126 4217 4313 4418 4513 46 7 4715 4815 4914 5 31 5018 5110 52 8 5313 5418 5512 5613 57 9 5811 5913 6 28 6012 61 5 62 8 6315 6422 65 4 6619 6710 6812 69 6 7 26 7021 7130 7226 7312 7411 7512 7616 7718 7847 7937 8 29 8039 8138 8254 8354 8461 8516 8625 8741 8827 8946 9 28 9048 9158 9245 9348 9422 9515 9616 9717 9816 9915 = 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Selection Problem - Index?
Make sure the dict for PRIM.BIN.NUM is set to "R#" in the format position (<5>). Then rebuild your index. That will allow your numbers to sort as numbers and not as text strings. If you can have a combination of letters and numbers in your bin then you will have to update the dict to create a standard length for the numeric part. Hth Colin Alfke Calgary Canada >-Original Message- >From: TPellitieri > >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 TxtYes No Yes Yes S 69 >VEND.NUM D NumYes No Yes Yes M 97 >PRIM.BIN.NUM V TxtYes No Yes Yes S --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Selection Problem - Index?
Hi Tom, The index says the "K-type" for your PRIM.BIN.NUM is "Txt", so looks like the dict it's based on is left-justified (e.g. "3L") rather than right-justified (e.g. "3R"). That would suggest the select is using a string compare rather than a numeric one. Given that, I'm surprized the first select works. If all the bin numbers are numeric, you can switch the dict to right-justified, delete the old index, and create a new one. Another alternative is to zero-fill the bin numbers to, say 6 digits (e.g. "01"), so the string select will work. Hope this helps, Tom >>> [EMAIL PROTECTED] 09/11/06 9:27 AM >>> 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. PRIM.BIN.NUM V TxtYes 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 BinCount 131 15 1329 133 19 134 13 1358 1369 137 12 138 13 139 25 = TOTAL123 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 BinCount 131 15 1329 133 19 134 13 1358 1369 137 12 138 13 139 25 14 2 1517 1632 1726 1824 1915 2 23 2021 2125 2223 2320 2420 2516 2613 2715 2821 2936 3 35 3033 31 8 32 5 33 6 34 6 3523 3635 4 30 4012 4126 4217 4313 4418 4513 46 7 4715 4815 4914 5 31 5018 5110 52 8 5313 5418 5512 5613 57 9 5811 5913 6 28 6012 61 5 62 8 6315 6422 65 4 6619 6710 6812 69 6 7 26 7021 7130 7226 7312 7411 7512 7616 7718 7847 7937 8 29 8039 8138 8254 8354 8461 8516 8625 8741 8827 8946 9 28 9048 9158 9245 9348 9422 9515 9616 9717 9816 9915 = 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ - This e-mail and any attachments may contain CONFIDENTIAL information, including PROTECTED HEALTH INFORMATION. If you are not the intended recipient, any use or disclosure of this information is STRICTLY PROHIBITED; you are requested to delete this e-mail and any attachments, notify the sender immediately, and notify the LabCorp Privacy Officer at [EMAIL PROTECTED] or cal
RE: [U2] Selection Problem - Index?
I originally wrote: > 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. > ... > 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. (PRIM.BIN.NUM is indexed) Wol <[EMAIL PROTECTED]> wrote on 09/11/2006 09:41:07 AM: > Just a quick guess as to what's happening ... > The selects that fail are using the index and ignoring the > justification :-( I agree that the first select appears to be using the index alphabetically (and LIST.INDEX confirms the text index), while the second select uses the attribute numerically. That would explain the different results. However, it doesn't explain why 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" PRIM.BIN.NUM is a V-type field with a display format of 14L, so it should be treated as left-justified. Regardless, I would expect the joint selection should be either pure numeric or pure alphabetic! Hence, my frustration. Please note that since we're using SB+, we're in Pick Flavor. --Tom Pellitieri Century Equipment --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Selection Problem - Index?
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: u2-users@listserver.u2ug.org > 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 TxtYes No Yes Yes S 69 > VEND.NUM D NumYes No Yes Yes M 97 > PRIM.BIN.NUM V TxtYes 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 BinCount > 131 15 > 1329 > 133 19 > 134 13 > 1358 > 1369 > 137 12 > 138 13 > 139 25 >= > TOTAL123 > > 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 BinCount > 131 15 > 1329 > 133 19 > 134 13 > 1358 > 1369 > 137 12 > 138 13 > 139 25 > 14 2 > 1517 > 1632 > 1726 > 1824 > 1915 > 2 23 > 2021 > 2125 > 2223 > 2320 > 2420 > 2516 > 2613 > 2715 > 2821 > 2936 > 3 35 > 3033 > 31 8 > 32 5 > 33 6 > 34 6 > 3523 > 3635 > 4 30 > 4012 > 4126 > 4217 > 4313 > 4418 > 4513 > 46 7 > 4715 > 4815 > 4914 > 5 31 > 5018 > 5110 > 52 8 > 5313 > 5418 > 5512 > 5613 > 57 9 > 5811 > 5913 > 6 28 > 6012 > 61 5 > 62 8 > 6315 > 6422 > 65 4 > 6619 > 6710 > 6812 > 69 6 > 7 26 > 7021 > 7130 > 7226 > 7312 > 7411 > 7512 > 7616 > 7718 > 7847 > 7937 > 8 29 > 8039 > 8138 > 8254 > 8354 > 8461 > 8516 > 8625 > 8741 > 8827 > 8946 > 9 28 > 9048 > 9158 > 9245 > 9348 > 9422 > 9515 > 9616 > 9717 > 9816 > 9915 >= > 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
