SQL and Crystal Reports

[rydexdba]

I am trying to have the following MSSQL 7 query show up in Crystal Reports:

select t.territory_name, c.company_name, co.city
from company c, contact co, territory t
where c.company_id = co.company_id
and co.territory_id = t.territory_id
and co.priority = '2A'
and co.date_2a >= '2001-04-20' and co.date_2a < '2001-04-22'
and ((co.date_2a >= isnull(co.date_1a, '2001-04-20')) and
(co.date_2a >= isnull(co.date_1b, '2001-04-20')) and
(co.date_2a >= isnull(co.date_2b, '2001-04-20')) and
(co.date_2a >= isnull(co.date_3a, '2001-04-20')) and
(co.date_2a >= isnull(co.date_3b, '2001-04-20')) and
(co.date_2a >= isnull(co.date_3c, '2001-04-20')) and
(co.date_2a >= isnull(co.date_3d, '2001-04-20')) and
(co.date_2a >= isnull(co.date_3v, '2001-04-20')))
order by co.date_2a desc


I can set &quot;co.date_2a >= co.date_Xy&quot; just fine. It's just that the isnull function in MSSQL server does not work the same way in Crystal Reports. Can anybody offer any suggestions on how I could put the above query in Crystal Reports? Any help will be greatly appreciated. Thanks!

 

[MalcolmW]

You could edit the SQL statement directly, but you would have to change your syntax to standard ANSI SQL.
Or you could add a record selection statement in the form of
(
If IsNull({co.date_1a}) then
{co.date_2a} >= Date(2001,4,20)
Else
{co.date_2a} >= {co.date_1a}
) and...
Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net