Using iSeries v5r4, and soon to be v6r1, I receive SQL0583 "Use of function &1 in &2 not valid" when running an SQL statement that includes the use of a function, defined as NOT DETERMINISTIC, in a join. Because the code is a warning, 583 = SQLSTATE 01685, the SQL statement does run and I verify that the results are correct.
While I can and will rewrite this particular SQL, can anyone tell me what usage results in the SQL code being informational (583 = SQLSTATE 01685) vs. an exception (-583 = SQLSTATE 42845) or how I might find that out?
Also note that when this SQL is used in a view and then SELECT * from view, no warning is presented. Although I don't think I'd interpret that to mean the usage within a view is supported any more than outside of a view. Note that the SQL (run independently from the view) returns the correct results quickly while the view is terribly slow (hours compared to a few minutes).