Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

It is not possible to create a derived table from an expression in MS ACCESS

Jun 13 2011 9:33AM by Madhivanan   

While the following is valid in SQL Server, it is invalid in MS ACCESS

select number from
(
select 1 as number
) as t
Read More..   [32134 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Madhivanan
3 · 40% · 12862
4
 
0
Knew
 
 
0
Incorrect
 
0
Interesting
 



Submit

4  Comments  

  • May be this one reason , public are using SQL Server to store the data instead of MS ACCESS .

    Intresting thing to know.

    commented on Jun 13 2011 9:42AM
    Paresh Prajapati
    6 · 23% · 7379
  • The real thing is not that you can't use derived tables but that you must have a table name in a select statement. The following by itself is invalid:

    SELECT 1;
    

    However, if you create a table in your database called OneRow (or for Oracle style, call it DUAL) and put just one row in it, then you can do this:

    SELECT 1 FROM OneRow UNION ALL SELECT 2 FROM OneRow;
    

    Now that you have cleared this barrier, you can use derived tables just fine in Access. In the default SQL syntax mode, it is done this way (Number is a reserved word so I used Num):

    SELECT T.*
    FROM [
       SELECT 1 AS Num FROM OneRow UNION ALL SELECT 2 FROM OneRow
    ]. AS T;
    

    Note particularly the period after the closing square bracket. Also you can't use square brackets in the embedded query, so you can't use column names that require quoting, but that's a bad idea to have any of those in your database in the first place.

    Finally, If you switch your database to use ANSI 92 syntax, then you can use the familiar syntax, though the original syntax will still work:

    SELECT T.*
    FROM (
       SELECT 1 AS Num FROM OneRow UNION ALL SELECT 2 FROM OneRow
    ) AS T;
    

    In Access 2007, click on the Home button, select "Access Options", click on "Object Designers" and in the "Query Design" section check the option "SQL Server Compatible Syntax (ANSI 92)". This option can't be changed if you have any queries open, and perhaps in other situations, but if you are having problems you can create a new database, set the option, and import everything from the old database.

    commented on Jun 16 2011 2:23PM
    ErikEckhardt
    65 · 3% · 898
  • ErikEckhardt, I am aware of the points you have given. But the thing about version 2007 is new to me. Thanks for the feedback

    commented on Jun 17 2011 1:55AM
    Madhivanan
    3 · 40% · 12862
  • Being able to use SQL 92 syntax is not new, it's available in Access 2003 and possibly earlier versions as well.

    commented on Jun 17 2011 11:09AM
    ErikEckhardt
    65 · 3% · 898

Your Comment


Sign Up or Login to post a comment.

"It is not possible to create a derived table from an expression in MS ACCESS" rated 5 out of 5 by 4 readers
It is not possible to create a derived table from an expression in MS ACCESS , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]