• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Records in Table 1 but not in Table 2

BBritain

Member
Hi Gurus,

I'm looking for the most efficient method of retrieving records that are in table Main, but not in table BoltOn. The tables have one common field (MCU). Both tables are pretty large and the number of records in Main but not in BoltOn is very small (probably less than .1%). This is being searched in a Form (probably Find/Browse) and grabbing ALL records in Main and using ER to check if they are in BoltOn (in order to exclude) will be prohibitive. Please suggest anything!

Regards,
Ben Again
 
How about a BSVW with Main outer join to Bolton. In the app add selection on MCU in Bolton where the value = -9999 or something nonsensical. The SQL should be created with WHERE MCU = -9999 or MCU IS NULL. As long as -9999 doesn't exist you should just get back the missing rows.

Craig
 

Larry_Jones

Member
Ben,

1. Does it have to be done in JDE? You could do this in SQL pretty fast ...
2. Why do you think the ER Check will be prohibitive? I've done that a number of times and typically its not noticeable to the end user. Create a index on Bolton of just the MCU and do a single fetch against that index.
 
Hi

Please do this steps:-

1. Join both of the table with the MCU.
2. On the find & browse form, Find button click event use the system function
set selection Append Flag
 
HI,

By using the write event on the find button click using Grid system function .

set selection append flag
set selection group (Pass the table name and field name)

Regards
Ramashankar
 

BBritain

Member
I am sooooo sorry all for not replying. I was sure I had but obviously not.

Craig - I tried your solution and it worked great!

Larry - Yes is had to be done in JDE and the process would match Millions of records and the ER and then I would have to do comparisons in the Grid Record Fetched - And this is where I often see the counter processing for 15/30/or more seconds just to get through the matching records. I needed a process that would only send the mismatched records from the database.

Ramalaxkar - Thank you, but that would still involve sending Millions of records from the database to the web server and I was looking for a way to avoid the useless data transfer.

jolly - Yes, I'm aware of that option - good option but less up front work with Craig's solution.

Thank you all for your responses. I'll try to be better at responding appropriately. I do appreciate you.

Ben again,
 
Top