IT SOLUTIONS
Your full service technology partner! 
+Expand
   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardQBE & SQL Topic   Print This     

How to do a FULL JOIN in SQL Paradox Editor

How to do a FULL JOIN in SQL Paradox Editor in QBE & SQL topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: Paradox   How do   How do FULL   JOIN SQL   Paradox Editor  
Coder_182
?

When i use the keyword  FULL JOIN  I get an error message. 

 Posted 11 years ago (Thread Starter)
Comment Quote
About Coder_182 +Expand
Location=? 
Joined=11 years ago   MB Posts=6  
Moderator
Bryan Valencia
209 Software
Home office in Manteca, CA

I am more familiar with Oracle/SQL Server but there the syntax is FULL OUTER JOIN

Yahoo ID: Commander_Bombast

Opportunity is missed by most because it is dressed in overalls and looks like work.

--THOMAS ALVA EDISON

 

 Posted 11 years ago
Comment Quote
About Bryan Valencia +Expand
Location=Home office in Manteca, CA 
Joined=18 years ago   MB Posts=23   KB Posts=27   KB Comments=4   BLOG, Topics=2  
Moderator
Bryan Valencia
209 Software
Home office in Manteca, CA

I just read that in paradox the OUTER keyword is optional.

So lets say you want to join customers and visitors from a website.  They are not mutually exclusive and you're matching by email address.

select visitors.name, visitors.email, customers.name, customers.email from visitors
full outer join customers on upper(customers.email)=upper(visitors.email)

You'll get 4 columns, like this...

Customer_name, Customer_email, null null    --this entry only in customers
null, null Visitor_name, Visitor_email   --this entry only in visitors
Customer_name, Customer_email,Visitor_name, Visitor_email --this entry in both
like this...
Bob A Fett, Bobafet@email.com, NULL, NULL
NULL, NULL, Claude O'vercast, cloudy@email.com
June C. june@email.com, June, June@EMAIL.com

Does this help?

Yahoo ID: Commander_Bombast

Opportunity is missed by most because it is dressed in overalls and looks like work.

--THOMAS ALVA EDISON

 

 Posted 11 years ago
Comment Quote
About Bryan Valencia +Expand
Location=Home office in Manteca, CA 
Joined=18 years ago   MB Posts=23   KB Posts=27   KB Comments=4   BLOG, Topics=2  
Coder_182
?
 Posted 11 years ago (Thread Starter)
Comment Quote
About Coder_182 +Expand
Location=? 
Joined=11 years ago   MB Posts=6  
Coder_182
?

When the from clouse have more than 4 tables. 

 Posted 11 years ago (Thread Starter)
Comment Quote
About Coder_182 +Expand
Location=? 
Joined=11 years ago   MB Posts=6  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

What was your error message?  what version of paradox?  What version of windows?

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

 Posted 11 years ago
Comment Quote
About Daniel Fought +Expand
Location=Home office in Fresno, CA. 
Joined=23 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Coder_182
?

My version of Paradox its 11, and I get the error message in the qbe too when I use it the (!) inclusion operator when i push show me sql. 

 Posted 11 years ago (Thread Starter)
Comment Quote
About Coder_182 +Expand
Location=? 
Joined=11 years ago   MB Posts=6  
Anonymous Post

I don't think that the qbe has ever interpreted the ! operator.  You will have to code the SQL by hand following the pattern Bryan presented above.

---
Dan
 Posted 11 years ago
Comment Quote

Post ID #15107 (Level 5) and Parent is 15101
Reply Posted 2/9/2014 6:09:43 AM
ASPT
ASPT
Hickory, NC USA

Dan,

I have not used those operators in queries in qbe in a long time, but it seems to me that in version Paradox 3.0 or 3.5 that the "!" may have been valid.  I will try to find my old books and see if I remember it correctly.  I could be wrong.

Ralph Maxwell

 Posted 11 years ago
Comment Quote
About ASPT +Expand
Location=Hickory, NC USA 
Joined=16 years ago   MB Posts=12  
Daniel Fought
Prestwood IT
Home office in Fresno, CA.

Can we please get the exact error message that you are getting.  That may give us some idea of what exactly your problem is.  Paradox does not translate the inclusion operator (!) into SQL.  Not sure why this wasn't coded since it is essentially a full join.  If you want a full join in SQL you have to either write it in SQL or use the SQL builder in Paradox.

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

 Posted 11 years ago
Comment Quote
About Daniel Fought +Expand
Location=Home office in Fresno, CA. 
Joined=23 years ago   MB Posts=401   KB Posts=12   KB Comments=4  
Most Recent Post
Coder_182
?

Yes I did it. But the problem is that not present present the data. In the QBE the ! operator works, but like you say in the sql query the ! operator don't work. How to do when the from clause have more than 3 tables. In the QBE  when i put the (!) in the example_id field, the query show the results. But in sql code, the query results is empty. Take a look to the following code:

Thanks in Advance!

Take a look the following code:

select r.example_id,r.effect_id,r.dummydata_id,r.business_location,

r1.title,r1.active,r1.status_id,r1.business_location,r2.effect_name,r2.business_location,

r3.investigation_check_list,r3.business_location,r4.approved_by,

r4.approved_date,r4.changed,r4.date_entered,r4.date_changed,r4.entered_by,r4.reason,

r4.more_info1,r4.more_info2,r4.more_info3,r4.category_id,r4.classification_id,r4.type_id,r4.status_id,r4.business_location,e.last_name,e.first_name,e.business_location

from dbo.r400h r  RIGHT OUTER JOIN dbo.r400a r1 ON r1.example_id=r.example_id ,dbo.R400D r2,dbo.r400b1 r3,dbo.r400hp r4 ,dbo.employee e

where r2.effect_id = r.effect_id

 

and r3.dummydata_id= r.dummydata_id and r4.sequence_id1 = r.sequence_id1 and e.social_security_no = r4.approved_by 

 Posted 11 years ago (Thread Starter)
Comment Quote
About Coder_182 +Expand
Location=? 
Joined=11 years ago   MB Posts=6  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1117A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #15101 Counter
5672
Since 2/2/2014
Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2025 Prestwood IT Solutions.   [Security & Privacy]