<< Click to Display Table of Contents >> Navigation:  The Vo2Ado RDD > Indexes > Index expression examples

Example 1

 

 USE CUSTOMER

 INDEX ON LASTNAME TO LASTNAME

 SET SCOPETOP TO 'H'

 SET SCOPEBOTTOM TO 'H'

 GO TOP

 ? CUSTOMER->LASTNAME

 

Because the index expression is simple the following index will be created:

 

 CREATE INDEX CUSTOMER_LASTNAME ON CUSTOMER(LASTNAME)

 

The syntax of the CREATE INDEX command can be set through a Callback method

 

 

When applying the Scopes the RDD will call back into your application, to get the replacement for the SUBSTR(%1%, %2%, %3%) function for your server. Assuming you are returning SUBSTRING(%1%,%2%,%3%) this will lead to the following SQL statement:

 

 SELECT * FROM CUSTOMER

 WHERE SUBSTRING(LASTNAME,1,1) >= 'H'

 AND SUBSTRING(LASTNAME,1,1) <= 'H'

 ORDER BY LASTNAME

 

Example 2

 

 USE CUSTOMER

 INDEX ON UPPER(LASTNAME+FIRSTNAME) TO NAME

 SEEK "HULST"

 ? CUSTOMER->LASTNAME

 

When parsing the index key, the RDD will call back into your application to get the repacement for UPPER(%1%). Lets assume you are returning UPPER(%1%) unmodified. When applying the Seek the RDD will again call back into your application, to get the replacement for the SUBSTR(%1%, %2%, %3%) function for your server. Assuming you are returning SUBSTRING(%1%,%2%,%3%) this will lead to the following SQL statement:

 

 SELECT * FROM CUSTOMER

 WHERE SUBSTRING(UPPER(LASTNAME+FIRSTNAME),1,5) = 'HULST'

 ORDER BY UPPER(LASTNAME+FIRSTNAME)

 

If you would be running on a case-insensitive server you could force the RDD to create an index by replacing the UPPER(%1%) function in your callback method with %1%. In that case the following SQL statements would be generated

 

    CREATE INDEX CUSTOMER_NAME ON CUSTOMER (LASTNAME, FIRSTNAME)

    SELECT * FROM CUSTOMER

    WHERE SUBSTRING(LASTNAME+FIRSTNAME,1,5) = 'HULST'

    ORDER BY LASTNAME+FIRSTNAME

 

 

Example 3

 

 

When parsing the index key, the RDD will call back into your application to get the repacement for STR(%1%,%2%,%3%). Lets assume you are returning STR(%1%,%2%,%3%) unmodified. This will lead to the following SQL statement:

 

 

Note

For a DBF workarea you could achieve the same results (assuming the INVOICE_NO field has a size of 10 and 0 decimals) by using the index expression STR(INVOICE_NO) or STR(INVOICE_NO,10). This is because VO will read the Field Size and Decimal information from the DBF when indexing and will generate as string of 10 characters and without decimals. You should not rely on this when indexing a SQL environment. One way to get around that without changing your sourcecode would be to replace the STR(%1%,%2%) function with STR(%1%, %2%, 0), but a similar replacement for the field size will be much more difficult.

 

Example 4

 

 USE INVOICE

 INDEX ON STR(CUSTOMERID,10,0) + DTOS(INV_DATE) TO INV_DATE

 SEEK STR(100,0)+"20010331"

 

When parsing the index key, the RDD will be smart enough to discover that you are only calling the STR() function and the DTOS() function to concatenate the two columns. So it will generate an index in this case:

 

 CREATE INDEX INVOICE_INVOICE_DATE ON INVOICE (CUSTOMERID, INVOICE_DATE)

 

But when doing the seek, the RDD will call back into your application to get the repacement for STR(%1%,%2%,%3%) and DTOS(%1%). Lets assume you are returning STR(%1%,%2%,%3%) and Convert(Char,%1%,112)

This will lead to the following SQL statement:

 

 SELECT * FROM INVOICE

 WHERE STR(CUSTOMERID,10,0)+Convert(Char,INVOICE_DATE,112) = '       10020010331'