SQL: Cannot use UNION inside CREATE VIEW??

  • Thread starter Thread starter Cicada
  • Start date Start date
C

Cicada

I cannot UNION tables inside SQL's CREATE VIEW command. An error message is
shown:
"Cannot use UNION inside Sub-Query"

How to break through this limitation?

My program like this:
"CREATE VIEW UUU (AID, Start,NAME) AS SELECT AID,1 as Start, mid(NAME,1,3)
as NAME FROM T1 UNION ALL SELECT AID, 2 as Start, mid(NAME,4,3) as NAME from
T1 UNION ALL SELECT AID,3 as Start, mid(NAME,7,3) as NAME FROM T1 ORDER BY
AID,Start"
 
this might be a moot point - but I don't think the mid function can be used
for views (either for MSSQL or Oracle).
and order by in a view, certainly not oracle then <g>
The substitute for Mid would be SUBSTR
if you have a MsAccess back-end there's no such thing as a view anyway

Pieter
 
Please do not see on mid() function.
My programming grammer has no problem for my application, which is based on
ADO.
The point is: "Cannot use UNION inside Sub-Query of VIEW"
I guess this situation may happen also for SQL Server user, since it is
Microsoft product also.


"Pieter Wijnen"
 
Cicada said:
I cannot UNION tables inside SQL's CREATE VIEW command. An error
message is shown:
"Cannot use UNION inside Sub-Query"

How to break through this limitation?

My program like this:
"CREATE VIEW UUU (AID, Start,NAME) AS SELECT AID,1 as Start,
mid(NAME,1,3) as NAME FROM T1 UNION ALL SELECT AID, 2 as Start,
mid(NAME,4,3) as NAME from T1 UNION ALL SELECT AID,3 as Start,
mid(NAME,7,3) as NAME FROM T1 ORDER BY AID,Start"
Click to expand...

Use CREATE PROCEDURE instead.
 
I just check it from Web Site, and find that not all DBMS support "Use UNION
inside VIEW / Sub-Query"
Support SQL Server (after v7.0), Sybase (after v12.5)
Not Support MS Access, PGSQL

You may verify above conclusion again.
For ADO I am using, I will be thankful if anyone can verify for me.
 
Cicada said:
I just check it from Web Site, and find that not all DBMS support
"Use UNION inside VIEW / Sub-Query"
Support SQL Server (after v7.0), Sybase (after v12.5)
Not Support MS Access, PGSQL

You may verify above conclusion again.
For ADO I am using, I will be thankful if anyone can verify for me.
Click to expand...

If you are using ADO to create this query in a Jet (Access) database,
then you can use CREATE PROCEDURE to do it. I have tested this, and it
works. As far as Jet is concerned, views, action queries, and
SQL-specific queries such as union queries are all just queries.
Although CREATE VIEW can't create the query you specified, CREATE
PROCEDURE can.
 
Cicada said:
I just check it from Web Site, and find that not all DBMS support "Use UNION
inside VIEW / Sub-Query"
Support SQL Server (after v7.0), Sybase (after v12.5)
Not Support MS Access, PGSQL
Click to expand...

I think the work around is to put the UNION into a derived table
(subquery), which is ironic given the error message when you do NOT
have a derived table is "Unions not allowed in a subquery."

For example, this fails with the error:

CREATE VIEW DropMe
AS
SELECT ID_product AS ProductID,
Param AS Param
FROM Product
UNION ALL
SELECT ID_product, Param
FROM Porduct;

Whereas this works OK:

CREATE VIEW DropMe
AS
SELECT DT1.ProductID AS ProductID,
DT1.Param AS Param
FROM
(
SELECT ID_product AS ProductID, Param AS Param
FROM Product
UNION ALL
SELECT ID_product, Param
FROM Porduct
) AS DT1;

BTW I've found that sometimes using a column list causes the VIEW to
appear as a PROCEDURE in the schema catalog (ADO's OpenSchema method)
e.g. it may be worth avoiding this kind of construct for Jet:

CREATE VIEW DropMe (ProductID, Param)
AS
SELECT DT1.ID_product, DT1.Param
FROM
(
SELECT ID_product, Param
FROM Product
UNION ALL
SELECT ID_product, Param
FROM Porduct
) AS DT1;

Jamie.

--
 
Thanks Jamie & All !
Jamie's solution is bingo and has perfectly solved my problem.
The original error is really as ironic as Jamie said.
 
Jamie Collins said:
I think the work around is to put the UNION into a derived table
(subquery), which is ironic given the error message when you do NOT
have a derived table is "Unions not allowed in a subquery."

For example, this fails with the error:

CREATE VIEW DropMe
AS
SELECT ID_product AS ProductID,
Param AS Param
FROM Product
UNION ALL
SELECT ID_product, Param
FROM Porduct;

Whereas this works OK:

CREATE VIEW DropMe
AS
SELECT DT1.ProductID AS ProductID,
DT1.Param AS Param
FROM
(
SELECT ID_product AS ProductID, Param AS Param
FROM Product
UNION ALL
SELECT ID_product, Param
FROM Porduct
) AS DT1;

BTW I've found that sometimes using a column list causes the VIEW to
appear as a PROCEDURE in the schema catalog (ADO's OpenSchema method)
e.g. it may be worth avoiding this kind of construct for Jet:

CREATE VIEW DropMe (ProductID, Param)
AS
SELECT DT1.ID_product, DT1.Param
FROM
(
SELECT ID_product, Param
FROM Product
UNION ALL
SELECT ID_product, Param
FROM Porduct
) AS DT1;

Jamie.
Click to expand...
 
Back
Top