|
Home > Archive > FoxPro Help and Support > May 2005 > index on two numeric field
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
index on two numeric field
|
|
|
| Hi
I have a table with two numeric field.(n,m)
I want index on both of field
I wrote index on n+m to book
But it don’t work what I must do I must numeric field to characteristic
field or no it don’t need to change
Thank you
| |
|
|
| Anders Altberg 2005-05-21, 1:24 pm |
| If the two fields are Integer you can use BINTOC(n) +BINTOC(m)
If they are numeric with or without decimals, use PADL(n, x1) + PADL(m, x2)
where x1 and x2 is the length of each field as defined. If n is B(6,2) x1 is
6.
-Anders
"bijan" <bijan@discussions.microsoft.com> wrote in message
news:3044087D-6EDF-4519-A540- 58176FE7E43B@microso
ft.com...
> Hi
> I have a table with two numeric field.(n,m)
> I want index on both of field
> I wrote index on n+m to book
> But it don’t work what I must do I must numeric field to characteristic
> field or no it don’t need to change
> Thank you
>
| |
|
| when i index on both fielf i got an errorit say allocateted arabic not find
what must i do
"Anders Altberg" wrote:
> If the two fields are Integer you can use BINTOC(n) +BINTOC(m)
> If they are numeric with or without decimals, use PADL(n, x1) + PADL(m, x2)
> where x1 and x2 is the length of each field as defined. If n is B(6,2) x1 is
> 6.
> -Anders
>
> "bijan" <bijan@discussions.microsoft.com> wrote in message
> news:3044087D-6EDF-4519-A540- 58176FE7E43B@microso
ft.com...
>
>
| |
|
| when i index on two field i got this message
allocated Arabic not found
what must i do
"Anders Altberg" wrote:
> If the two fields are Integer you can use BINTOC(n) +BINTOC(m)
> If they are numeric with or without decimals, use PADL(n, x1) + PADL(m, x2)
> where x1 and x2 is the length of each field as defined. If n is B(6,2) x1 is
> 6.
> -Anders
>
> "bijan" <bijan@discussions.microsoft.com> wrote in message
> news:3044087D-6EDF-4519-A540- 58176FE7E43B@microso
ft.com...
>
>
| |
|
| sorry i got this message
collating sequence 'Arabic' not found
what must i do
thank you
"bijan" wrote:
[color=darkred]
> when i index on two field i got this message
> allocated Arabic not found
> what must i do
>
> "Anders Altberg" wrote:
>
| |
| Anders Altberg 2005-05-22, 1:23 pm |
| If you want to use Arabic sort order for your index you need to be using a
Windows version that supports Arabic. Arabic Windows is code page 1256.
Please see the explanation in Visual FoxPro's Help: Error Messages
alphabetic order. Look for 'Collating sequence 'name' not found' It's error
1915.
-Anders
"bijan" <bijan@discussions.microsoft.com> wrote in message
news:9B092782-F3DB-4444-A618- 3C52CDE74FDC@microso
ft.com...[color=darkred]
> sorry i got this message
> collating sequence 'Arabic' not found
> what must i do
> thank you
>
> "bijan" wrote:
>
PADL(m, x2)[color=darkred]
x1 is[color=darkred]
characteristic[color
=darkred]
| |
| Leonid 2005-05-23, 9:24 am |
| I doubt that BINTOC(n) +BINTOC(m) may be used with Arabic (or any other than
'Machine') collating sequence. Or I'm wrong?
Leonid
"Anders Altberg" <x_pragma@telia.com> wrote in message
news:OaQom7uXFHA.1384@TK2MSFTNGP09.phx.gbl...
> If you want to use Arabic sort order for your index you need to be using a
> Windows version that supports Arabic. Arabic Windows is code page 1256.
> Please see the explanation in Visual FoxPro's Help: Error Messages
> alphabetic order. Look for 'Collating sequence 'name' not found' It's
> error
> 1915.
> -Anders
>
> "bijan" <bijan@discussions.microsoft.com> wrote in message
> news:9B092782-F3DB-4444-A618- 3C52CDE74FDC@microso
ft.com...
> PADL(m, x2)
> x1 is
> characteristic
>
| |
| Anders Altberg 2005-05-23, 1:24 pm |
| Hi Leonid,
The two fields are numeric. I don't see how bintoc() can require Arabic
collation. Any clues.
-Anders
"Leonid" <leonid@NOgradaSPAM.lv> wrote in message
news:#AZbRG6XFHA.252@TK2MSFTNGP12.phx.gbl...
> I doubt that BINTOC(n) +BINTOC(m) may be used with Arabic (or any other
than
> 'Machine') collating sequence. Or I'm wrong?
>
> Leonid
>
>
> "Anders Altberg" <x_pragma@telia.com> wrote in message
> news:OaQom7uXFHA.1384@TK2MSFTNGP09.phx.gbl...
a[color=darkred]
>
>
| |
| Leonid 2005-05-24, 7:24 am |
| Hi, Anders
Try
set collate to "General"
create cursor tmp (f1 I)
for m.i=0 to 255
insert into tmp values (m.i)
endfor
index on bintoc(f1) tag f1
browse
Leonid
"Anders Altberg" <x_pragma@telia.com> wrote in message
news:uuObH27XFHA.2884@tk2msftngp13.phx.gbl...
> Hi Leonid,
> The two fields are numeric. I don't see how bintoc() can require Arabic
> collation. Any clues.
> -Anders
>
> "Leonid" <leonid@NOgradaSPAM.lv> wrote in message
> news:#AZbRG6XFHA.252@TK2MSFTNGP12.phx.gbl...
> than
> a
>
| |
| Olaf Doschke 2005-05-24, 11:24 am |
| > The two fields are numeric. I don't see how bintoc() can require Arabic
> collation. Any clues.
a) bintoc() requires MACHINE collating sequence
for correct sorting/seeking of indexed values.
See Leonids example for what happens if
the collating sequence is not MACHINE.
b) Maybe INDEX ON doesn't work with
collating sequence ARABIC?! That may be
totally independant of the index expression.
If I do SET COLLATE TO "arabic" I get
the error message, because this collating
sequence is not installed.
This may work:
SET COLLATE TO MACHINE
INDEX ON BINTOC(n)+BINTOC(m) TO Book
That is, if you intend on creating an IDX index.
IDX indexes won't open automatically with the
table and won't be updated if not open!
A CDX index is recommended, therefore:
SET COLLATE TO MACHINE
INDEX ON BINTOC(n)+BINTOC(m) TAG Book
Of yourse the expression may vary depending on
the field types, you got some recommendations.
VFP9 has extended BINTOC() to also work with
numeric,float,double
and currency. Previous VFP
versions only support integer.
http://msdn.microsoft.com/library/d...b619ff83ad6.asp
(You may need to copy the lines of this broken link
into one line, before using as URL)
Bye, Olaf.
| |
| Leonid 2005-05-25, 3:24 am |
| There are some problems using indexes with different collating sequnces for
one table:
set collate to "Machine"
create cursor tmp (f1 I, f2 I, f3 C(1))
for m.i=0 to 255
insert into tmp values (m.i, m.i, chr(m.i))
endfor
index on bintoc(f1)+bintoc(f2
) tag t1 additive
index on deleted() tag t2 additive
set collate to "General"
index on f3 tag t3 additive
sys(3054,1)
select * from tmp where bintoc(f1)+bintoc(f2
)> bintoc(100)+bintoc(1
00) and
f3<chr(200) into cursor tmp1
browse
set collate to "Machine"
select * from tmp where bintoc(f1)+bintoc(f2
)> bintoc(100)+bintoc(1
00) and
f3<chr(200) into cursor tmp1
browse
sys(3054,0)
Both select statements give wrong results: in first one
bintoc(f1)+bintoc(f2
)> bintoc(100)+bintoc(1
00) is treated in "General"
collating sequence, but in second f3<chr(200) is treated in "Machine"
collating sequence. And both select statements are not fully optimazible.
So, IMO it's better to use only one collating sequence and if it's not
"Machine", then better not to use bintoc for indexing
Leonid
"Olaf Doschke" < T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14> wrote in message
news:eOv69rHYFHA.2508@TK2MSFTNGP15.phx.gbl...
>
> a) bintoc() requires MACHINE collating sequence
> for correct sorting/seeking of indexed values.
> See Leonids example for what happens if
> the collating sequence is not MACHINE.
>
> b) Maybe INDEX ON doesn't work with
> collating sequence ARABIC?! That may be
> totally independant of the index expression.
>
> If I do SET COLLATE TO "arabic" I get
> the error message, because this collating
> sequence is not installed.
>
> This may work:
>
> SET COLLATE TO MACHINE
> INDEX ON BINTOC(n)+BINTOC(m) TO Book
>
> That is, if you intend on creating an IDX index.
> IDX indexes won't open automatically with the
> table and won't be updated if not open!
>
> A CDX index is recommended, therefore:
>
> SET COLLATE TO MACHINE
> INDEX ON BINTOC(n)+BINTOC(m) TAG Book
>
> Of yourse the expression may vary depending on
> the field types, you got some recommendations.
>
> VFP9 has extended BINTOC() to also work with
> numeric,float,double
and currency. Previous VFP
> versions only support integer.
>
> http://msdn.microsoft.com/library/d...b619ff83ad6.asp
>
> (You may need to copy the lines of this broken link
> into one line, before using as URL)
>
> Bye, Olaf.
>
| |
| Stefan Wuebbe 2005-05-25, 8:25 pm |
| Hi Leonid,
"Leonid" <leonid@NOgradaSPAM.lv> schrieb im Newsbeitrag
news:%23pf0NwPYFHA.3132@TK2MSFTNGP09.phx.gbl...
> There are some problems using indexes with different collating sequnces for one
> table:
I don't think so. When you add another index with an optimizable
collate setting, Rushmore will use it instead of the other one (indexing
the same expression)
...
INDEX on f3 TAG f3machine ADDITIVE
set collate to "General"
index on f3 tag t3 additive
...
Regards
-Stefan
--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------
| |
|
| thank you
i used bintoc function and i could index on two integer field
and it worked good
but when i seek to find for example 33 it dont find it
| |
| Stefan Wuebbe 2005-05-27, 3:24 am |
|
"bijan" <bijan@discussions.microsoft.com> schrieb im Newsbeitrag
news:287BBEAF-D99F-49C1-84DF- 92127E667C6D@microso
ft.com...
> thank you
> i used bintoc function and i could index on two integer field
> and it worked good
> but when i seek to find for example 33 it dont find it
Seek() requires an expression matching type and content of a given
index tag. So when you have got an index tag on BinToC(), you'd
Seek( BinToC(33) )
You can add another tag, indexing the same field on plain integer for
example, to be able to Seek(33)
hth
-Stefan
--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------
| |
| Olaf Doschke 2005-05-27, 7:24 am |
| > Seek() requires an expression matching type and content of a given
> index tag. So when you have got an index tag on BinToC(), you'd
> Seek( BinToC(33) )
> You can add another tag, indexing the same field on plain integer for
> example, to be able to Seek(33)
Before seeking you also should set the collating sequence to the
sequence the index is defined in. And you may even not find
a record with seek bintoc(33) if the index is on bintoc(id1)+bintoc(i
d2)
and EXACT is set on, because you then only seek a partial
expression, which will find nothing, as it isn't even the correct
length for an exact match with the index.
To find a single id simply have the single fields indexed too:
set collate to machine
index on id1 tag id1
index on id2 tag id2
index on bintoc(id1)+bintoc(i
d2) tag id1id2
set collate to machine
set exact off
* these would both work then:
?seek(33,"alias","id1")
?seek(bintoc(33),"alias","id1id2")
set collate to machine
set exact on
* now only the first seek would find something:
?seek(33,"alias","id1")
?seek(bintoc(33),"alias","id1id2")
Bye, Olaf.
| |
| Leonid 2005-05-27, 7:24 am |
| Hi Stefan,
Yes, in this case query will be optimazable, but the problem is that it will
treat inequality f3<chr(200) in "Machine" collating sequence, while I want
it to treat it in "General" collating sequence (in alphabetical order). The
query may be divided into two parts:
set collate to "Machine"
select * from tmp where bintoc(f1)+bintoc(f2
)> bintoc(100)+bintoc(1
00) cursor
tmp1
set collate to "General"
select * from tmp1 where f3<chr(200) into cursor tmp2
or
set collate to "General"
select * from tmp where f3<chr(200) into cursor tmp1
set collate to "Machine"
select * from tmp1 where bintoc(f1)+bintoc(f2
)> bintoc(100)+bintoc(1
00)
cursor tmp2
But in both cases if tmp1 is large, performance will be poor
Leonid
"Stefan Wuebbe" <stefan.wuebbe@gmx.de> wrote in message
news:eZ0sf8VYFHA.2684@TK2MSFTNGP09.phx.gbl...
> Hi Leonid,
>
> "Leonid" <leonid@NOgradaSPAM.lv> schrieb im Newsbeitrag
> news:%23pf0NwPYFHA.3132@TK2MSFTNGP09.phx.gbl...
>
> I don't think so. When you add another index with an optimizable
> collate setting, Rushmore will use it instead of the other one (indexing
> the same expression)
> ...
> INDEX on f3 TAG f3machine ADDITIVE
> set collate to "General"
> index on f3 tag t3 additive
> ...
>
>
> Regards
> -Stefan
>
>
> --
> |\_/| ------ ProLib - programmers liberty -----------------
> (.. ) Our MVPs and MCPs make the Fox run....
> - / See us at www.prolib.de or www.AFPages.de
> -----------------------------------------------------------
>
| |
| bijan 2005-05-27, 11:24 am |
| thank you from all person answer me.
but it is reallu bad for a graet database that cant not index on two numeric
filed and we must use function to index it .
anybody can sent a message to visual foxpro and say whay they try to make it
easy to index on two numeric field excel can do it very easy.
thank you again
| |
| Villi Bernaroli 2005-05-30, 9:24 am |
| Mi e' parso che bijan abbia scritto:
> anybody can sent a message to visual foxpro and say whay
> they try to make it easy to index on two numeric field
> excel can do it very easy.
Ordering is one thing, indexing is another.
AFAIK, Excel orders.
--
The answer to the ethernal question is:
put a read events after the launch of the form
|
|
|
|
|