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?
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
As seen at [url]http://www
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
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 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
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
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