Msg102, Level15, state1, line3.......

[bloomlight]

When I ran the following codes, got error message say that "Msg102, Level15, state1, line3; Incorrect syntax near ' '. I tried to combine line1-line2. Didn't work. Combined line2 with line3. Didn't work either. I got the similar error message both times. What should I do to fix the error?

===================

Select Distinct
CASE WHEN T5.PhysicianSKey IS NULL
THEN T4.LastName
ELSE T6.LastName END,
CASE WHEN T5.PhysicianSKey IS NULL
THEN T4.FirstName
ELSE T6.FirstName END,
T1.Admission, T1.Discharge, T1.MedicalRecordNumber,
T0.FirstName, T0.LastName,
CASE WHEN T5.PhysicianSKey IS NULL
THEN T3.PhysicianTypeCode
ELSE T5.PhysicianTypeCode END,
T2.ReferralDate, T1.UnitID
From Episode T1
Inner join Patient T0 on (T0.PatientSKey = T1.PatientSKey) Inner join
EpisodeReferral T2 on (T2.PatientSKey = T1.PatientSKey
and T2.EpisodeSKey = T1.EpisodeSKey )
left outer join EpisodePhysician T3
Inner join Physician T4 on (T4.PhysicianSKey = T3.PhysicianSKey)
On (T3.EpisodeSKey = T1.EpisodeSKey
And T3.PhysicianTypeCode = 'REFER')
left outer join EpisodePhysician T5
Inner join Physician T6 on (T6.PhysicianSKey = T5.PhysicianSKey)
On (T5.EpisodeSKey = T1.EpisodeSKey
And T1.UNITID='HOSPICE'
And T5.PhysicianTypeCode = 'ALT')
Where T1.Admission between {d '2009-09-01'} and {d '2009-09-30'} Order by 1,2

 

[markros]

1. Your JOINS seem incorrect, see

left outer join EpisodePhysician T3 Inner join Physician T4 on (T4.PhysicianSKey = T3.PhysicianSKey) On (T3.EpisodeSKey = T1.EpisodeSKey And T3.PhysicianTypeCode = 'REFER') left outer join EpisodePhysician T5 Inner join Physician T6 on (T6.PhysicianSKey = T5.PhysicianSKey) On (T5.EpisodeSKey = T1.EpisodeSKey And T1.UNITID='HOSPICE' And T5.PhysicianTypeCode = 'ALT')

Why did you specify JOIN condition twice?


Where T1.Admission between {d '2009-09-01'} and {d '2009-09-30'} Order by 1,2

I've never seen dates passed like this - IMHO, you simply pass them as a string in single quotes - what database you're using?

 

[bloomlight]

Thanks markros.

I got the codes from a programmer. We are using their software and need to have a report run from our SQL database. I tried to contect the programmer and learned that she is out of office until Friday.

Any suggestions would greatly appreciated.

 

[markros]

Do you have an access to SSMS? If yes, can you adjust the code to fix joins and dates and run it to see if you can get it working?

 

[gmmastros]

Does the code you posted actually run (it probably does, but would like to make sure).

I tried to combine line1-line2

What does this mean?

Sounds to me like you want to combine the first name and last name in to a single column. If this is the case, then it should be relatively simple to do.

For example, to combine this in to one:

[tt][blue]
CASE WHEN T5.PhysicianSKey IS NULL
THEN T4.LastName
ELSE T6.LastName END,
CASE WHEN T5.PhysicianSKey IS NULL
THEN T4.FirstName
ELSE T6.FirstName END,
[/blue][/tt]

There are 2 ways to do this:

CASE WHEN T5.PhysicianSKey IS NULL
     THEN T4.LastName [!]+ ', ' + T4.FirstName[/!]
     ELSE T6.LastName [!]+ ', ' + T6.LastName [/!]END [!]As PhysicianFullName[/!],

Or

CASE WHEN T5.PhysicianSKey IS NULL
     THEN T4.LastName
     ELSE T6.LastName END [!]+ ', ' +[/!]
CASE WHEN T5.PhysicianSKey IS NULL
     THEN T4.FirstName
     ELSE T6.FirstName END [!]As PhysicianFullName[/!],

You can combine the the case statements in option 1 because both case statements are checking the same thing.

Make sense?

Markros, while the joins and dates appear odd, it is valid syntax. I've seen Access create joins like this in their query builder thingy, and I know that it does work in SQL Server too. Odd, Yes. Valid syntax, also yes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bloomlight]

George, the reason that I tried to combine was trying to fix the error. I really don't need to combine them if the query works. Any idea why I got error with the original codes? THANKS.