Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Not allowed a parameter
Fri, Nov 6 2009 7:36 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

I don't know which problem/enhancement request  area this falls in but can you make this work?

SELECT
_ID,
_fkContacts,
_fkStaff,
_InOutInd,
_ELNtype,
IF(_aList IS NOT NULL,TRUE,FALSE) AS _HasAttachments,
_Subject,
_Comments,
_Timestamp
FROM ELN
WHERE _fkContacts = :IDToMatch  
AND
_TimeStamp   >= CURRENT_TIMESTAMP - INTERVAL :Years YEAR <<<<<<<<<<<<<<<<<<<<
ORDER BY _timestamp DESC


ElevateDB Error #700 An error was found in the statement at line 14 and column 46 (Expected interval constant but instead found ?)

Roy Lambert
Sat, Nov 7 2009 11:28 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< I don't know which problem/enhancement request area this falls in but can
you make this work? >>

No, you need to be able to set the interval type in the client on this one,
and Delphi doesn't support interval types.  There's no way for EDB to figure
out the parameter type because it can literally be just about any interval
type when subtraction from a TIMESTAMP is involved.

BTW, the actual syntax would be this:

_TimeStamp >= CURRENT_TIMESTAMP - :Years

and you would set the data type to INTERVAL YEAR in Delphi, if this was
supported.  Unfortunately, TParam does not even allow one to set a native
data type/sub type, bypassing the TFieldType.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Nov 7 2009 12:33 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


>No, you need to be able to set the interval type in the client on this one,
>and Delphi doesn't support interval types. There's no way for EDB to figure
>out the parameter type because it can literally be just about any interval
>type when subtraction from a TIMESTAMP is involved.

I think either we have grossly miss-communicated here or you're talking gibberish Smiley

The parameter I want to set HAS (according to you from the standard) to be a quote surrounded integer literal

eg

_TimeStamp   >= CURRENT_TIMESTAMP - INTERVAL '1' YEAR

or

_TimeStamp   >= CURRENT_TIMESTAMP - INTERVAL '2' YEAR

and its the '1' or '2' I want as a parameter. One of us is wrong, probably me but if it is can you explain it so I can understand please.

Roy Lambert
Mon, Nov 9 2009 8:04 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< The parameter I want to set HAS (according to you from the standard) to
be a quote surrounded integer literal >>

Yes, if it is a constant literal, which it is not - it's a parameter.

<< and its the '1' or '2' I want as a parameter. >>

You can't do just the 1 or 2 as a parameter.  That's like saying that you
want to do this:

_TimeStamp >= TIMESTAMP :TimeStampParam

Literals are the only thing that require a modifier for dates, times,
timestamps, and intervals.  Parameters are assumed to already be set to the
proper type by EDB and/or the client application.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Nov 10 2009 2:45 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


I must be very think. There's no way my brain can say

>_TimeStamp >= TIMESTAMP :TimeStampParam

is the same as

_TimeStamp   >= CURRENT_TIMESTAMP - INTERVAL :Years YEAR

what you seem to be saying is that you have to treat  "INTERVAL :Years YEAR" as a single element. Why?

Roy Lambert
Fri, Nov 13 2009 12:38 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< must be very think. There's no way my brain can say

_TimeStamp >= TIMESTAMP :TimeStampParam

is the same as

_TimeStamp >= CURRENT_TIMESTAMP - INTERVAL :Years YEAR >>

Well, I meant just the constant portion, but yes, that is the comparison.

<<< What you seem to be saying is that you have to treat "INTERVAL :Years
YEAR" as a single element. Why? >>

Because :Years is a parameter hence its type is assigned in a binary fashion
via EDB and/or the client application.  Parameters never use any kind of
type identification or modifiers in the same way that constants do.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Nov 14 2009 4:19 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


After reading that several times I think I understand what you're saying. Shame cos it would have been a really useful feature for me.

Roy Lambert
Image