sql 2008 view

I have created the following view
create      view [dbo].[myview_vw]
AS
SELECT            a.vendor_code,
            a.address_name,
            a.contact_phone,
            a.fax_number,
            a.attention_email,
            a.terms_code,
            b.bank_name,
            b.bank_account_num,
            b.aba_number,
            b.account_type
                   
FROM        mymaserap a,  mybankdet b
where         a .vendor_code = b.vendor_code

GO

this works fine but what I want to say is what is a Savings Account and what is A cheque account based on b. account_type

alter     view [dbo].[myview_vw]
AS
SELECT            a.vendor_code,
            a.address_name,
            a.contact_phone,
            a.fax_number,
            a.attention_email,
            a.terms_code,
            b.bank_name,
            b.bank_account_num,
            b.aba_number,
            b.account_type = case b.account_type
                                         when 0 then 'Savings Account'
                                         when 1 then 'Cheque Account'
                            end      
FROM        mymaserap a,  mybankdet b
where         a .vendor_code = b.vendor_code

GO

I am getting syntax error near '='
I should be able to put in a case here.
Would welcome some advice
Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EyalCommented:
alter     view [dbo].[myview_vw]
AS
SELECT            a.vendor_code,
            a.address_name,
            a.contact_phone,
            a.fax_number,
            a.attention_email,
            a.terms_code,
            b.bank_name,
            b.bank_account_num,
            b.aba_number,
            case b.account_type
                                         when 0 then 'Savings Account'
                                         when 1 then 'Cheque Account'
                            end      as account_type
FROM        mymaserap a,  mybankdet b
where         a .vendor_code = b.vendor_code
santhimurthydCommented:
Assignement can be doen only in Update statement, when as in the Select statement it won't be possibel

alter     view [dbo].[myview_vw]
AS
SELECT            a.vendor_code,
            a.address_name,
            a.contact_phone,
            a.fax_number,
            a.attention_email,
            a.terms_code,
            b.bank_name,
            b.bank_account_num,
            b.aba_number,
           b.account_type ,
           case b.account_type when 0 then
                        'Savings Account'
                when 1 then 'Cheque Account'
            end  as account_type_Desc
FROM        mymaserap a,  mybankdet b
where         a .vendor_code = b.vendor_code

GO


TempDBACommented:
Flyfishtrout15,
       You can go with any of the script the experts suggested above. Basically you are getting error because you are trying to assign a value to the already referenced attribute in the table. The code snippet from your query

b.account_type = case b.account_type
                                         when 0 then 'Savings Account'
                                         when 1 then 'Cheque Account'
                            end      
FROM        mymaserap a,  mybankdet b
where         a .vendor_code = b.vendor_code

The bold portion is the cause of the error. b.account_type has already a value so you can't assign a value in select statement for the column. Instead you can also modify your query to be correct as :-

alter     view [dbo].[myview_vw]
AS
SELECT            a.vendor_code,
            a.address_name,
            a.contact_phone,
            a.fax_number,
            a.attention_email,
            a.terms_code,
            b.bank_name,
            b.bank_account_num,
            b.aba_number,
            account_type = case b.account_type
                                         when 0 then 'Savings Account'
                                         when 1 then 'Cheque Account'
                            end      
FROM        mymaserap a,  mybankdet b
where         a .vendor_code = b.vendor_code

GO

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
tempdba gave me the best explanation
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.