Creating view on union subquery over OLEDB - error

I got error which says - Union is not allowed in subquery - when I try to create view like this:
create view v_ab as SELECT id from a UNION ALL select id from b
Does anybody how can I create such view without error?

Thanks
PaaB
0
PaaB
11/8/2005 2:00:04 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

6 Replies
361 Views

Similar Articles

[PageSpeed] 55

Om Sri Sai Ram

It should work.
Can you give your table definitions,
Thanks,
Ram


Thanks,
Ram


Om Sri Sai Ram
0
potturi_rp
11/8/2005 5:19:50 PM
For testing need I just create table named a and b every with only one column named id and defined as integer. When I use the union query on the same tables in MS Access aplication then everything is OK - so it looks like an Access's SQL syntax limitation. I think I can go round this problem by using of full outer join, but it looks like the Access don't support full join syntax. Do you have some idea?

Thanks
PaaB
0
PaaB
11/8/2005 10:28:01 PM

Om Sri Sai Ram

 

If you want it in MS Access, save your SQL as query.  Use only following SQL for creating query (remove “create view v_ab as”)

 

SELECT id from a UNION ALL select id from b

 

Thanks,

Ram

 


Thanks,
Ram


Om Sri Sai Ram
0
potturi_rp
11/9/2005 3:55:13 PM
I need create the view in ASP.NET program...

Thanks,
PaaB
0
PaaB
11/9/2005 5:02:38 PM

Om Sri Sai Ram

 

Works with ODBC name space

Code:

 

        Try

 

            Dim ConnectStr As String

 

            ConnectStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Trim(GetExePath()) & "\ram.mdb" & ";Uid=Admin;Pwd=;"

 

            Dim m_SpreadConn As System.Data.Odbc.OdbcConnection

            m_SpreadConn = New System.Data.Odbc.OdbcConnection(ConnectStr)

            m_SpreadConn.Open()

 

 

            Dim cmd As New System.Data.Odbc.OdbcCommand("create view v_ab1 as SELECT id from a  UNION ALL select id from b", m_SpreadConn)

 

            cmd.ExecuteNonQuery()

 

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

 

 

Thanks

Ram

 


Thanks,
Ram


Om Sri Sai Ram
0
potturi_rp
11/9/2005 10:12:51 PM
Thank you very much Ram. I will try it, it is acceptable solution for me.
0
PaaB
11/10/2005 12:39:39 PM
Reply:

Similar Artilces:

CREATE VIEW WITH UNION
When I create a view like this: create view v1 as select * from table1 union select * from table_his1 the structure of both tables are identical If I execute SELECT statement on the view, Sybase use the correct INDEX por both tables? Will I have problems with the perfomance? Thanks! ...

CREATE VIEW with UNION
When I create a view like this: create view v1 as select * from table1 union select * from table_his1 the structure of both tables are identical If I execute SELECT statement on the view, Sybase use the correct INDEX por both tables? Will I have problems with the perfomance? Thanks! Will Sybase use the correct index for both tables? Yes ... No ... Maybe ... *shrug* ... it depends on *what* indexes you have on your tables, the available statistics on the tables, as well as your actual queries. 'course, all of this assumes that you're running a version of...

