Results 1 to 7 of 7

Thread: Records in Table 1 but not in Table 2

  1. #1
    Member
    Join Date
    Nov 2000
    Location
    Denver, Colorado
    Posts
    630

    Records in Table 1 but not in Table 2

    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

  2. #2
    Senior Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    1,027
    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
    Craig Welton
    PatWel Group Inc.
    http://www.patwel.com
    Home of the FREE JDE Object Browser, JDETrace, NERDup and BusBuild+ Tools

    E1 9.0 8.98.4.2 Wintel SQL 2008
    E1 9.2 9.2.1.4 iSeries

  3. #3
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251
    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.
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  4. #4
    New Member
    Join Date
    Jan 2015
    Location
    Gurgaon
    Posts
    16
    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

  5. #5
    New Member
    Join Date
    Jan 2015
    Location
    Gurgaon
    Posts
    16
    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

  6. #6
    Member
    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    425
    Quote Originally Posted by Larry_Jones View Post
    Ben,

    1. Does it have to be done in JDE? You could do this in SQL pretty fast ...
    ^ this, in a SQL view masquerading as a JDE custom table.
    Contract JD Edwards Development Consultant.
    EnterpriseOne Xe through 9.2. Windows/Unix/OS400. SQLServer/Oracle/DB2 for i.
    ER, C/C++, BI Publisher, SQL, DSI dcLink

  7. #7
    Member
    Join Date
    Nov 2000
    Location
    Denver, Colorado
    Posts
    630
    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,

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.