Error when trying to save view

[earme]

I'm receiving the error "View definition includes no output columns or includes no items in the FROM clause" when I try to save the following query as a view:

SELECT dbo.CP.ProdNo, dbo.SP.cost, dbo.CP.Price, round((isnull(dbo.CP.Price, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2) as custgm,
  CASE UPPER(dbo.BC.pCode) 
    WHEN 'P' THEN dbo.SP.Pp
    WHEN 'W' THEN dbo.SP.Pw
    WHEN 'C' THEN dbo.SP.Pc
    WHEN 'R' THEN dbo.SP.Pr 
  END AS sprice,
  CASE UPPER(dbo.BC.pCode) 
    WHEN 'P' THEN round((isnull(dbo.SP.Pp, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
    WHEN 'W' THEN round((isnull(dbo.SP.Pw, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
    WHEN 'C' THEN round((isnull(dbo.SP.Pc, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
    WHEN 'R' THEN round((isnull(dbo.SP.Pr, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
  END AS stdgm
FROM dbo.BILLCUST INNER JOIN dbo.CUSTPROD ON dbo.BILLCUST.BillCustNo = dbo.CP.CustNo INNER JOIN dbo.SP ON dbo.CP.ProdNo = dbo.SP.ProdNo

Any ideas?
Thanks,
Earme

 

[Durkin]

It looks like you haven't assigned Table aliases but you are using them to refer to tables. Durkin

 

[earme]

Ok, so the query should have been:

SELECT dbo.CP.ProdNo, dbo.SP.cost, dbo.CP.Price, round((isnull(dbo.CP.Price, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2) as custgm,
  CASE UPPER(dbo.BC.pCode) 
    WHEN 'P' THEN dbo.SP.Pp
    WHEN 'W' THEN dbo.SP.Pw
    WHEN 'C' THEN dbo.SP.Pc
    WHEN 'R' THEN dbo.SP.Pr 
  END AS sprice,
  CASE UPPER(dbo.BC.pCode) 
    WHEN 'P' THEN round((isnull(dbo.SP.Pp, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
    WHEN 'W' THEN round((isnull(dbo.SP.Pw, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
    WHEN 'C' THEN round((isnull(dbo.SP.Pc, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
    WHEN 'R' THEN round((isnull(dbo.SP.Pr, 0) - isnull(dbo.SP.cost, 0))/(isnull(dbo.SP.cost,1)) * 100, 2)
  END AS stdgm
FROM dbo.BC INNER JOIN dbo.CP ON dbo.BC.CustNo = dbo.CP.CustNo INNER JOIN dbo.SP ON dbo.CP.ProdNo = dbo.SP.ProdNo

So, I'm not using table aliases, but still getting the error.

Earme

 

[essa2000]

Dear ;
Try to use this Query ;

SELECT CP.ProdNo, SP.cost, CP.Price, round((isnull(CP.Price, 0) - isnull(SP.cost, 0))/(isnull(SP.cost,1)) * 100, 2) as custgm,
CASE UPPER(BC.pCode)
WHEN 'P' THEN SP.Pp
WHEN 'W' THEN SP.Pw
WHEN 'C' THEN SP.Pc
WHEN 'R' THEN SP.Pr
END AS sprice,
CASE UPPER(BC.pCode)
WHEN 'P' THEN round((isnull(SP.Pp, 0) - isnull(SP.cost, 0))/(isnull(SP.cost,1)) * 100, 2)
WHEN 'W' THEN round((isnull(SP.Pw, 0) - isnull(SP.cost, 0))/(isnull(SP.cost,1)) * 100, 2)
WHEN 'C' THEN round((isnull(SP.Pc, 0) - isnull(SP.cost, 0))/(isnull(SP.cost,1)) * 100, 2)
WHEN 'R' THEN round((isnull(SP.Pr, 0) - isnull(SP.cost, 0))/(isnull(SP.cost,1)) * 100, 2)
END AS stdgm
FROM dbo.BILLCUST BC INNER JOIN dbo.CUSTPROD CP ON dbo.BILLCUST.BillCustNo = dbo.CP.CustNo INNER JOIN dbo.SP SP ON dbo.CP.ProdNo = dbo.SP.ProdNo


Actually , in first post you used complete table names in from clauase and use CP , SP and BC i.e. should be aliase in select list.

In Second post , you used only CP, BC , SP which are not actual names of tables and you did not use tables names in this query.

If you want to use short names instead of long names in your query then you should mention both the actual name and then the aliase for this table.

In your query I don't know what is SP either it is the actual name or aliase. If it is the actual name of the table then there is no need to use SP aliase for this table. I used SP as aliase for it.

I hope now it will help you out.


Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[Durkin]

Are any of the objects you are referencing views? Durkin

 

[earme]

Sorry of the confusion. I changed the names of the tables when posting, my own kind of security I guess, but I forgot to change them in part of the FROM statment, thus the alias confusion. CP, BC, and SP are tables, I've just changed their names for posting, no views are used.
Another thing I should have mentioned, the query works, it will return results, but it won't let me save it.

Sorry again,
earme

 

[tlbroadbent]

If you are using Enterprise Manager to create the query, you've just found anmother reason many of us shun it as a development tool. Copy the view to Query Analyzer and create the view there.

Create View vYourViewName As

<the query>

Go Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.