I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

December Edition
Subscribe now! It's Free!
Enter your email:

   ► 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 5 years ago (Thread Starter)
Comment Quote
About Coder_182 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #15101, 10 replies
Thread Started 2/2/2014 12:03:23 PM
View Counter=4958
Last Reply Posted 2/26/2014 7:52:15 AM)
Location=? 
Joined=5 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 5 years ago
Comment Quote
About Bryan Valencia -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Bryan Valencia

Bryan Valencia is the author of millions of lines of code and writes for PrestwoodBoards and Visual Studio Journey on-line magazines.

Web Presence
LinkedIn, Bryan On LinkedIn (Visit Me)
Website, Software Services (http://209software.com/)
Website, Visual Studio Journey (http://vstudiojourney.blogspot.com/)

Post ID #15102 (Level 2) and Parent is 15101
Reply Posted 2/3/2014 10:31:09 AM
Location=Home office in Manteca, CA 
Joined=12 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 5 years ago
Comment Quote
About Bryan Valencia -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Bryan Valencia

Bryan Valencia is the author of millions of lines of code and writes for PrestwoodBoards and Visual Studio Journey on-line magazines.

Web Presence
LinkedIn, Bryan On LinkedIn (Visit Me)
Website, Software Services (http://209software.com/)
Website, Visual Studio Journey (http://vstudiojourney.blogspot.com/)

Post ID #15103 (Level 3) and Parent is 15101
Reply Posted 2/3/2014 10:57:48 AM
Location=Home office in Manteca, CA 
Joined=12 years ago   MB Posts=23   KB Posts=27   KB Comments=4   BLOG, Topics=2  
Coder_182
?
 Posted 5 years ago (Thread Starter)
Comment Quote
About Coder_182 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #15109 (Level 1.1)  Reply to 15103 and Parent is 15101
Thread Started 2/25/2014 12:51:25 PM
Location=? 
Joined=5 years ago   MB Posts=6  
Coder_182
?

When the from clouse have more than 4 tables. 

 Posted 5 years ago (Thread Starter)
Comment Quote
About Coder_182 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #15111 (Level 1.2)  Reply to 15103 and Parent is 15101
Reply Posted 2/26/2014 6:41:42 AM
Location=? 
Joined=5 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 5 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #15104 (Level 4) and Parent is 15101
Reply Posted 2/3/2014 11:33:40 AM
Location=Home office in Fresno, CA. 
Joined=17 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 5 years ago (Thread Starter)
Comment Quote
About Coder_182 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #15106 (Level 1.1)  Reply to 15104 and Parent is 15101
Thread Started 2/8/2014 7:38:28 PM
Location=? 
Joined=5 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 5 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 5 years ago
Comment Quote
About ASPT -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #15110 (Level 1.1)  Reply to 15107 and Parent is 15101
Thread Started 2/25/2014 1:04:09 PM
Location=Hickory, NC USA 
Joined=10 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 5 years ago
Comment Quote
About Daniel Fought -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
About Daniel Fought
Danial Fought is a senior programmer analyst with Prestwood IT where he develops custom Windows software and custom websites. When Dan is not coding for clients, he participates in this online community. Coding specialties include Paradox/ObjectPAL, MS Access, Visual Basic, and VS.Net/VB.Net.

Post ID #15112 (Level 6) and Parent is 15101
Reply Posted 2/26/2014 6:43:09 AM
Location=Home office in Fresno, CA. 
Joined=17 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 5 years ago (Thread Starter)
Comment Quote
About Coder_182 -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #15113 (Level 7) and Parent is 15101
Reply Posted 2/26/2014 7:32:01 AM
Location=? 
Joined=5 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 = P1151A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #15101 Counter
4958
Since 2/2/2014

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech