Consultor Eletrônico

Status: Unverified

GOAL:

What is the implication of STRING() function with Oracle DataServer - to use or not to use?

FIX:

To illustrate the question an example is presented below with the sports2000 database:

FOR EACH order WHERE STRING(orderdate) = "02/22/98":
DISPLAY orderdate shipdate promisedate.

FOR EACH order WHERE STRING(orderdate, "99/99/99") = "02/22/98":
DISPLAY orderdate shipdate promisedate.

When the STRING() function is used in the where clause, the DataServer translates it as a TO_CHAR() in the SQL it sends to ORACLE, and the string "02/22/98" will be passed exactly as it is. The NLS_DATE_FORMAT format is changed by the DataServer to 'YYYYMMDDHH24MISS' since this is the format it uses internally, therefore ORACLE won't be able to find the records using the query specified. The DataServer has no way of knowing that the string passed in the query is a date or anything else. So, the DataServer simply pass the TO_CHAR() function with the string specified in the code. When the format "99/99/99" is added, it works because then the query is resolved on the client side. All records are returned by the server and the the Progress client filters them out on the client side. When the STRING() is used, the DataServer passes it to the Oracle and then it's out of the Progresses' control. It is important to note that the above code is not portable even against a Progress Database when a session is started with a different date format (-d startup parameter). In this case the above query would not even work.

The right thing to do in this case is either:

1. not to use a date as a character string in the where clause (removing the STRING function) - use a date field so the DataServer passes the value following the format of the NLS_DATE_FORMAT;

2. if for any reason the string is needed to be used, use the DATE function on the string date as shown below:
FOR EACH order WHERE orderdate = DATE("02/22/98"):
DISPLAY orderdate shipdate promisedate.