Re: SQL Query over I-STAR

In <C7n5nE.Kr4@vcd.hp.com> kenm@vcd.hp.com (Ken Miles) writes: >In SQL Query over I-STAR I wrote: >> >> select * from remote_rate >> where >> counter_id = 61 and >> timestamp > (current year to minute - interval(30) minute to minute) >> and I get: >> 1261: Too many digits in the first field of datetime or interval. >I turned on set explain and got this: >QUERY: >------ >select * from cope_rate > where > counter_id = 61 and > timestamp > (current year to minute - interval(30) minute to minute) >Estimated Cost: 1 >Estimated # of Rows Returned: 1 >1) cope_rate: REMOTE PATH > Remote SQL Request: > select x0.counter_id ,x0.timestamp ,x0.duration ,x0.shift ,x0.qu > antity from printer_prod:"gap".rate x0 where ((x0.counter_id = 6 > 1 ) AND (x0.timestamp > (? - ' 30' ) ) )> >Looks like maybe "CURRENT" isn't getting across the network? No, not necessarily. The "?" is a placeholder (as in PREPARE). This means that the local engine intends to resolve CURRENT and pass it as a value to the assistant engine to use when it EXECUTEs. I suspect you may be hitting some kind of datatype conversion problem in the way that value resolution gets handled. But at which end??? It's the fact that the 30 gets sent as a character string rather than an interval that looks highly suspicious to me. I suspect that the remote engine is being asked to resolve "datetime_column < (numeric_constant - character_string)" in which case I am not surprised it is coughing. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.