Limitations of Subquery or View

[mrdjsm1th]

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?

 

[mirtheil]

According to the v8.7 documentation:

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 http://www.pervasive.com/library/docs/psql/870/sqlref/sqlref-4-19.html.
When using Union statements, you're limited to standard statements.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mrdjsm1th]

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
)

 

[mirtheil]

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

 

[mrdjsm1th]

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

 

[mirtheil]

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

 

[mrdjsm1th]

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