sql

  1. E9.2 SQL client AS400-DB2 free

    Hello, please may i ask for your recomandation? For SQL i am using "IBM i Access Client Solution". It is nice simple tool, but "SQL Developper from Oracle is much more better. " Exist any good free alternative? For "SQL developper" not found working JDBC driver for AS400-DB. Thank you very...
  2. E9.2 F0150 Address Organization Structure Master - Find Top Parent with SQL

    In order to do some reporting outside of E1, I need to find the top parent for any given child in F0150. The hierarchies vary in the number of levels and the child entered may not be the bottom child. How can I accomplish this in a SQL query? I'm hoping someone has done this before and I...
  3. E9.2 SQL to fetch F983051 (Versions) F98306 (PO Template) relationship

    Hello, I'm trying to build an SQL relationship between tables F983051 (Versions) & F98306 (PO Template), but I'm unable to understand how the BLOB in F983051 is ordered. I was able to debug BSFN B9800030 & it basically loops through each one of the "1" type values of the PO Template (in the PO...
  4. cazzafed

    E9.2 Execute SQL from E1 Application

    Hello all, can execute SQL update/delete/select from enterprise one application? I'm using 9.2 Best regards.
  5. E9.2 Oracle SQL F00165 coming with "0" at each character

    I created the following DB function, which I expect will retrieve the text from table F00165. (Usage in F00165): The problem is that it retrieves the text in the following BLOB format: This is an issue because when I convert this into CLOB, those zeroes are converted into "invisible"...
  6. A7.3 F3294 Config String History Join with F4211 Sales Order *Help Please*

    I'm Not sure which fields are the correct field to join with or what join to use. (between F3294 and F4211, F4211 as the primary file) Everything thing I try, the data in the config string either ends up wrong or incomplete. I'm just wondering what fields I should join with in F4211 to get...
  7. E9.2 Non-APPL dependent queries and watchlists?

    Hello Team, I'm trying to understand a little bit better the capabilities of the UDOs Queries and Watchlists. I see that a query would be -in essence- an application-based filtered fetch. From a couple videos and guides, I see that they always create a query using an application (I guess it...
  8. BookClub

    E9.2 Converting Attachments and Placing Them Back

    Hello! I recently converted all of my attachments from RTF to HTML. I did this by pulling the GDTXFT field from F00165 using SQL and changing it from an image into readable RTF text like so: cast(cast(F00165.GDTXFT as varbinary(max)) as nvarchar(max)) I then changed the RTF as needed to make it...
  9. E9.2 Can a query be build in JDE using 'EXECUTE IMMEDIATE'-like functions?

    Let's say I have a table in JDE from which I can assemble a SELECT-type query, including fields, tables, JOINs, &c. Somehow I can add the logic to build up a string that would look like my sentence. Is there a way to execute that query & retrieve the results into a Fetch-like command for...
  10. SQL Developer amazingly slow

    I'm not sure if I'm doing something wrong, but I simply cannot run any query without having to take a lot of time. I did this: & got this: I've no idea what's going on, or if it's on SQLDeveloper side. But this happens with every query I try to run. Any ideas?
  11. E9.2 Is there an application to test a BSFN?

    Hi, I'm trying to convert the BLOB file of a data structure pulled from F98743. However, using UTL_RAW.CAST_TO_VARCHAR2, HETTORAW & DBMS_LOB.SUBSTR is giving me weird results. I have relied on these functions to convert versions' BLOBs. I'm not sure if I'm doing something wrong. My query is...
  12. E9.2 How to trim in DataRequest ?

    Does anyone have any idea on how to imitate SQL TRIM functionality in Data Request within Orchestrator ? I have a data request based on a UDC but my input isnt working because of all the spaces within the feild which makes it impossible to get the value i'm looking for. Help ?!? :P
  13. WIP - Open Work Orders per Workcenter/ Sequence

    Hello everybody! For the Shop Floor Control context, how would you determine the amount of open work orders per workcenter (Business Unit)? I am working on a SQL to get a daily report. For now I would guess I would check in F4801 for orders where status <90 and then reference to F31122Z1 where...
  14. Securing Access to iNavigator SQL Scripts and ACS SQL Scripts

    To Secure Interactive SQL I can: - CRTAUTL, add only users that should have STRSQL - EDTOBJAUT, *PUBLIC *EXCLUDE, Remove any others, Add AUTHL STRSQL - Seems to block a profile not on the list for STRSQL (not authorized to command) How do we secure access to iNavigator SQL Scripts (Older Client...
  15. Financial Statements SQL Query - F0006 F0901 F0902

    First post on this site. Inherited a OneWorld Xe environment on Microsoft SQL Server. Need to pull in via SQL query from F0006 F0901 F0902. Anyone have this query available to read in balance sheet and P&L data? Basically trying to do an M&A financial roll-up to a new company defined in another ERP.
  16. Is there someone who already build a SQL query to load base prices in the P4106?

    Hello everybody, First time I am really playing with items, costs and prices.... Items mass upload is ok, costs also (via interop). But what about prices (F4106) I have 4000 items that are waiting to get a price.... Anyone can help me building a sql statement that will fill the F4106 and all...
  17. How to insert blob content into F00165 via SQL from SQL server to Oracle database

    Hi All, we have some pdf files stored on SQL server in BLOB field and I need your help with insert this BLOB field to F00165 (Oracle database) via SQL. Program for copying from SQL server to Oracle database is written in C#. Thanks for your help. Petr
  18. Aludoq_GPM

    Hi fellows, tip of how to use a dynamic sql within JDE 8.0 7334

    Few years ago, i'd a trouble with performance to extract data from large table (like Cardex). At this time we've used a mix approach with Oracle SQL call directly by the JDE, putting the data to a temporally table to be showing in JDE. The picture in attach show how this solution works. Table...
  19. Top Tables in E1 /DB2

    By chance anyone has a SQL command in handy which pulls out the top 10 or top 30 tables on E1/DB2 I sense that these are the big ones F4211,F0101,F4101,F0911,F4311 But need more data Thanks in advance
  20. DSauve

    Question about accessing SQL Server db using jdbc

    We have a SQL Server database that we would like to access from JDE E1. I've created a system DSN to point to the database, created a system user for myself and set up OCM mappings, and can access a table in this database from UTB. If I try to access this same table from DATABROWSER on my...
Back
Top