×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Limitations of Subquery or View

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!

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