Checking if specific RecID is in Cursor

[Beatrix Willius]

Hi everybody,

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?

Is there a faster/easier way then simply looping through the table?

Mit freundlichen Grüßen/Regards

Trixi Willius

http://www.mothsoftware.com
Mail Archiver X: archive, clean and search email
http://www.beatrixwillius.de
Fractals, 3d landscapes etc.