Incorrect syntax near '='.

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:

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?

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

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...

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

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)

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