Solved

error setting up a view in sql

Posted on 2014-04-25
3
186 Views
Last Modified: 2014-06-09
I have a query that I am trying to create a view and I am getting the query to run in sql just fine but when I put it in as a view I am getting a error message:  Here is the code:  the attachment is the error message
Select 
ord_no,
entered_dt,
cus_no,
item_no,
line_no,
tot_sls_amt,
prod_cat_desc,
qty_ordered
 from (
Select oeordhdr_sql.ord_no,
oeordhdr_sql.entered_dt,
oeordhdr_sql.cus_no
from oeordhdr_sql
where orig_ord_type in ('o','i','c')
 
union
 
Select oehdrhst_sql.ord_no,
oehdrhst_sql.entered_dt,
oehdrhst_sql.cus_no
From oehdrhst_sql
where orig_ord_type in ('o','i','c','q')) as oehdr
 
Join
 
(select oeordlin_sql.item_no,
oeordlin_sql.line_no,
oeordlin_sql.ord_no AS line_ord_no,
Case When oeordlin_sql.ord_type = 'c' Then
((oeordlin_sql.unit_price * oeordlin_sql.tot_qty_ordered) * (100 - oeordlin_sql.discount_pct)) / 100 * -1
else
((oeordlin_sql.unit_price * oeordlin_sql.tot_qty_ordered) * (100 - oeordlin_sql.discount_pct)) / 100
end as tot_sls_amt,
imcatfil_sql.prod_cat_desc,
oeordlin_sql.qty_ordered
from oeordlin_sql
join imcatfil_sql on oeordlin_sql.prod_cat = imcatfil_sql.prod_cat
where ord_type in ('o','i','c')
 
union
 
select oelinhst_sql.item_no,
oelinhst_sql.line_no,
oelinhst_sql.ord_no AS line_ord_no,
Case
When oehdrhst_sql.orig_ord_type = 'c' Then
((oelinhst_sql.unit_price * oelinhst_sql.tot_qty_ordered) * (100 - oelinhst_sql.discount_pct)) / 100 * -1
else
((oelinhst_sql.unit_price * oelinhst_sql.tot_qty_ordered) * (100 - oelinhst_sql.discount_pct)) / 100
end as tot_sls_amt,
imcatfil_sql.prod_cat_desc,
oelinhst_sql.qty_ordered
from oelinhst_sql
join oehdrhst_sql on oelinhst_sql.inv_no = oehdrhst_sql.inv_no
join imcatfil_sql on oelinhst_sql.prod_cat = imcatfil_sql.prod_cat
where oelinhst_sql.ord_type in ('o','i','c','q'))
 
as oelin on oehdr.ord_no = oelin.line_ord_no
order by oehdr.ord_no,oelin.line_no

Open in new window

Screen-Shot-2014-04-25-at-10.00..png
0
Comment
Question by:sharris_glascol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 40024085
The sql that is in the grid on the picture is not exactly like the one you published that runs. Maybe it was rewritten by Management Studio when you put it in the designer.

First try to run sql without the view, but with TOP 100 PERCENT like it is in the view, see if it still runs without the error. If it does, then create the view in query window (not in designer) with the exact same sql that runs.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 40024127
You can always use TSQL to create your view. This will help you keep the format of your query. Just add the following on top of your query ( not in the designer).

CREATE VIEW yourviewname

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40025424
Where you get a message "could not be bound" it means that it is unable to identify the true source object. Problem is that the error message tries to show the assumed source (that the engine reckons is correct).

That is partly due to query designer picking up the Table name, or at least what it thinks might be the table name (aka alias). Or, when there is a column name that exists in multiple sources and must have that extra clarification.

So, if your query works, then open a T-SQL window instead and as jyparask suggests.

CREATE VIEW vw_your_name_goes_here AS
Select    
--    add in the correct table / alias name (ie oehdr or oelin) as a prefix to the following
       ord_no,
       entered_dt,
       cus_no,
       item_no,
       line_no,
       tot_sls_amt,
       prod_cat_desc,
       qty_ordered

from (
       Select oeordhdr_sql.ord_no,
              oeordhdr_sql.entered_dt,
              oeordhdr_sql.cus_no
       from   oeordhdr_sql
       where  orig_ord_type in ('o','i','c')
 
       union
 
       Select oehdrhst_sql.ord_no,
              oehdrhst_sql.entered_dt,
              oehdrhst_sql.cus_no
       From   oehdrhst_sql
       where  orig_ord_type in ('o','i','c','q')
     ) as oehdr
 
Join
 
     (
       select oeordlin_sql.item_no,
              oeordlin_sql.line_no,
              oeordlin_sql.ord_no AS line_ord_no,
              Case When oeordlin_sql.ord_type = 'c' 
                   Then ((oeordlin_sql.unit_price * oeordlin_sql.tot_qty_ordered) * (100 - oeordlin_sql.discount_pct)) / 100 * -1
                   else ((oeordlin_sql.unit_price * oeordlin_sql.tot_qty_ordered) * (100 - oeordlin_sql.discount_pct)) / 100
              end as tot_sls_amt,
              imcatfil_sql.prod_cat_desc,
              oeordlin_sql.qty_ordered
        from  oeordlin_sql
        join  imcatfil_sql on oeordlin_sql.prod_cat = imcatfil_sql.prod_cat
        where ord_type in ('o','i','c')
 
        union
 
        select oelinhst_sql.item_no,
               oelinhst_sql.line_no,
               oelinhst_sql.ord_no AS line_ord_no,
               Case When oehdrhst_sql.orig_ord_type = 'c' 
                    Then ((oelinhst_sql.unit_price * oelinhst_sql.tot_qty_ordered) * (100 - oelinhst_sql.discount_pct)) / 100 * -1
                    else ((oelinhst_sql.unit_price * oelinhst_sql.tot_qty_ordered) * (100 - oelinhst_sql.discount_pct)) / 100
               end as tot_sls_amt,
               imcatfil_sql.prod_cat_desc,
               oelinhst_sql.qty_ordered
        from   oelinhst_sql
        join   oehdrhst_sql on oelinhst_sql.inv_no = oehdrhst_sql.inv_no
        join   imcatfil_sql on oelinhst_sql.prod_cat = imcatfil_sql.prod_cat
        where  oelinhst_sql.ord_type in ('o','i','c','q')
      ) as oelin on oehdr.ord_no = oelin.line_ord_no

--order by oehdr.ord_no,oelin.line_no

Open in new window


Now, there are a couple of column names (such as in WHERE clause) that really should have the table alias as a prefix.  For example...

where ord_type in ('o','i','c')

-- s/b

where oeordlin_sql.ord_type in ('o','i','c')

Open in new window


And leave out the order by within the view. Make that part of the select from the view. e.g.

Select ord_no,entered_dt,cus_no,item_no,line_no,tot_sls_amt,prod_cat_desc,qty_ordered
from  vw_your_name_goes_here 
order by ord_no,line_no

Open in new window


I think the more precision you get within each subquery element, the better you will be.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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