error setting up a view in sql

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
sharris_glascolAsked:
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.

Vadim RappCommented:
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

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
Ioannis ParaskevopoulosCommented:
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
Mark WillsTopic AdvisorCommented:
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
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
Microsoft SQL Server

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.