Incorrect syntax near the keyword 'THEN'.

[Finedean]

Hi All,
It tried bracket, Parentth...etc nothing works. I get the same error message:
Incorrect syntax near the keyword 'THEN'.

can anyone help?

Here is the complete 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,

'CLAIMTYPE' = CASE WHEN CLAIM_DETAILS_HCVW.HSERVICECD like ' 17%' THEN 'Baby' ELSE 'Adult' END,

'AMOUNT' = CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0')ELSE CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET END

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'

thanks in advance

 

[lespaul]

if you are creating a pass-through query to SQL Server you can use CASE WHEN THEN, but if you are running an access query you need the iif statement:

IIF(condition, value if true, value if false)

you can nest:

iif(condition, value if true, iif(condition, value if true, value if false))

iif(CLAIM_DETAILS_HCVW.HSERVICECD like " 17%", "Baby", "Adult")

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Finedean]

thanks,
it not an access query, it sql query analyzer. the code works if I remove this line:
'AMOUNT' = CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0')ELSE CLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET END

 

[randy700]

it not an access query, it sql query analyzer

Which means you might get better help by posting in one of the SQL Server forums?


Randy

 

[Finedean]

thanks randy, I thought I was in SQL Server Forums.