Results 1 to 9 of 9

Thread: Search Multiple Columns in Table - Best/Fastest Method?

  1. #1
    Member Mike Mackinnon's Avatar
    Join Date
    Nov 2000
    Location
    Halifax, Canada
    Posts
    177

    Search Multiple Columns in Table - Best/Fastest Method?

    I am looking at doing a search through a custom table that has multiple fields for an item number.
    Example table fields: Item1, Item2, Item3, Item4, Item5, Item6, Quantity, Location, etc.

    I want to return ALL records that have an item number in any of the "item fields". Currently I have DD defined field (FC Field - LITM) that I am using for user entry of the item number to be found. I then use events rules in "Grid Record is fetched" to check the value of the FC Item field to compare to all field BC values and hide/display as necessary (on IF with multiple OR clauses). It turns out that this takes a fair bit of time to return the grid results and was wondering if there is a better way to search multiple fields for data like this??

    I found that for the application the fastest method is the manual 'user searching' each QBE field individually works best.
    However, I would maybe like something more 'elegant' as it might be a bit of a pain for user to search each column for his/her item data.

    Thanks!
    Mike MacKinnon
    IMP Group International
    EnterpriseOne tools RL 9.1.4.4, Application RL 9.0
    BI Desktop Publisher 11.1.1.7
    Browser IE10 & Chrome 62.0.3202.94

    - Financials, Inventory (Sales & Procurement), MRP, Manufacturing, Development Tools, BI Publisher

  2. #2
    Member nkuebelbeck's Avatar
    Join Date
    Mar 2012
    Location
    Minnesota
    Posts
    543
    the sql equivalent of SELECT * FROM table WHERE useriput IN(item1, item2, item3, item4); ?

    I think you'd need to get into jde C land. Personally never done this but using ER does seem painful.
    Analyst/Developer
    E1: 9.2 | TR: 9.2.1.4 | JAS: WebLogic | ES: OS400
    DB: DB2 | WebDev Client: Win7Pro (64 bit), VS 2010

  3. #3
    Senior Member
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas
    Posts
    1,552
    Quote Originally Posted by nkuebelbeck View Post
    the sql equivalent of SELECT * FROM table WHERE useriput IN(item1, item2, item3, item4); ?
    I read it as the sql equivalent of SELECT * FROM table WHERE fld1=1234 or fld2=1234 or fld3=1234...

    Either way, this is easily done using the JDEBASE C APIs. Not sure if your tools release has it but there is also the C API XRCS_parseAppQueryWhere to extract the WHERE clause from an APPL query (much the same way we have always been able to extract the WHERE clause from a UBE section in C using ubeSection_GetDataSelection) and then you can append stuff like the item search criteria. What gets harder is then returning that result set to the APPL grid. Its possible but its not always pretty. There is an ER system function called "Set Selection Group" which, theoretically, would allow you programatically append your item search to the user's QBE/Filter critearia. Something like:

    Code:
    select *
    from table
    where (field1=UserFilterValue and field2=UserFilterValue) AND (ItemField1=1234 or ItemField2=1234 or ItemField3=1234)
    I say "theoretically" because "Set Selection Group" is kind of half baked. I have used it with varying success. You might get it to work, just review the resulting SQL in your JAS log to make sure it is doing what you want.

    What you want is the above, what you will probably get is the following... which will still work.

    Code:
    select *
    from table
    where (field1=UserFilterValue and field2=UserFilterValue) AND (ItemField1=1234 or (ItemField2=1234 or (ItemField3=1234)))
    Last edited by BOster; 06-19-2019 at 01:55 PM.
    Brian Oster
    Application Development Manager
    E1: 9.0 (TR9.1.5.1) / 9.2 (TR9.2.2.2)
    JAS/BSSV: Weblogic 12.1.2 / Weblogic 12.2
    ES: Win2008 / Win2016
    DB: MSSQL 2014 / 2016
    WebDev Client: Win7Pro / Win10Pro

  4. #4
    Senior Member DSauve's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA
    Posts
    1,246
    I see that you're on E1 apps 9.0 with tools 9.1.4.4, so this won't help you now, but once you're on apps 9.2 with tools 9.2+ (exact subrelease I'm not sure of), you can use the EnterpriseOne Search to search multiple columns in a table or view and return all records matching any of those columns.
    Don Sauve
    Wagstaff, Inc.
    E1 9.2, Win2016, SQL Server 2016, TR 9.2.2.6, WebLogic 12.2.1, BI Publisher

  5. #5
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,235
    Mike,

    consider a virtual table approach where you:
    1. Create a DB View using SQL that normalizes your data from 1 row with 6 Item# fields to upto 6 rows with a a single item # field.
    2. Describe the view / virtual table in JDE as a table and Business View.
    3. Build a normal Search/Select form against the BSVW.

    Alternatively fix the design and normalize the source data.
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  6. #6
    Member Mike Mackinnon's Avatar
    Join Date
    Nov 2000
    Location
    Halifax, Canada
    Posts
    177
    Thank you!!
    I did not think there was anything that I could do (on current release) using standard JD tools/ER...but I though maybe someone ran across this before.
    I thought about creating a SQL view and using that as the basis for a bus view but decided against it. I may go back to it is the user finds the current solution too cumbersome.

    Thank you all for the replies!

    * The SQL equiavalent would have been:
    select * from PRODDTA.F550000 where FIELD1 = 'ITEM' or FIELD2 = 'ITEM' or FIELD3 = 'ITEM' or FIELD4 = 'ITEM' or FIELD 5 = 'ITEM' or FIELD6 = 'ITEM'
    Mike MacKinnon
    IMP Group International
    EnterpriseOne tools RL 9.1.4.4, Application RL 9.0
    BI Desktop Publisher 11.1.1.7
    Browser IE10 & Chrome 62.0.3202.94

    - Financials, Inventory (Sales & Procurement), MRP, Manufacturing, Development Tools, BI Publisher

  7. #7
    It may not be much better than they are currently doing, but based on your SQL stated above, couldn't they use a query setup against the app that has all those fields listed with the condition "Match Any' selected. Then they would go into the query and change the value for each selection to the item number needed and run it to get all the records at once instead of one at a time. Just a thought.

  8. #8
    Member nkuebelbeck's Avatar
    Join Date
    Mar 2012
    Location
    Minnesota
    Posts
    543
    gotta ask. why do you have 6 fields for item number?
    Analyst/Developer
    E1: 9.2 | TR: 9.2.1.4 | JAS: WebLogic | ES: OS400
    DB: DB2 | WebDev Client: Win7Pro (64 bit), VS 2010

  9. #9
    Member Mike Mackinnon's Avatar
    Join Date
    Nov 2000
    Location
    Halifax, Canada
    Posts
    177
    We have a setup for warehouse locations that store some smaller parts/items.
    A location can be split into possibly 6 sub locations (small items like screws, nuts, etc).

    So the record looks like this (main fields) - Location, Aisle, Bin, Location03, Location04, Item1, Item2, Item3, etc.:

    H06F21 H 06 F 21 Q32527/14WV4SE R38917/36XT4PV R38609/26ZE8JN 020-1261-008 SUPPORT HFXJDE15 118311 120000
    H06F23 H 06 F 23 860-6801-210 SUPPORT HFXJDE15 118311 120000

    So for example I want to look in table to find if Part "R38917/36XT4PV" has a defined location and what it is (I want to search all records and all 'item' fields).
    This lets the user know if there is a defined location already for the item being received and where to put away.
    Last edited by Mike Mackinnon; 06-20-2019 at 12:42 PM.
    Mike MacKinnon
    IMP Group International
    EnterpriseOne tools RL 9.1.4.4, Application RL 9.0
    BI Desktop Publisher 11.1.1.7
    Browser IE10 & Chrome 62.0.3202.94

    - Financials, Inventory (Sales & Procurement), MRP, Manufacturing, Development Tools, BI Publisher

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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.