Re: sql help

["tim" <tim2006@xxxxxxxxxxx>]

Thanks Jason, 

I will keep that in mind. 

I was able to get it to work as follows (fyi)

ts timeStamp = timestampFrom(datetimelib.dateValue(datein),
timevalue(timein));
comment comment[];   	
    	
get comment
  with #sql{
            select cmtclaim, cmtdate, cmttime, cmtcomm, cmtuser, cmtprior,
cmttype
     			from NTFILES.CLMNOTESL2
     			WHERE 
       		     cmtclaim =: claim and timestamp(cmtdate, cmttime) <=
:ts 
     		                 };



-----Original Message-----
From: egl-i-bounces@xxxxxxxxxxxx [mailto:egl-i-bounces@xxxxxxxxxxxx] On
Behalf Of Jason Abreu
Sent: Friday, March 13, 2009 8:17 AM
To: egl-i@xxxxxxxxxxxx
Subject: Re: [EGL-i] sql help

EGL SQL does not support everything that SQL can do.
For instance, it doesn't support the WITH clause
(WITH table1 as ..., table2 as ..., etc).
So the #sql in EGL may not support the timestamp function.

I believe the best solution would be to use a prepared statement.
That's all I use any more because of the control I have over the SQL 
statement.

If you have any questions, feel free to email me directly.

Try this:

prepare myStmt from
"select cmtclaim, cmtdate, cmttime, cmtcomm, cmtuser, cmtprior, " +
"cmttype from NTFILES.CLMNOTESL2 where cmtclaim = ? and " +
"timestamp(cmtdate, cmttime) <= timestamp(?, ?) ";

try
   get comment with myStmt using claimn, datein, timein;
onException (exc AnyException)
   //process exception
end

--Jason

tim wrote:
> Im trying to do the following, but it doesn't seem to like it
>
>  
>
> get comment with #sql{
>
> select cmtclaim, cmtdate, cmttime, cmtcomm, cmtuser, cmtprior, cmttype
>
> from NTFILES.CLMNOTESL2
>
> WHERE cmtclaim =: claimn and
>
>           timestamp(cmtdate,cmttime) <= timestamp(:datein,:timein)
>
> };
>
>  
>
> Im getting message:
>
>  
>
> java.rmi.RemoteException: EGL0504E GET: [SQL0418] Use of parameter marker
> not valid.[sqlstate:42610][sqlcode:-418] 
>
>  
>
>  
>
> any suggestions?