How to join table in a VIEW without using UNION 1

[BEVer69]

Hello,
I am new to SQL and need a hand creating a view in Pervasive PSQL v8. The first table called ITEM2 contains a text field which needs to be split apart into 10 separate fields and joined to a table called PROMOCODE. The following select statement is not pretty but works so far for the first two promocodes. The problem is that I cannot use a UNION in a view.

#select item2.plunmbr, promotions.promocode,
promotions.startdate, promotions.enddate
from item2
inner join promotions on
left(item2.promocodes,6) = promotions.promocode

union select item2.plunmbr, promotions.promocode,
promotions.startdate, promotions.enddate
from item2
inner join promotions on
right(left(item2.promocodes,12),6) = promotions.promocode

Many Thanks

 

[BEVer69]

Sorry for the bump but I am completely stumped.

Mirtheil, you seem to be the expert of this area. Any suggestions? I apologize that my SQL knowledge is weaker than others.

 

[mirtheil]

Well, I haven't used v8 in several years. The current version of PSQL is v10 and it does support Unions in a view.
As far as this problem, you say that it works for the first two promocodes. What happens for the rest?
Also, why are you creating a view? Why not just use the SQL you have?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[BEVer69]

I would like to use the view in Crystal. I don't believe that it will support such a complex query unless it is put into a view. Is there a way to use aliases to get around a union?

 

[mirtheil]

If you can put a SQL statement into Crystal and you are using the ODBC drivers, it should work. Crystal should just pass it through to the PSQL engine. Does the query work properly through the Pervasive Control Center?

If I'm reading the query right, the "left(item2.promocodes,6)" and the "right(left(item2.promocodes,12),6)" are first two splits of the 10 fields in Item2. Is that right?

One thought that popped into my head would be a Stored Procedure. I'm not as familiar with Crystal Reports. Can it call a Stored Procedure? If so, you might be able to do your Union and return the result set using the RETURNS clause in the Stored Procedure.

I can confirm that your statement does work correctly in a view in V10. I don't have a v9 box at home so I can't test it.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mirtheil]

Oh, and I'm not sure if you'll be able to use aliases to get around the UNION.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com