Please Ignore

Problem not with SQL .. but elsewhere



Adrian



-----Original Message-----
From: Adrian Wreyford [mailto:wreymed@...]
Sent: 10 April 2005 11:42 PM
To: IBObjects@yahoogroups.com
Subject: [IBO] Need help on proper syntax for IBOQuery SQL to return the
expected result please?



Dear all

Well I've tried many different iterations, but obviously missing something
quite basic here.

I'm setting up the SQL for a Query for a report in code.
The last two lines of code is where my problem is.

I want the result set to contain:
All ANIMALS with ANIMALSTATUS =FARM,
AND
ANIMALINSPECTIONDATE IS NULL
AND
All the FEMALE animals with DOB < sFDate,
AND
All the MALE animals with DOB < sMDate.
The query I set up as follows:

IBOQuery1.SQL.Add('Select * From ANIMAL');
IBOQuery1.SQL.Add('WHERE');
IBOQuery1.SQL.Add('ANIMALINSPECTIONDATE IS NULL');
IBOQuery1.SQL.Add('AND ANIMALSTATUS = ''FARM''');
IBOQuery1.SQL.Add('AND ((ANIMALGENDER = ''F'' AND ANIMALDOB <= ''' + sFDate
+ ''')');
IBOQuery1.SQL.Add('OR (ANIMALGENDER = ''M'' AND ANIMALDOB <= ''' + sMDate +
'''))');

This returns all the female animals that meet the criteria, but no male
animals!!

IF I change the last two lines to read:
IBOQuery1.SQL.Add('AND (ANIMALGENDER = ''F'' AND ANIMALDOB <= ''' + sFDate +
''')');
IBOQuery1.SQL.Add('OR (ANIMALGENDER = ''M'' AND ANIMALDOB <= ''' + sMDate +
''')');

Then I get all the female animals that adhere to the criteria, but every
male animal in the database, that is older than sMDate, irrespective of its
INSPECTIONDATE, or ANIMALSTATUS, but that is to be expected with the OR
where it is.

If this must be posted at Firebird rather than IBO please tell me???

Thanks in advance

Adrian







___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !




_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/IBObjects/


* To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com
<mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
<http://docs.yahoo.com/info/terms/> Service.



[Non-text portions of this message have been removed]