I am trying to do a Crystal sql que

[LMCRYER]

I am trying to do a Crystal sql query and it is driving me absolutely
bonkers becuase I cant see what the heck I am doing wrong. I have attached
the query and I have tested out each piece - it craps out when it gets to
the LAST AND line -- AND PROBLEM."STOPDATE" >= NOW()

The error message is telling me "invalid column name" -

The problem.stopdate field is a datetime field....any ideas? I'm stumped.
Any ideas are really welcome. I do realize this could and should be in a
stored procedure, but circumstances dont allow that -- (ultra paranoid tech
department)


SELECT DISTINCT
PERSON."PID", PERSON."LASTNAME", PERSON."FIRSTNAME",
PERSON."DATEOFBIRTH", PERSON."SEX", PERSON."RACE",
PERSON."EXTERNALID"
FROM
"ML"."PERSON" PERSON
WHERE
PERSON."PID" in
(SELECT DISTINCT
PROBLEM."PID"
FROM
"ML"."PROBLEM" PROBLEM
WHERE
PROBLEM.&quot;CODE&quot; >= 'ICD-250.00' AND PROBLEM.&quot;CODE&quot; <= 'ICD-250.00'
******** this is where it stops working -
AND PROBLEM.&quot;STOPDATE&quot; >= NOW())
*******
ORDER BY
PERSON.&quot;PID&quot; ASC


LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[PaulDeshayes]

I assume that your query works without this line :
AND PROBLEM.&quot;STOPDATE&quot; >= NOW())

Well, if the field problem.stopdate exists, then the problem occurs probably because of NOW().

I don't know what database you're using, but maybe you could try to replace Now() by problem.stopdate (just to test...), Now, or Getdate, or Getdate(), or sysdate or sysdate().

Good luck !
/Paul

 

[LMCRYER]

This was resolved by doing this:


PROBLEM.STOPDATE>TO_DATE(sysdate,'DD-MM-YYYY')

I have no idea why its so darn fussy, but it is - and it works! Totally made my Monday!

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned