CODEWITHSUNDEEP
sql-server
bones
bones

Reputation: 63

Why does this fail? (Incorrect Syntax Near '=')

Why does this fail?

GO
    select vcCarrier1, vcTerminal1, vcZipEnd
      FROM   [GroundEDI].[dbo].[tblCustomerRouting]
      HAVING
      ( (CASE  WHEN vcCarrier1 = 'Carrier Code' 
        THEN vcTerminal1 = 'STM'
        ELSE NULL
        END)
       -- ELSE vcTerminal1 
       AND
       (CASE WHEN vcZipEnd = 'ZIP'
       THEN vcTerminal1 = 'Terminal Code'
       ELSE NULL
        END))

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '='.

Upvotes: 0

Views: 45

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

These lines are incorrect: THEN vcTerminal1 = 'STM' and THEN vcTerminal1 = 'Terminal Code'. inside the Then clause of the case statement there can only be an expression that returns a scalar value, not a comparison and not an assignment.

I'm guessing (since it's not very clear from your attempt) it should be something like this:

SELECT vcCarrier1, vcTerminal1, vcZipEnd
FROM   [GroundEDI].[dbo].[tblCustomerRouting]
WHERE vcTerminal1 = 
    CASE WHEN vcCarrier1 = 'Carrier Code' THEN 
        'STM'
    WHEN vcZipEnd = 'ZIP' THEN
         'Terminal Code'
    ELSE 
        vcTerminal1 
    END

Upvotes: 1

Ionic
Ionic

Reputation: 3935

You have no logical expression in your having. By the way, having only makes sense on aggregated values.

Maybe this statement makes the result, which your trying to achieve?

select vcCarrier1, vcTerminal1, vcZipEnd
FROM   [GroundEDI].[dbo].[tblCustomerRouting]
WHERE (vcCarrier1 = 'Carrier Code'  AND  vcTerminal1 = 'STM') OR 
        (vcZipEnd = 'ZIP' AND vcTerminal1 = 'Terminal Code')

Best regards, Ionic

Upvotes: 0

Related Questions