Checking if specific RecID is in Cursor

[Ruslan Zasukhin]

On 24/5/10 12:31 PM, "Beatrix Willius" <bwillius at gmx.de> wrote:

Hi Beatrix ,
 
> I'm trying to check if a specific RecID is in a Cursor. The cursor can have
> the following form:
> 
> SELECT Message.MailboxID, Message.RecID, Message.OriginatorFrom as [From],
> Message.ReceiverTo as [To], Message.MainSubject as [Subject],
> Message.MainDateTime as [Date], Message.AttachmentCount as [Att.],
> MailboxName(Mailbox.Mailboxpath) as [Mailbox]
> 
> FROM Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID
> 
> WHERE Message.MailboxID = :1
> 
> GROUP BY Message.MailboxID, Message.RecID, Message.OriginatorFrom,
> Message.ReceiverTo, Message.MainSubject, Message.MainDateTime,
> Message.AttachmentCount, Mailbox.MailboxPath
> 
> HAVING RecID in (select RecID from Message where Message.MessageBody like
> '%the%') 
> 
> ORDER By [Date] ASC
> 
> I thought the easiest way to do this is to simple wrap the existing SQL with
> (excluding the where for testing in VStudio)
> 
> select recid from (
> SELECT Message.MailboxID, Message.RecID, Message.OriginatorFrom as [From],
> Message.ReceiverTo as [To], Message.MainSubject as
>  [Subject], Message.MainDateTime as [Date], Message.AttachmentCount as [Att.],
> MailboxName(Mailbox.Mailboxpath) as [Mailbox]
>  
> FROM Message INNER JOIN Mailbox ON Message.MailboxID = Mailbox.RecID GROUP BY
> Message.MailboxID,
>  Message.RecID, Message.OriginatorFrom, Message.ReceiverTo,
> Message.MainSubject, Message.MainDateTime, Message.AttachmentCount,
>  Mailbox.MailboxPath
> 
> HAVING RecID in (select RecID from Message where Message.MessageBody like
> '%the%') 
> 
> ORDER By [Date] ASC)
> 
> This, however, gives an error 71000 (expecting ")" found "order"). If I remove
> the order clause it works. However, I really don't want to muck around with
> the original SQL. Is this a bug or a feature?

I think this is by SQL standard.

And as I remember, we have in mantis request to relax this from Igor
Nikitin.  He also want this in Vstudio sources.

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]