PH11331: TIMESTAMP FUNCTION NOT SUPPORTED IN FILTER

APAR status

• Closed as suggestion for future release.

Error description

• A report with a timestamp function in a detail filter is failing
  with "XQE-PLN-0098 The vendor specific function "timestamp" is
  not supported.". The timestamp function can be successfully used
  in other parts of the report, ie. a list.
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• This is not a defect. The observed error is expected in this
  scenario, i.e. this is design intent. There is a way to get rid
  of the error by modifying a model query subject definition.
  
  In the model from the in-house testcase, the database query
  sujbect [Datasource_great_outdoors_sales].[QS2] contains Cognos
  SQL which uses the "current_time" function for the query item
  ACTION_TIME.
  
      Select
          15.3 as ID_ACTION,
          15.3 FK_VISIT,
          current_time as ACTION_TIME
      from
          great_outdoors_sales...SYSIBM.DUAL
  
  Since this is in the context of Cognos SQL, the "current_time"
  function will be treated as the Cognos business date/time
  function
  
  https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com
  .ibm.swg.ba.cognos.ca_mdlg.doc/c_sql99_current_time.html#sql99_c
  urrent_time
  
  rather than the DB2 database function of the same name.
  
  The main difference between the two is that the Cognos
  "current_time" returns a time with timezone, whereas the
  database function returns a time without timezone.
  
  Since DB2 cannot return the current time with timezone, this
  will force CA to do some local processing for the query that has
  the filter against the ACTION_TIME item.
  And since this filter expression also contains a DB2 specific
  function ("timestamp"), this is why we report an error about a
  vendor-specific function not being supported. In other words,
  vendor specific functions are not supported when Cognos is
  locally processing a query.
  
  The real solution here is to avoid doing any local processing at
  all and have everything pushed to the database. There are two
  ways of accomplishing this.
  
  1) Use the SQL function "localtime" instead of "current_time" in
  the Cognos SQL:
  
      Select
          15.3 as ID_ACTION,
          15.3 FK_VISIT,
          localtime as ACTION_TIME
      from
          great_outdoors_sales...SYSIBM.DUAL
  
  The "localtime" will return a time value without time zone. In
  fact, the underlying native SQL sent to DB2 will actually use
  the DB2 function "current_time".
  
  or,
  
  2) Change the SQL type of the
  [Datasource_great_outdoors_sales].[QS2] query subject from
  "Cognos" to "Native" SQL. That way, the "current_time" will be
  treated as a vendor-specific function.
  Note that, when changing the SQL type from Cognos to native, the
  FROM clause (and possibly other expressions) will need to be
  updated, i.e.
  
  SELECT
      15.3 AS ID_ACTION,
      15.3 AS FK_VISIT,
      CURRENT_TIME AS ACTION_TIME
  FROM
      SYSIBM.DUAL
  
  
  **IMPORTANT NOTE**
  
  The above solution will fix the XQE-PLN-0098 error,  however the
  testcase will now fail at the database level due to incompatible
  datatypes used in the filter expression, i.e. time vs timestamp
  
      [Model_WCS].[QS2].[ACTION_TIME] >=
  timestamp('2007-09-24-15.53.37.2162474',9)
  
  This is likely an issue with the in-house testcase rather than
  the customer's testcase. Either way, you can get rid of this
  error by ensuring that ACTION_TIME has a data type of
  "timestamp". This can be done by updating the Cognos SQL in the
  query subject to use "localtimestamp" instead of "localtime":
  
      Select
          15.3 as ID_ACTION,
          15.3 FK_VISIT,
          localtimestamp as ACTION_TIME
      from
          great_outdoors_sales...SYSIBM.DUAL
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels