Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create view errors with a coalease

Posted on 2008-11-14
8
Medium Priority
?
242 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:sfletcher1959
  • 4
  • 4
8 Comments
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 22963775
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
 

Author Comment

by:sfletcher1959
ID: 22963910
Yes, this is true. When I run the SQL as just a Query everything works great including the SUM and the COALESCE.
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 22963941
What is the error you are getting?  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sfletcher1959
ID: 22964059
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
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 22964122
Hrm... is this supposed to be an updatable view or for select only?
0
 

Author Comment

by:sfletcher1959
ID: 22964462
It is just for view. I need the information in SUMMARY for Dashboards and Charts.
0
 
LVL 6

Accepted Solution

by:
matthewrhoades earned 2000 total points
ID: 22964520
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
 

Author Closing Comment

by:sfletcher1959
ID: 31516951
I haven't had a chance to try this but it should allow me to find the offending statement.

Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question