sql

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...

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...

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...

Hello all, can execute SQL update/delete/select from enterprise one application? I'm using 9.2 Best regards.

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"...

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...

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...

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...

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...

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?

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...

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

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...

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...

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.

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...

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

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...

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

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...