Create view errors with a coalease

I am getting "errors" when creating a view from a P-SQL Query that executes just without any error when executing without creating a view from it. I think the errors might have to do with a COALESCE.
The View was created but my WHERE clause was removed and I get "errors" when I try to edit the P-SQL statement

Here is the Coalesce statement:

YEAR(COALESCE (date1, date2)) AS MYear

The purpose of this view is to provide a YTD Summary using the SUM function in the SELECT. Therefore there is also a Group By in the query.

The actual P-SQL Query is rather large and has confidential information so I can't post it. I know this makes solving this issue more difficult but I am stuck with not being able to post the actual query.

I guess the initial question is can a CREATE VIEW handle a COALESCE, SUM and GROUP BY?
For those not familiar with P-SQL it is like MS-SQL as far as syntax goes.

Thanks!
sfletcher1959VPAsked:
Who is Participating?
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.

matthewrhoadesCommented:
You should be able to use those items together, with the only caveat being that you have to be careful what is in your select list when you are using aggregate functions and group by clauses.

0
sfletcher1959VPAuthor Commented:
Yes, this is true. When I run the SQL as just a Query everything works great including the SUM and the COALESCE.
0
matthewrhoadesCommented:
What is the error you are getting?  
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

sfletcher1959VPAuthor Commented:
It is not helpful at all, it only says "Error" but when I edit the SQL statement I see that that my WHERE clause has been striped out.
0
matthewrhoadesCommented:
Hrm... is this supposed to be an updatable view or for select only?
0
sfletcher1959VPAuthor Commented:
It is just for view. I need the information in SUMMARY for Dashboards and Charts.
0
matthewrhoadesCommented:
If it does not return an error code with the error I would start stripping pieces out of the view and see when the where clause stops getting removed.

I wonder if there are any characters before you WHERE clause that are causing it go missing?  Perhaps a bad character in one of your fields causing the view to fail?  I would strip the database to a minimum of data and try creating the view.  If that does not work, remove the coalesce statement and then start taking aggregates and joins out.
0

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
sfletcher1959VPAuthor Commented:
I haven't had a chance to try this but it should allow me to find the offending statement.

Thanks!
0
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
Query Syntax

From novice to tech pro — start learning today.

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.