Please ignore .. problem not with SQL, but elsewhere.
Adrian

-----Original Message-----
From: Adrian Wreyford [mailto:wreymed@...]
Sent: 10 April 2005 11:50 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] 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.

Thanks in advance

Adrian




Yahoo! Groups Links