cpwrs


I am tring to use this tsql query in a sql 2005 report it keeps giving me the error below. This query works in my ms sql 2000 ok but I keep getting this error in the 2005. Can some one look at this and tell me what I am doing wrong. Thank you.

There is an error in the query. Incorrect syntax near '('.

SELECT
DISTINCT
JOB.JOBID,
JOB.*,
PAYER.PAY_COMPANY,
PATIENT.SSN,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLENAME,
PATIENT.AM_PHONE,
PATIENT.EMAIL,
JOB_OUTCOME.DESCRIPTION AS 'JOB_OUTCOME_DESC'

FROM
(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)
( LEFT JOIN PAYER ON JOB.PAYERID = PAYER.PAYERID)
( LEFT JOIN REFERRAL_SOURCE AS ADJUSTER ON JOB.ADJUSTER.REFERRAL_ID)
( LEFT JOIN REFERRAL_SOURCE AS CM ON JOB.CASEMANAGERID = CM.REFERRAL_ID)
( LEFT JOIN JOB_OUTCOME ON JOB.JOBOUTCOMEID = JOB_OUTCOME_ID)

WHERE
(JOB.APPT_DATE BETWEEN @STARTDATE AND @ENDDATE)

AND (JOB.TRANSPORTATION = '1')

AND (AREACOORDINATOR = '138')

ORDER BY
JOB.APPT_DATE DESC;




Re: Newbie Need help with tsql query

Arnie Rowland


In this line

FROM
(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)

there are five (5) opening parentheses, but ONLY one of them is closed.

This could NOT have worked in SQL 2000 either.

Please post the ENTIRE query so that we may better assist you.







Re: Newbie Need help with tsql query

cpwrs

Sorry I pasted the wrong one The 5 ( are becuase the opens before the left look at it again. This works just fine in sql 2000 in a cold fusion application I am using this in sql 2005 with a asp.net application.

There is an error in the query. An expression of non-boolean type specified in a context where a condition is expected, near ')'.


SELECT
DISTINCT
JOB.JOBID,
JOB.*,
PAYER.PAY_COMPANY,
PATIENT.SSN,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLENAME,
PATIENT.AM_PHONE,
PATIENT.EMAIL,
JOB_OUTCOME.DESCRIPTION AS 'JOB_OUTCOME_DESC'

FROM
(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)
LEFT JOIN PAYER ON JOB.PAYERID = PAYER.PAYERID)
LEFT JOIN REFERRAL_SOURCE AS ADJUSTER ON JOB.ADJUSTER.REFERRAL_ID)
LEFT JOIN REFERRAL_SOURCE AS CM ON JOB.CASEMANAGERID = CM.REFERRAL_ID)
LEFT JOIN JOB_OUTCOME ON JOB.JOBOUTCOMEID = JOB_OUTCOME_ID)

WHERE
((JOB.APPT_DATE >= '02/19/2007' AND ( JOB.APPT_DATE <= '02/19/2007'))

AND (JOB.TRANSPORTATION = '1')

AND (AREACOORDINATOR = '138')

ORDER BY
JOB.APPT_DATE DESC;






Re: Newbie Need help with tsql query

Arnie Rowland

Your parentheses are still not correct. This is missing a closing parenthesis.

((JOB.APPT_DATE >= '02/19/2007' AND ( JOB.APPT_DATE <= '02/19/2007'))

AND (JOB.TRANSPORTATION = '1')

AND (AREACOORDINATOR = '138')






Re: Newbie Need help with tsql query

Louis Davidson

The problem (once you get your parenthesis straighten out Smile is here:

(((((JOB LEFT JOIN PATIENT ON JOB.PATIENTID = PATIENT.PATIENTID)
LEFT JOIN PAYER ON JOB.PAYERID = PAYER.PAYERID)
LEFT JOIN REFERRAL_SOURCE AS ADJUSTER ON JOB.ADJUSTER.REFERRAL_ID)
LEFT JOIN REFERRAL_SOURCE AS CM ON JOB.CASEMANAGERID = CM.REFERRAL_ID)
LEFT JOIN JOB_OUTCOME ON JOB.JOBOUTCOMEID = JOB_OUTCOME_ID)



You need to have JOB.ADJUSTER.REFERRAL_ID = REFERRAL_SOURCE..

Or something along these lines.

I would just get rid of most of these parentheses. None of them are actually needed in your query in the JOIN or WHERE clause.





Re: Newbie Need help with tsql query

Arnie Rowland

Thanks Louis.

That was to be my next suggestion.

But first, I wanted to help the OP understand that when he/she posts

This query works in my ms sql 2000
, or,
This works just fine in sql 2000 in a cold fusion application
and the query can't possible execute, he/she needs to be a bit more 'critical' when looking over the code and finding the obvious mistakes.

I've been feeling a bit pedantic today...






Re: Newbie Need help with tsql query

Louis Davidson

I know the feeling Smile