getting error incorrect syntax near the keyword as in sql server

Question

I am converting mysql query to sql query I have the below query, when i run this query in sql server it says

Error 156: Incorrect syntax near the keyword 'as'.

Can anyone please help why i am getting this error ?

Mysql Query(This is working fine in mysql):

UPDATE  tb_Episode as e left JOIN tb_Payer as p ON (e.CustID = p.company_id) AND (e.PayorType = p.payor_type)
                        left JOIN tb_HHPPS as h ON e.HHPPS = h.HHPPS
                        SET
                                e.PayerType = If(p.payer_type=1,"Ep","NonEp"),

                                e.LUPAAlert = If((p.payer_type)<>"1",0,If(EpEnd<=Now(),0,If(TotVisits<5,1,0))),
                                e.LUPADays = If((If((p.payer_type)<>"1",0,If(EpEnd<=Now(),0,If(TotVisits<5,1,0))))=0,0,EpEnd-Now()),
                                e.FinalAlert = If((p.payer_type)<>"1",0,If(abs(DATEDIFF(Now(),EpEnd))>1,0,1)),
                                e.FinalDays = If((If((p.payer_type)<>"1",0,If(abs(DATEDIFF(Now(),EpEnd))>1,0,1)))=1,abs(DATEDIFF(Now(),EpEnd)),0),
                                e.RAPAlert = If(p.payer_type="1",If(abs(DATEDIFF(Now(),EpStart))>0,1,0),0),
                                e.RAPDays = If((If(p.payer_type="1",If(abs(DATEDIFF(Now(),EpStart))>0,1,0),0))=1,abs(DATEDIFF(Now(),EpStart)),0)

                        where  e.billed_flag = "0"

SQLQuery (Getting error):

UPDATE  tb_Episode as e left JOIN tb_Payer as p ON (e.CustID = p.company_id) AND (e.PayorType = p.payor_type)
                        left JOIN tb_HHPPS as h ON e.HHPPS = h.HHPPS
                        SET
                                e.PayerType = IIF(p.payer_type=1,'Ep','NonEp'),

                                e.LUPAAlert = IIF((p.payer_type)<>"1",0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))),
                                e.LUPADays = IIF((IIF((p.payer_type)<>"1",0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))))=0,0,EpEnd-getdate()),
                                e.FinalAlert = IIF((p.payer_type)<>"1",0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)),
                                e.FinalDays = IIF((IIF((p.payer_type)<>"1",0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)))=1,abs(DATEDIFF(millisecond,getdate(),EpEnd)),0),
                                e.RAPAlert = IIF(p.payer_type="1",IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0),
                                e.RAPDays = IIF((IIF(p.payer_type="1",IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0))=1,abs(DATEDIFF(millisecond,getdate(),EpStart)),0)

                        where  e.billed_flag = '0'

Simplify the query to find the problem. stackoverflow.com/help/mcve — jarlh
– jarlh, Commented Feb 2, 2018 at 13:58
Just a bit of code review here (and hoping to stop my eye twitch), for (p.payer_type)<>"1", is payer_type a number or a character datatype? Under the hood, there is a difference between '1' and 1. Also, "<>'. — Shawn
– Shawn, Commented Feb 2, 2018 at 18:09
Have you checked MSDN for syntax of UPDATE with JOIN? That's probably the first thing you should do before posting here. Clearly you syntax is wrong. — Eric
– Eric, Commented Feb 2, 2018 at 19:50

Jacob H · Accepted Answer · 2018-02-02 18:22:35Z

3

This should work. SQL Server doesn't use the syntax of UPDATE [TABLE] AS [Alias] it requires the FROM clause still.

UPDATE e 
SET e.PayerType = IIF(p.payer_type=1,'Ep','NonEp'),
    e.LUPAAlert = IIF((p.payer_type)<>'1',0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))),
    e.LUPADays = IIF((IIF((p.payer_type)<>'1',0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))))=0,0,EpEnd - CAST(getdate() as date)),
    e.FinalAlert = IIF((p.payer_type)<>'1',0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)),
    e.FinalDays = IIF((IIF((p.payer_type)<>'1',0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)))=1,abs(DATEDIFF(millisecond,getdate(),EpEnd)),0),
    e.RAPAlert = IIF(p.payer_type='1',IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0),
    e.RAPDays = IIF((IIF(p.payer_type='1',IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0))=1,abs(DATEDIFF(millisecond,getdate(),EpStart)),0)
FROM tb_Episode as e
LEFT JOIN tb_Payer as p ON (e.CustID = p.company_id) AND (e.PayorType = p.payor_type)
LEFT JOIN tb_HHPPS as h ON e.HHPPS = h.HHPPS
WHERE e.billed_flag = '0'

edited Feb 2, 2018 at 18:22

answered Feb 2, 2018 at 14:00

Jacob H

2,5341 gold badge14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nikul Panchal Over a year ago

Thanks Jacob, i am very close to resolve this query, i have run the query and now it says "The data types date and datetime are incompatible in the subtract operator." why getting this error ?

Jacob H Over a year ago

Must be the EpEnd -getdate()). Is EpEnd date type?

Eray Balkanli Over a year ago

do like: EpEnd - cast(getdate() as date)

Jacob H Over a year ago

Updated the query to add the date cast too.

Shawn Over a year ago

I voted for this one, but for correctness-sakes, pretty please fix the "s.

|

Onkel Toob · Accepted Answer · 2018-02-02 14:02:43Z

It'll probably work if you move the JOIN statements like this:

UPDATE [e]
    SET 
        [e].[PayerType] = IIF([p].[payer_type] = 1, 'Ep', 'NonEp')
      , [e].[LUPAAlert] = IIF(([p].[payer_type]) <> "1", 0, IIF([EpEnd] <= GETDATE(), 0, IIF([TotVisits] < 5, 1, 0)))
      , [e].[LUPADays] = IIF((IIF(([p].[payer_type]) <> "1", 0, IIF([EpEnd] <= GETDATE(), 0, IIF([TotVisits] < 5, 1, 0)))) = 0, 0, [EpEnd] - GETDATE())
      , [e].[FinalAlert] = IIF(([p].[payer_type]) <> "1", 0, IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpEnd])) > 1, 0, 1))
      , [e].[FinalDays] = IIF((IIF(([p].[payer_type]) <> "1", 0, IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpEnd])) > 1, 0, 1))) = 1, ABS(DATEDIFF([millisecond], GETDATE(), [EpEnd])), 0)
      , [e].[RAPAlert] = IIF([p].[payer_type] = "1", IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpStart])) > 0, 1, 0), 0)
      , [e].[RAPDays] = IIF((IIF([p].[payer_type] = "1", IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpStart])) > 0, 1, 0), 0)) = 1, ABS(DATEDIFF([millisecond], GETDATE(), [EpStart])), 0)
FROM [tb_Episode] AS [e]
LEFT JOIN [tb_Payer] AS [p]
     ON([e].[CustID] = [p].[company_id])
       AND ([e].[PayorType] = [p].[payor_type])
LEFT JOIN [tb_HHPPS] AS [h]
     ON [e].[HHPPS] = [h].[HHPPS]
WHERE 
    [e].[billed_flag] = '0';

However, I wasn't able to test this statement due to the lack of table definitions and the statement's complexity.

Collectives™ on Stack Overflow

getting error incorrect syntax near the keyword as in sql server

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related