January 20, 2004 at 7:01 am
Can someone help me with this query?
CREATE VIEW IMEXtrlrUtilUNIONasapSM_VW as SELECT top 100 percent * from IMEXtrlrUtilShipMat_VW
UNION select top 100 percent * from IMEXtrlrUtilASAP_VW
ORDER BY OrderNbr, City, Shipper
Server: Msg 104, Level 15, State 1, Procedure IMEXtrlrUtilUNIONasapSM_VW, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.
Chris
January 20, 2004 at 7:31 am
Try to explicitely name your columns in the view, not SELECT...* FROM...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 20, 2004 at 8:17 am
Thanks for the advice but the I get the same error.
CREATE VIEW IMEXtrlrUtilUNIONasapSM_VW as SELECT top 100 percent
IMEXtrlrUtilShipMat_VW.OrderNbr, IMEXtrlrUtilShipMat_VW.City,
IMEXtrlrUtilShipMat_VW.Shipper, IMEXtrlrUtilShipMat_VW.Weight from IMEXtrlrUtilShipMat_VW
Union select top 100 percent IMEXtrlrUtilASAP_VW.OrderNbr, IMEXtrlrUtilASAP_VW.City,
IMEXtrlrUtilASAP_VW.Shipper, IMEXtrlrUtilASAP_VW.Weight from IMEXtrlrUtilASAP_VW
ORDER BY OrderNbr, City, Shipper
anything else?
January 20, 2004 at 10:59 am
Don't use the table names in the select list:
CREATE VIEW IMEXtrlrUtilUNIONasapSM_VW as
SELECT top 100 percent OrderNbr, City, Shipper, Weight
from IMEXtrlrUtilShipMat_VW
Union
select top 100 percent OrderNbr, City, Shipper, Weight
from IMEXtrlrUtilASAP_VW
ORDER BY OrderNbr, City, Shipper
For the record, even if not including and Order By clause, I advocate never using Select * in a view. If you change the underlying schema of a table, you will have to recompile any views using Select * before they work correctly.
January 20, 2004 at 11:10 am
Thank you that worked however the output is a little different than the original which is in access. Does Access sort differently than sql server?
Chris
January 20, 2004 at 12:33 pm
No they sort the same way. However, if the sort seems different, I would check the data types of the fields. Perhaps one field is a number in Access and a varchar in SQL Server, or something like that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply