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!

*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.

Jobs

How to join table in a VIEW without using UNION

How to join table in a VIEW without using UNION

(OP)
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

RE: How to join table in a VIEW without using UNION

(OP)
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.

RE: How to join table in a VIEW without using UNION

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

RE: How to join table in a VIEW without using UNION

(OP)
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?

RE: How to join table in a VIEW without using UNION

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

RE: How to join table in a VIEW without using UNION

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

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!

Resources

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