December 22, 2008 at 7:52 am
I am having problem with this where clause. I need the it else select the
SpecialtyCode when Local_Clinic_Code in 'CSCHR5A','HYEWFDAI','GYNGDR15' else when SpecialtyCode is null.
WHERE
dbo.OP_Tariff.SpecialtyCode = CASE WHEN OP.Local_Clinic_Code IN ('CSCHR5A','HYEWFDAI','GYNGDR15')THEN OP_Tariff.SpecialtyCode
ELSE (OP_Tariff.SpecialtyCode IS NULL) END
I keep getting the error :
Server: Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'IS'
Is the CASE Statement the correct or is there another way of doing this.
December 22, 2008 at 8:06 am
You can't do this quite like your'e trying to do it. If you fixed the syntax error you'd come out with a string like one of these:
dbo.OP_Tariff.SpecialtyCode = NULL
or
dbo.OP_Tariff.SpecialtyCode = IS NULL
Both of which are wrong. A value isn't = to NULL in SQL server, it IS NULL. NULL means the value is unknown, and one unknown value does not necessarily EQUAL another unknown value.
You could "fake" the code with something like this:
ISNULL(dbo.OP_Tariff.SpecialtyCode,'Z999999') = CASE WHEN OP.Local_Clinic_Code IN ('CSCHR5A','HYEWFDAI','GYNGDR15')THEN OP_Tariff.SpecialtyCode
ELSE 'Z999999' END
(I'm not 100% sure what you want to see here, so my logic might be off with the ISNULL. You may need a case statement on each side of the = sign to get what you really want... or there may be another way to do this all together. If you can clarify your problem with sample data we may be able to suggest an alternative method)
December 22, 2008 at 8:21 am
Basically I am trying to amend a work mates sql script. Currently his code is as follows.
SELECT OP.Attendance_ID, dbo.udf_providerCode(OP.PAS_data_source) AS providerCode, dbo.PBC_Providers.providerName,
OP.NHS_Number,
OP.Postcode_of_Usual_Address,
OP.Sex,
OP.Date_of_Birth,
OP.GP_Code,
OP.Practice_Code,
OP.PAS_Hospital_Number,
OP.Referrer_Code,
dbo.udf_OP_POD(OP.First_Attendance, OP.Date_of_Birth, OP.Date_of_Attendance) AS POD, OP.First_Attendance, OP.Attended_or_DNAd,
OP.Date_of_Attendance, OP.month_of_attendance, OP.Specialty_Code, OP.Cons_Specialty_Code, CASE dbo.udf_OP_POD(First_Attendance,
Date_of_Birth, Date_of_Attendance)
WHEN 'OPFA' THEN OP_Tariff_Local.FirstAttendanceTariff WHEN 'OPFUP' THEN OP_Tariff_Local.FollowUpAttendanceTariff WHEN 'OPCHFA' THEN OP_Tariff_Local.ChildFirstAttendanceTariff
WHEN 'OPCHFUP' THEN OP_Tariff_Local.ChildFollowUpAttendanceTariff END AS Total_Cost
FROM nwcscmdsdata.dbo.Outpatient_CMDS_Data OP INNER JOIN
dbo.PBC_Providers ON dbo.udf_providerCode(OP.PAS_data_source) = dbo.PBC_Providers.providerCode LEFT OUTER JOIN
dbo.OP_Tariff ON OP.Specialty_Code = dbo.OP_Tariff.SpecialtyCode LEFT OUTER JOIN
dbo.OP_Tariff_Local ON dbo.udf_providerCode(OP.PAS_data_source) = dbo.OP_Tariff_Local.providerCode AND
OP.Specialty_Code = dbo.OP_Tariff_Local.SpecialtyCode
WHERE
(OP.Attended_or_DNAd IN ('5', '6')) AND
(OP.First_Attendance IN ('1', '2')) AND
(OP.month_of_attendance BETWEEN '200804' AND '200903') AND
(OP.Purchaser_ID LIKE '5K5%') AND
(dbo.OP_Tariff.SpecialtyCode IS NULL) AND
(NOT ISNULL(OP.Contract_Line_Number,'') = 'NONCHARGE')
I have highlighted the areas where I need to amend. By using dbo.OP_Tariff.SpecialtyCode IS NULL he is ensuring that none of the records in the dbo.OP_Tariff table are selected. This is was what was wanted before but the criteria has changed. Consequently I need to pull out 2 specialty codes from OB tarriff table where the local clinical codes are in ('CSCHR5A','HYEWFDAI','GYNGDR15') and also where the OP_Tariff.SpecialtyCode IS NULL. I am not sure whether I am being clear.
December 22, 2008 at 8:57 am
That makes a bit more sense. Just use something like this instead of an overly complicated case statement:
WHERE
(OP.Attended_or_DNAd IN ('5', '6')) AND
(OP.First_Attendance IN ('1', '2')) AND
(OP.month_of_attendance BETWEEN '200804' AND '200903') AND
(OP.Purchaser_ID LIKE '5K5%') AND
(
(dbo.OP_Tariff.SpecialtyCode IS NULL) OR
(OP.Local_Clinic_Code IN ('CSCHR5A','HYEWFDAI','GYNGDR15'))
) AND
(NOT ISNULL(OP.Contract_Line_Number,'') = 'NONCHARGE')
December 22, 2008 at 9:09 am
Cheers. I initially thought about that but I thought it was too simple lol. Many thanks it seems to have worked. Sometimes the best solutions are the easiest ones!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply