×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Limitations of Subquery or View

thread318-1505094

Limitations of Subquery or View

Limitations of Subquery or View

(OP)
I am using Pervasive 8.70.014 and trying to work with a denormalized table in a third party database.  The difficult table has 12 columns to store bill of material mix components and I am attempting to normalize the columns into a view or derived table for a reporting solution.

I am having difficulty encapsulating the following SQL into a view or using it as a derived table:

SELECT
Inmx.ProdID
, 'Component1' as Component
, Mix1CompID as MixCompID
, Prod.GrpID as ComponentGrpID
, Mix1Pct/100 as MixPct

FROM Inmx
LEFT OUTER JOIN Prod on Inmx.Mix1CompID = Prod.ProdID
WHERE Mix1Pct <> 0

UNION SELECT
Inmx.ProdID
, 'Component2' as Component
, Mix2CompID as MixCompID
, Prod.GrpID
, Mix2Pct/100 as MixPct
FROM Inmx
LEFT OUTER JOIN Prod on Inmx.Mix2CompID = Prod.ProdID
WHERE Mix2Pct <> 0

...10 more Union Selects for each of the Inmx.Mix*CompID

Does Pervasive 8.70.014 allow multiple Union Selects within a view?  Is it possible to use the above statements as a derived table within an outer query?

RE: Limitations of Subquery or View

According to the v8.7 documentation:

Quote:

View definitions cannot contain UNION operators. The operator UNION cannot be applied to any SQL statement that references one or more views.
As seen at [url]http://www.pervasive.com/library/docs/psql/870/sqlref/sqlref-4-19.html[/url].
When using Union statements, you're limited to standard statements.   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Limitations of Subquery or View

(OP)
Is it possible to do something like this?

Select * from
(
SELECT
Inmx.ProdID
, 'Component1' as Component
, Mix1CompID as MixCompID
, Prod.GrpID as ComponentGrpID
, Mix1Pct/100 as MixPct

FROM Inmx
LEFT OUTER JOIN Prod on Inmx.Mix1CompID = Prod.ProdID
WHERE Mix1Pct <> 0

UNION SELECT
Inmx.ProdID
, 'Component2' as Component
, Mix2CompID as MixCompID
, Prod.GrpID
, Mix2Pct/100 as MixPct
FROM Inmx
LEFT OUTER JOIN Prod on Inmx.Mix2CompID = Prod.ProdID
WHERE Mix2Pct <> 0
)

RE: Limitations of Subquery or View

I can't remember and can't find anything specific in the documentation.  Have you tried it?  Does it give an error?  
I don't have access to any V8.x machines currently (all of mine are on v9 and v10).   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Limitations of Subquery or View

(OP)
Yes, here is the error message I get:

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: select * from
(

SELECT<< ??? >>
Inmx.ProdID
, 'Component1' as Component
, Mix1CompID as MixCompID
, Prod.GrpID as ComponentGrpID
, Mix1Pct/100 as MixPct

FROM Inmx

RE: Limitations of Subquery or View

I'm guessing that it doesn't work then.  But, in the example given, simply executing the original statement should work:
SELECT
Inmx.ProdID
, 'Component1' as Component
, Mix1CompID as MixCompID
, Prod.GrpID as ComponentGrpID
, Mix1Pct/100 as MixPct

FROM Inmx
LEFT OUTER JOIN Prod on Inmx.Mix1CompID = Prod.ProdID
WHERE Mix1Pct <> 0

UNION SELECT
Inmx.ProdID
, 'Component2' as Component
, Mix2CompID as MixCompID
, Prod.GrpID
, Mix2Pct/100 as MixPct
FROM Inmx
LEFT OUTER JOIN Prod on Inmx.Mix2CompID = Prod.ProdID
WHERE Mix2Pct <> 0

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Limitations of Subquery or View

(OP)
Yes, I can execute the original statement, however I need to join other tables to the results of that statement.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close