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

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


0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
tempdba gave me the best explanation
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.