Incorrect syntax near '='.

[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:

CASE
    WHEN
        MBAL.ALCUCD = '1' AND    -- ADDRESS CODE
        MABL.ALE2ST = '1'            -- ADDRESS TYPE
    THEN
        MBDE.DEB9CD = 'NULL' AND
        'B' AS ADDRESS_TYPE
END

[MCP]

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?

[Qmoto]

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

[Qmoto]

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

[MCP]

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)

[Qmoto]

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