SQL Code Error

[ielmrani via AccessMonster.com]

Hi,
I am new to SQL server...

I am getting the following error on this sql code:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Like'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'CLAIM_HMASTERS_VS'.

It works if I don't incluce this line:
IIf(CLAIM_DETAILS_HCVW.HSERVICECD Like ' 17%','Baby','Mother') AS
ClaimType

Here is the complete sql code:

use mis
select CLAIM_HMASTERS_VS.CLAIMNO, CLAIM_DIAGS_VS.DIAG, CLAIM_DIAGS_VS.
DIAGDESC, PROV_MASTERS_HCVW.LASTNAME AS FACILITY,
CLAIM_HMASTERS_VS.MEMBID, CLAIM_HMASTERS_VS.MEMBNAME, CLAIM_HMASTERS_VS.
DATERECD, CLAIM_HMASTERS_VS.DATEPAID,
CLAIM_HMASTERS_VS.DATEFROM, CLAIM_HMASTERS_VS.HPCODE, CLAIM_DIAGS_VS.
DIAGREFNO,
IIf(CLAIM_DETAILS_HCVW.HSERVICECD Like ' 17%','Baby','Mother') AS
ClaimType

FROM (((CLAIM_HMASTERS_VS INNER JOIN CLAIM_DETAILS_HCVW ON CLAIM_HMASTERS_VS.
CLAIMNO = CLAIM_DETAILS_HCVW.CLAIMNO)

INNER JOIN CLAIM_DIAGS_VS ON CLAIM_HMASTERS_VS.CLAIMNO = CLAIM_DIAGS_VS.
CLAIMNO)INNER JOIN PROV_MASTERS_HCVW ON
CLAIM_HMASTERS_VS.PROVID = PROV_MASTERS_HCVW.PROVID)

where CLAIM_HMASTERS_VS.CLAIMNO like '200601119%' AND CLAIM_HMASTERS_VS.
HPCODE LIKE 'HIPA' AND CLAIM_DIAGS_VS.DIAGREFNO LIKE '1'

Any Help will be appreciated

ismail

 

[Jeff L]

SQL Server does not support IIF. It uses Case instead. Your statment
on SQL Server would be

'ClaimType' = Case When CLAIM_DETAILS_HCVW.HSERVICECD Like '%17%' Then
'Baby' Else 'Mother' End

Hope that helps!

 

[ielmrani via AccessMonster.com]

Worked. thank you so much.

Ismail

SQL Server does not support IIF. It uses Case instead. Your statment
on SQL Server would be

'ClaimType' = Case When CLAIM_DETAILS_HCVW.HSERVICECD Like '%17%' Then
'Baby' Else 'Mother' End

Hope that helps!

Hi,
I am new to SQL server...

[quoted text clipped - 35 lines]

ismail