Showing posts with label tsql. Show all posts
Showing posts with label tsql. Show all posts

Monday, March 19, 2012

Newbie Need help with tsql query

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;

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.

|||

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;

|||

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')

|||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.
|||

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...

|||I know the feeling Smile

Saturday, February 25, 2012

newbie - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad