MySQL Forums
Forum List  »  Views

View with an outer join
Posted by: NOT_FOUND NOT_FOUND
Date: March 06, 2015 11:08PM

Because I can't do subqueries in the from statement, I have created two views. Using an outer join, I am able to create a selection statement and get the results I want. After creating a view using that statement, I get an error when querying the final view.

Error: Error formatting SQL query: empty string given as argument for ! character

Using a CASE statement, I have set a value for when there is not a record in one table. Runs fine as a select statement, but not as a view.

Here is the code.
create view member_acct_balance as
select b.*,
case when p.total_payments is null then 0 else p.total_payments end as total_payments,
case when p.member_id is null then 0 else b.total_billings-p.total_payments end as acct_balance
from member_billing_history b left outer join member_payment_history p
on p.member_id = b.member_id
and p.payment_year = b.billing_year

Any help will be welcomed. I am experienced Sybase developer and this is my first project in MySQL.

Options: ReplyQuote


Subject
Views
Written By
Posted
View with an outer join
4464
March 06, 2015 11:08PM
1500
March 07, 2015 12:27AM
1156
March 07, 2015 10:13AM
1117
March 07, 2015 03:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.