Ad:

MS SQL Discussion Thread
Unsolved
Views: 30083

Dec 6th, 2006

Incorrect syntax near '='.

Expand Post »

I'm getting the error Incorrect syntax near '='. when I use the following case statement in a view. Obviously I'm not an expert on MS SQL, but I'm curious as to why I am allowed to use the '=' sign in a preceeding statement but not in the THEN section.

Help me understand what I'm doing wrong (beside everything) :cheesy:

MS SQL Syntax (Toggle Plain Text)
CASE
    WHEN
        MBAL.ALCUCD = '1' AND    -- ADDRESS CODE
        MABL.ALE2ST = '1'            -- ADDRESS TYPE
    THEN
        MBDE.DEB9CD = 'NULL' AND
        'B' AS ADDRESS_TYPE
END
CASE
    WHEN
        MBAL.ALCUCD = '1' AND    -- ADDRESS CODE
        MABL.ALE2ST = '1'            -- ADDRESS TYPE
    THEN
        MBDE.DEB9CD = 'NULL' AND
        'B' AS ADDRESS_TYPE
END

Similar Threads

Updated : Simple ASP.Net Login Page in ASP.NET
Syntax error in "like" in sql statement in ASP.NET
c++ SQLServer ODBC errors in C++
Multi-threading with C++.NET in C++

Qmoto

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Dec 2006

Permalink

Dec 6th, 2006

Re: Incorrect syntax near '='.

Click to Expand / Collapse Quote originally posted by Qmoto ...

I'm getting the error Incorrect syntax near '='. when I use the following case statement in a view. Obviously I'm not an expert on MS SQL, but I'm curious as to why I am allowed to use the '=' sign in a preceeding statement but not in the THEN section.

Help me understand what I'm doing wrong (beside everything) :cheesy:
MS SQL Syntax (Toggle Plain Text)
CASE
    WHEN
        MBAL.ALCUCD = '1' AND    -- ADDRESS CODE
        MABL.ALE2ST = '1'            -- ADDRESS TYPE
    THEN
        MBDE.DEB9CD = 'NULL' AND
        'B' AS ADDRESS_TYPE
END
CASE WHEN MBAL.ALCUCD = '1' AND -- ADDRESS CODE MABL.ALE2ST = '1' -- ADDRESS TYPE THEN MBDE.DEB9CD = 'NULL' AND 'B' AS ADDRESS_TYPE END

CASE is used when you want to return different values based on different conditions, such as:

select case when priority=1 then 'Low' when priority=2 then 'Med' when priority=3 then 'High' end from priorities

or the alternative

select case priority when 1 then 'Low' when 2 then 'Med' when 3 then 'High' end from priorities

I'm not sure what you're trying to do. SQL also has an IF syntax, for example:

if exists(select * from whatever)
begin
do something
end
else
begin
do something else
end

Can you give the entire SQL statement so that we can understand exactly what you're trying to do?

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

Permalink

Dec 7th, 2006

Re: Incorrect syntax near '='.

This code is used in the creation of a view, so I do not believe the if/then structure will work.

What I'm trying to do is set column MBDE.DEB9CD = 'NULL' and ADDRESS_TYPE = 'B' where MBAL.ALCUCD AND MABL.ALE2ST are equal to one.

Does that help?

Also, I've just been informed that I need to interpret 00000001 as '1', could I do that with IsNumeric or something?

Here's a shortened version of what I am doing

MS SQL Syntax (Toggle Plain Text)
CREATE VIEW CUSTOMER
AS
SELECT
     CAST(MBBF.CUSNO AS varchar(48))   AS BU_ACCOUNT_KEY
    ,CAST(MBBF.CUSNM AS varchar(64))   AS BU_ACCOUNT
    ...
    ,CAST(MBDE.DEB9CD AS varchar(48)) AS ADDRESS_KEY
    ,CASE
    WHEN
        MBAL.ALCUCD = '1' AND
    MABL.ALE2ST = '1'
    THEN
        MBDE.DEB9CD = 'NULL' AND
        'B' AS ADDRESS_TYPE
END
    ,'NULL'            AS ADDRESS_TELEPHONE
    ....
 
FROM
    TABLE1
          LEFT JOIN
               TABLE2
 
ECT...
CREATE VIEW CUSTOMER
AS
SELECT
     CAST(MBBF.CUSNO as varchar(48))   AS BU_ACCOUNT_KEY
    ,CAST(MBBF.CUSNM as varchar(64))   AS BU_ACCOUNT
    ...
    ,CAST(MBDE.DEB9CD as varchar(48)) AS ADDRESS_KEY
    ,CASE
    WHEN
        MBAL.ALCUCD = '1' AND
    MABL.ALE2ST = '1'
    THEN
        MBDE.DEB9CD = 'NULL' AND
        'B' AS ADDRESS_TYPE
END
    ,'NULL'            AS ADDRESS_TELEPHONE
    ....

FROM
    TABLE1
          LEFT JOIN
               TABLE2

ECT...

Last edited by Qmoto; Dec 7th, 2006 at 12:34 pm. Reason: Forgot to close the [code] tag

Qmoto

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Dec 2006

Permalink

Dec 7th, 2006

Re: Incorrect syntax near '='.

Never mind, I fixed it... I was making the problem too difficult.

I simply had to use a case statement on the field in question, so instead of one CASE statement trying to do two (2) things, I ended up with two CASE statements each doing only one thing.

Thanks,
Steve

Qmoto

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Dec 2006

Permalink

Dec 9th, 2006

Re: Incorrect syntax near '='.

Are all the values in the column like 000000001? Because you could just do field+0 that would implicitly convert it... or cast(field as int) to explicitly do it. if you have mixed fields, then you could have a case statement like

case when field like replicate('[0-9]',len(field)) then cast(field as int) else field end

(untested.. but something like that)

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

Permalink

Dec 14th, 2006

Re: Incorrect syntax near '='.

Sorry for the late reply.

This column is made up of various data, not just the 0000001, but thanks for the idea of doing a field+0 to do an implicit conversion. I'm guessing that will come in handy at some point in the future.

Thanks again,
Steve

Qmoto

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Dec 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Table query returning error -- help!

Next Thread in MS SQL Forum Timeline: Active Users in MSSQL

DaniWeb Message

Cancel Changes

User Name
Password