Using date values in a FetchData query string causes error

Owned by Mike Fechner
Dec 17, 2017
3 min read

Problem

Developer is experiencing error ** Incompatible data types in expression or assignment. (223) when performing a FetchData request to a Business Entity:

 

An Progress.Lang.AppError has occurred:
** Incompatible data types in expression or assignment. (223)
Prepare-String: FOR EACH ActiveCashier WHERE ActiveCashier.LogDate = 12 / 15 / 17 INDEXED-REPOSITION
Client-Query: FOR EACH eActiveCashier WHERE eActiveCashier.LogDate = 12/15/17
Default-Query: FOR EACH ActiveCashier INDEXED-REPOSITION

 

Fetch Data Request is constructed like

oRequest = NEW Consultingwerk.OERA.FetchDataRequest ("eActiveCashier", 
                                                     SUBSTITUTE ("FOR EACH eActiveCashier WHERE eActiveCashier.LogDate = &1",
                                                                 STRING(TODAY)), 
                                                     0).

oRequest = NEW Consultingwerk.OERA.FetchDataRequest ("eActiveCashier", 
                                                     SUBSTITUTE ("FOR EACH eActiveCashier WHERE eActiveCashier.LogDate = &1", 
                                                                 TODAY), 
                                                     0).

Solution

The recommended method of inserting any numeric or date value into a dynamic query string is the use of the QUOTER function:

oRequest = NEW Consultingwerk.OERA.FetchDataRequest ("eActiveCashier", 
                                                     SUBSTITUTE ("FOR EACH eActiveCashier WHERE eActiveCashier.LogDate = &1",
                                                                 QUOTER(TODAY)), 
                                                     0).

Progress Knowledge Base articles: