For example, one of the index tags on this project uses the following index expression:
INDEX ON normalizeTicket(Ticket_No)+str(RecNo()) TAG TicketNo TO CHARGES.CDX
ISAM Smart Order feature of PGDBE seemed to handle this just fine, however there appears to be major performance issues.
Therefore, it may be necessary to use a SQL statement that uses a stored procedure call in the ORDER BY clause.
The PostGreSql documentation states that stored procedures to not return a value, so I don't know how this would work.
The documentation states that stored functions are available. Is this the proper strategy?
Code: Select all
FUNCTION normalizeTicket(xTic)
RETURN upper(left(strip(xTic)+space(10),10))
FUNCTION Strip( in_str, cExtra )
// syntax strip(<c>) returns uppercase void of spaces and punctuation
LOCAL x,y,z, xJunk:='- .,/;\|()&'
IF empty(in_str)
RETURN("")
ENDIF
IF valtype(cExtra) == "C" .AND. !empty(cExtra)
xJunk += cExtra
ENDIF
in_str := alltrim(in_str)
z := ''
x:=len(in_str)
FOR y := 1 TO x
// z += IIF(substr(in_str,y,1)$'- .,/;\|()&',"",substr(in_str,y,1)) // strip out the junk
z += IIF(in_str[y]$xJunk,"",in_str[y]) // strip out the junk
NEXT
RETURN(upper(z))