Order By in a View

Hi everyone,
I'm relatively new to MSSQL.
I was trying to import a view from MS Access to MSSQL that has an order by statement.

And everytime I tried it gave me the following error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

ِAnyone can help?

M.

Quote:

Originally Posted by mmaamoun

Hi everyone,
I'm relatively new to MSSQL.
I was trying to import a view from MS Access to MSSQL that has an order by statement.

And everytime I tried it gave me the following error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

ِAnyone can help?

M.

option 1. place the ORDER BY on the code where you call the view

option 2. include a top 100% on your view. or use a ridiculous large number that will ensure return of all rows|||Excellent, the select top 100 percent ...
worked like magic for me:)

Thanks:)

Quote:

Originally Posted by ck9663

option 1. place the ORDER BY on the code where you call the view

option 2. include a top 100% on your view. or use a ridiculous large number that will ensure return of all rows

ORDER BY Can be used in view?

In MSSql,"Order by" and "distinct" can be used in view?
thanks!u can use 'order by' and 'distinct' in view.u have to use 'top ' key word when use order by clause.See below example,

use pubs
go
create view vauthors
as
select distinct top 100 percent city from authors
order by city|||No. It does not make sense to do order by in the view as a view is just a partion of a table(s). Perform the ordering you required when you select from the view.

Select *
from YourView
Order By your_view_column|||I remember that the following SQL is wrong:
create view vauthors
as
select distinct top 100 percent city from authors
order by city

Now in MSSQL,it can be run?
it mustn't include the order by and distinct in view,now it can be?|||what khtan said is right. u should order by when u select from view.though it is possible that 'order by' clause can be used in view,which u asked

Oracle to MS SQL error

Hello,
I have this query that was written for Oracle, but when I run it in MS
SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
I can't seem to figure it out, can you someone please point in the
right direction?
select 99, login_name, (full_name)
from shr_wr_approvers
where ACCOUNTING_NUMBER = ''
union select level, C240000005, ( C240000001)
from T265
where level in (2,3,4)
start with upper(C240000006) = '0397639'
connect by prior upper(C800000108) = upper(C240000006)
union select 0,'','No Data' from dual where not exists (select
login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
exists (select C1 from T265 where level in (2,3,4) start with
upper(C240000006) = '0397639' connect by prior upper(C800000108) =
upper(C240000006)) order by 1,2Did you try running the query through the Microsoft SSMA (SQL Server
Migration Assistant) tool first? It's a free download from Microsoft.com and
it will convert PL/SQL to T-SQL for you so you can use it in SQL Server.
BTW ... Is this a Remedy database schema that you are converting?
BR, mkro
--
mkro
"Ybleu" wrote:

> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
>|||Hi
start with and connect by prior is not available in SQL server as they are
proprietary Oracle extensions
You could use recursive CTEs in SQL 2005, if you are using SQL 2000 you may
have to resort to using a temporary table.
Check out Joe Celko's "Trees and Hierarchies" ISBN 1-55860-920-2 which talks
about this and methods of implementing hierarchical data.
Posting DDL, example data and expected results is always useful when
answering this sort of question see
http://www.aspfaq.com/etiquette.asp?id=5006
John
"Ybleu" wrote:

> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
>|||Ybleu wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) =
> upper(C240000006)) order by 1,2
START WITH... CONNECT BY is not standard SQL it's an Oracle special.
SQL Server has the ANSI/ISO standard syntax for recursive queries.
Lookup the WITH keyword in Books Online (SQL Server 2005 only).
If you want an exact solution then it would help if you could post DDL,
sample data and show your required end result. Also tell us what
version you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Oracle SQL vs MSSQL SQL

How can I list out all table name of within a schema in MSSQL2005?

In Oracle, I can run this SQL to get all table_name or object_name of a users.

SQL:

SELECT TABLE_NAME FROM USER_TABLES;

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='<DB_USERNAME>';

SELECT OBJECT_NAME FROM USER_OBJECTS;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='<DB_USERNAME>';

How to do it in MSSQL2005? any SQL cmd to execute to get the table / object list?

How to do it in MS SQL Server management studio?

I wanna have a list of table / objects under a schema by one action/script, but I DO NOT wanna to use MS SQL server management studio, check table / object name one by one.

Thanks.

Yes .. It is possible...

Select * from INFORMATION_SCHEMA.TABLES where table_schema='dbo'