creating view with union all

Posted on 2012-08-24
Last Modified: 2012-08-24
I have a query that uses union all that I am trying to turn into a view. But I am getting errors. This is the query.  ProductionCosts.txt Usually creating a view from a query is pretty simple. I tried it several ways without any luck. I triedCOO-TotalProductionCost-view1.txt and I tried it without the field names listed . What do I need to do to make it work?
Question by:qbjgqbjg
    LVL 45

    Accepted Solution


    I don't see anything in the query that would prevent it from being used as the core query in a view.

    What error(s) are you getting?

    Also, about how many rows do you expect this to return?

    LVL 8

    Assisted Solution

    I think your Create view syntax maybe wrong...
    I dont think you need to define the fields...

    did you try just
    create view [dbo].[COO_TotalProductionCost_vw]
        ,End_month  = case when year(ConstEndDt) <> 1900 then month(ConstEndDt)end
        ,End_year  = case when year(ConstEndDt) <> 1900 then  year(ConstEndDt)end

    Open in new window


    Author Comment

    I think I figured it out. I was running the query in our live environment. It worked fine. But I was trying to create the view in a test environment. So, I think there is something different in test.

    Author Closing Comment

    Thanks for your help.
    LVL 45

    Expert Comment

    One final comment...

    This is a fairly complex view.  It reads from several tables and has the potential to generate a lot of rows.

    Find out how many rows this returns.

      SELECT count(*) FROM myview;

    If it's a large number, you'll be well served to avoid using the view when you need to filter the results.  You can get better performance by applying the filter directly to the subquery than you can by applying it to the results of the view.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now