Solved

error setting up a view in sql

Posted on 2014-04-25
3
169 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
3 Comments
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

11 Experts available now in Live!

Get 1:1 Help Now