OLEDB / Access 2002
I have the database connection working fine, and simple queries like "SELECT * FROM News" work fine... but when I attempt to do a simple Inner Join I get the following Error: Syntax error in FROM clause when I call the .Execute method of the Command object. Now the Query itself works just fine if I run it in Access... which is why I'm so lost now. Here is the code, any help would be appreciated! (btw the provider i'm using is Microsoft.Jet.OLEDB.4.0) private void do_GetNews() { // At this point in the code, the connection is open and works fine... I just ...

ANN: Gnostice PDFOne .NET v3.01 released
We are happy to announce the immediate availability of PDFOne .NET v3.01. PDFOne .NET v3.01 includes important enhancements for deployment on 64-bit and 32-bit environments using a single set of PDFOne .NET assemblies. v3.01 also includes enhanced fault tolerance for reading PDF files and other fixes and optimizations. v3.01 is a free update to all current customers of PDFOne .NET 3.0 and the download is available through the registered downloads area on the Gnostice website. To view the full list of enhancements and fixes in PDFOne .NET 3.01, please go to: http://www.gnostice.co...

Create a View to an Access DB
Is it possible to create a View in a SQL database that references a table in an Access database? If so how is this achieved? Thanks Yes you can. Prerequisites are that you set up the Access db as a linked server in your SQL server.See http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/ If this post was useful to you, please mark it as answer. Thank you!...

CREATE VIEW and UNION restriction
Hi, I can't understand why I can't use a UNION within a VIEW. This is a real restriction. I'm trying to create a VIEW across the same table that exists in a live database and an archive database, so that users have a seamless view of the data, regardless of it being archived or not. The users are attaching to the VIEW from Microsoft Access. Anybody have any ideas/workarounds? Thanks. David. ...

ACCESS & create view
Hi there, I'm trying migrate from ASA to ACCESS 2k DBE. and constanly geting errors with CREATE VIEW SQL statment. des M$ ACCESS 2k suports VIEW creatin ? (I found someting in ACCESS help. IMHO everyting shuld be ok.) Any idedas ? Dalius T. ...

How do I access a SQL view in VB.Net?
I'm able to access my stored procedures just fine, but I can't query/show a view that I'd built in SQL Server. Any suggestions on how would be welcome. Sample Stored Procedure reference, and I just want to reference a view now: ' Connect to the Database Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("myConnectionString")) ' Establish the set of data commands and a database connection that are used to fill the dataset Dim MyCommand As New SqlCommand("vw_Mast_PW_Accounts", MyConn) MyCommand.CommandType = CommandType...

How can I create simple view with UNION ALL
Using PD7.5 EBF2. I try to create a view. This will be a union of 4 tables. The SQL I need is: SELECT * FROM TAG_TRANS_01 UNION ALL SELECT * FROM TAG_TRANS_02 UNION ALL SELECT * FROM TAG_TRANS_03 UNION ALL SELECT * FROM TAG_TRANS_04 I try typing this in a view entity on the SQL Query tab in my physical model (SQL Server 2000). PD changes the SQL to: select * from ARCS.TAG_TRANS_01 union select ALL * from ARCS.TAG_TRANS_02 union select ALL * from ARCS.TAG_TRANS_03 union select ALL * from ARCS.TAG_TRANS_04 Why does PD insist on moving the ALL keyword? ...

Using UNION in Create view syntax
Hi All, When I try to create a view using a select that has a UNION clause I get the following error: Set operators may not appear within the definition of a view The SQL looks like this: CREATE VIEW citg_Payroll_Alloc_acct_bal ( ldr_entity_id ) AS SELECT ldr_acct_bal.ldr_entity_id FROM ldr_acct_bal WHERE ldr_acct_bal.ldr_amt_0 <> 0 UNION ALL SELECT ldr_acct_bal.ldr_entity_id FROM ldr_acct_bal WHERE ldr_acct_bal.ldr_amt_1 <> 0 Is there any way to use a UNION clause in a create view statement? Thanks for your help Shaun UNION in VIEW is a new ...

Problems with creating a datasource from MS Access
--------------BE70C74F572A1DC7316A607D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I am running Windows NT 4.0 as my OS and have loaded the Sybase ODBC Driver kit for NT (version 3.0.1 SYSYBNT.DLL). I also have Sybase Open Client v 10.0.4 with EBF 7704 running on my desktop. I don't have any problems configuring data sources via sqledit and being able to ping servers that I set up here. However, when I use MS Access (v. 7.0) to create a data source using the Sybase System 11 ODBC drivers, I get an error message saying "The setup routines fo...

