Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Limitations of Subquery or View

Status
Not open for further replies.
mrdjsm1th

mrdjsm1th

Technical User
Oct 22, 2002
21
US
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?
 
Sort by date Sort by votes
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.
Click to expand...
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
 
Upvote 0 Downvote
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
)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Yes, I can execute the original statement, however I need to join other tables to the results of that statement.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
How to Execute Create View code in Actian Zen?
Replies
1
Views
527
mirtheil
mirtheil
Ambientguitar
  • Locked
  • Question
Need help opening a Pervasive Database or BTR files
Replies
3
Views
679
mirtheil
mirtheil
derkenstock
  • Locked
  • Question
Looking for a function(s) that will provide something similar to GROUP_CONCAT() or LISTAGG()
Replies
1
Views
227
mirtheil
mirtheil
bojimuncher
  • Locked
  • Question
Daily Scheduled Exports or Mirror from Pervasive Server to Different Pervasive Server 1
Replies
2
Views
376
bojimuncher
bojimuncher
David Ford
  • Locked
  • Question
[pervasive][odbc client interface] Numeric value out of range. (#0) - MS Access and Sage 50
Replies
2
Views
324
David Ford
David Ford

Part and Inventory Search

Sponsor

Back
Top