MS SQL

MS SQL RSS

Incorrect syntax near '='.

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2006

Posts: 4

Reputation: Qmoto is an unknown quantity at this point

Solved Threads: 0

Qmoto

Offline

Newbie Poster

Incorrect syntax near '='.

Dec 6th, 2006

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:

 Help with Code Tags 
 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

•

Join Date: Sep 2006

Posts: 44

Reputation: MCP is an unknown quantity at this point

Solved Threads: 3

MCP

Offline

Light Poster

Re: Incorrect syntax near '='.

Dec 6th, 2006

•

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:
Help with Code Tags

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?

•

Join Date: Dec 2006

Posts: 4

Reputation: Qmoto is an unknown quantity at this point

Solved Threads: 0

Qmoto

Offline

Newbie Poster

Re: Incorrect syntax near '='.

Dec 7th, 2006

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

 Help with Code Tags 
 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

•

Join Date: Dec 2006

Posts: 4

Reputation: Qmoto is an unknown quantity at this point

Solved Threads: 0

Qmoto

Offline

Newbie Poster

Re: Incorrect syntax near '='.

Dec 7th, 2006

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

•

Join Date: Sep 2006

Posts: 44

Reputation: MCP is an unknown quantity at this point

Solved Threads: 3

MCP

Offline

Light Poster

Re: Incorrect syntax near '='.

Dec 9th, 2006

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)

•

Join Date: Dec 2006

Posts: 4

Reputation: Qmoto is an unknown quantity at this point

Solved Threads: 0

Qmoto

Offline

Newbie Poster

Re: Incorrect syntax near '='.

Dec 14th, 2006

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