Create Access stored procedure VIEW
Using Access 97 (not my app) I need to create a view?  I can do this in SQL but it does not work in access.  Ideas or tips appreciated. Found the facts! The Microsoft Access database engine does not support the use of CREATE VIEW, or any of the DDL (Data Definition Language (DDL): The language used to describe attributes of a database, especially tables, fields, indexes, and storage strategy. ANSI defines this to have the tokens CREATE, DROP, and ALTER.) statements, with non-Microsoft Access database engine databases. http://office.microsoft.com/en-us/access/HA0123144310...

Create view A before view B?
How to specify the schema creation order? I have a view B which call view A. So I want to create view A before create view B. But PD11 always create view B then create A, it will show error? Any clues? Yoyo See the online help and search for View generation order, one of the entries will be Defining a generation order for view. This can be specified with the use of a stereotyped extended dependency. HTH Jay Stevens [TeamSybase] On 10 Jul 2008 21:56:33 -0700, "Yoyo Young" <young@public1.wx.js.cn> wrote: >How to specify the schema creation order? ...

create view with union fails... can you help?
Hello, I am trying to run this simple statement: create view v1 as select col1, col2 from t1 union select col1, col2 from t3 and get message: Msg 197, Level 15, State 1: Server 'sybase1', Line 4: Set operators may not appear within the definition of a view. Am I doing something wrong? gene wrote: > Hello, I am trying to run this simple statement: > create view v1 > as > select col1, col2 from t1 > union > select col1, col2 from t3 > > and get message: > > Msg 197, Level 15, State 1: > Server 'sybase1', Line 4: &g...

.net driving me mad to create/access tables
 i have been looking at SOO many videos and tutorials,, and starter kit to try find out how to do something so simple. I have created a table _user_privmsgs with a to_userid (uniqueindenity) which is FK of the UserID  in aspnet_users table.now with this relation it should be simple to get the private messages of a user with something like SELECT * _user_privmsgs WHERE to_userid = $useridbut this $userid seems impossible to get , and i have looked everywhere for a way to implement something so simple.  Can someone help me either find a tutorial on how to achieve this, or help m...

Web resources about - Creating view on union subquery over OLEDB - error - asp.net.access-datasource

Londonistan: How Britain is Creating a Terror State Within - Wikipedia, the free encyclopedia
The book encompasses a critique of multiculturalism , alleged weak policing, cultural relativism , and what Phillips calls a 'victim culture'. ...

Facebook’s Matt Kelly Describes Process Of Creating Plugin For WordPress
The plugin for WordPress launched by Facebook in June has been downloaded more than 180,000 times, and Engineer, Developer Relations Matt Kelly ...

Creating Online Ads that Drive Brand Awareness
New research shows how the creative elements of online ads impact brand awareness.

Bug - Creating a Video.Watches Action stopped working - Facebook-Entwickler
Since today executing a POST on a me/video.wacthes endpoints no longer works, it was working around 10 hours ago since then the call always returns ...

Facebook Creating Fourth-Party Privacy Rules for Facebook Connect Widget & Plugin Developers
In a recently-written post on the Facebook Developers Wiki, Facebook had added new rules for information sharing between websites that have implemented ...

The Obama campaign won by creating their own Facebook, with unique features for political campaigns.
... seen as too dangerous to nominate someone who is so thoroughly narcissistic, arrogant and dishonest. The Obama campaign won basically by creating ...

Creating A Great Startup Culture
... with any Bay Area startup these days (ping pong, beer on tap, catered lunches and Macbook Airs) – though these things are, of course, Creating ...

WIRED Creating the Nebula for iPad on the iTunes App Store
Get WIRED Creating the Nebula on the App Store. See screenshots and ratings, and read customer reviews.

Nets of freedom creating mesh networks 03.07.2013 - Flickr - Photo Sharing!
Explore Strelka Institute photo's photos on Flickr. Strelka Institute photo has uploaded 14526 photos to Flickr.

Creating the incorrigible Cartman - YouTube
"South Park" creators Trey Parker and Matt Stone were big fans of "All in the Family," a 1970s CBS sitcom. Perhaps this is why their most incorrigible ...

Resources last updated: 11/29/2015 6:37:23 PM