bigoteetoe
Active Member
Is it possible to crate views from within SQL Server and then have JDE access it as any other table? Or are we limited to just using whatever tables and business views we create within JDE?
clee
Active Member
No. Yes.
Chi Lee
IT Project Leader, Va International Terminals, Inc.
B7331 SP11.3, NT4 ES/DS, Citrix, Win2K clients, SQL Server7
Chi Lee
IT Project Leader, Va International Terminals, Inc.
B7331 SP11.3, NT4 ES/DS, Citrix, Win2K clients, SQL Server7
Randy
Active Member
You can create a SQL select, name it and then create a table within JDE with the same name. Put the Data Values into the table but DO NOT generate the table. Make a Business View from the table. In this way, when you call the business view which was created from the table, your SQL select will execute.
Thank You,
Randy Epps
Senior JDE Developer
One Source Consulting
(214) 538-6759
www.1sourceconsulting.com
Thank You,
Randy Epps
Senior JDE Developer
One Source Consulting
(214) 538-6759
www.1sourceconsulting.com
Scott Beebe
Reputable Poster
There is one client where we did this. We called it a virtual table, don't know if there is a better name for it. We had to create the table definition in OneWorld first. Then we created the view in the SQL database to exactly match our definition of the table we defined in OneWorld. You need to name the view the same as your table, F55XXXX. You will also need to name all of the fields in the view the same as the alias names in the table definition.
We used this as a clever way to view and import data from custom tables the client was using to store data for an in-house application.
We used this as a clever way to view and import data from custom tables the client was using to store data for an in-house application.
Pon
Well Known Member
A good rule of thumb to remember is that you have to "define" the table/view in OneWorld if you'd like to utilize the table/view in a OW app. However, be careful not to "generate" the file or you may blow the info in your custom file away (provided the file existed first in some other software). For example, many co-existent companies have custom World files that they'd like to utilize in OneWorld. Therefore, they have to define the custom World file in OneWorld exactly as it appears in World but, not generate it in OneWorld. Best of luck.
Wkunneke
Active Member
To expand on this just a bit...
We did this in Oracle and I've been told it works just as well in SQL Server. We actually thought of this and used it to get around the short field names used in JDE. We had an Oracle table maintained by a GIS application that we needed to access from JDE. Using the AS clause, the Oracle View was created with field names that matched the table definition we created in JDE. As stated by others: DO NOT GENERATE THE TABLE. Just define it. Worked perfect for us.
Bill
We did this in Oracle and I've been told it works just as well in SQL Server. We actually thought of this and used it to get around the short field names used in JDE. We had an Oracle table maintained by a GIS application that we needed to access from JDE. Using the AS clause, the Oracle View was created with field names that matched the table definition we created in JDE. As stated by others: DO NOT GENERATE THE TABLE. Just define it. Worked perfect for us.
Bill
jdesmm
Well Known Member
I just want to take a moment to say that finding this thread is the best thing that has happened for us since we started working with the JDE toolset! Like the first reply to this post, we were always told by our consultants that it couldn't be done, and we've been irked time and time again by the level of complexity to which we have had to go in report design simply because we could not pre-group or pre-calculate in JDE business views or could not fit any more tables into the view.
This information gives us the power to greatly simplify our work going forward and possibly alleviate many performance issues we're having as well. Thank you! Thank you!
This information gives us the power to greatly simplify our work going forward and possibly alleviate many performance issues we're having as well. Thank you! Thank you!
bigoteetoe
Active Member
Thank you all for your responses. I am encouraged by your success stories. However, it does not seem to work for me. This is what I did.
1. Saved (did not generate) a table (F5708023) in JDE
2. Created a Business View (V5708023) in JDE using F5708023 above.
3. Created a Batch Application (R5708023) in JDE using V5708023 as the business view for a columnar selection
4. Created the MS SQL Server view TESTDTA.F5708023 in MS Query Analyzer (same column names, type)
However, when I try to 'preview' the report, I get the error:
Unable to locate table in specified datasource for section: F5708023 (SQL Server View) for R5708023.
Any ideas?
1. Saved (did not generate) a table (F5708023) in JDE
2. Created a Business View (V5708023) in JDE using F5708023 above.
3. Created a Batch Application (R5708023) in JDE using V5708023 as the business view for a columnar selection
4. Created the MS SQL Server view TESTDTA.F5708023 in MS Query Analyzer (same column names, type)
However, when I try to 'preview' the report, I get the error:
Unable to locate table in specified datasource for section: F5708023 (SQL Server View) for R5708023.
Any ideas?
Larry_Jones
Legendary Poster
Does the JDE SQL Database account have access (SELECT permissions) to the base table(s) in the view?
Randy
Active Member
Have you done the server package for all of these. The system will need to connect the table with the SQL statement before they can actually work. Also, ensure that you have the EXACT name for the table and the SQL stored Procedure.
Thank You,
Randy Epps
Senior JDE Developer
One Source Consulting
(214) 538-6759
www.1sourceconsulting.com
Thank You,
Randy Epps
Senior JDE Developer
One Source Consulting
(214) 538-6759
www.1sourceconsulting.com
Randy
Active Member
Some people are starting to realize that just because you hire a Big Name consulting firm, it does not guarantee that they have accomplished consultants.
Thank You,
Randy Epps
Senior JDE Developer
One Source Consulting
(214) 538-6759
www.1sourceconsulting.com
Thank You,
Randy Epps
Senior JDE Developer
One Source Consulting
(214) 538-6759
www.1sourceconsulting.com
Wkunneke
Active Member
I gave a presentation at Quest Global on this 
and it was well received. You left one step out...
The data source has to be set up and mapped properly in OCM. My CNC guy would be glad to post the details if you need them, just let me know in a reply.
and it was well received. You left one step out...The data source has to be set up and mapped properly in OCM. My CNC guy would be glad to post the details if you need them, just let me know in a reply.
Larry_Jones
Legendary Poster
William,
I don't know about the AS/400 but for Oracle or SQL Server you shouldn't need to create a new data source and OCM mapping as long as the database view is owned by / in the schema for TESTDTA.
Regards,
I don't know about the AS/400 but for Oracle or SQL Server you shouldn't need to create a new data source and OCM mapping as long as the database view is owned by / in the schema for TESTDTA.
Regards,
bigoteetoe
Active Member
The base tables of the view all have permissions given to: TESTDTA, public
The view I created only have permissions given to TESTDTA. I will try also assigning it 'public' permissions.
The view I created only have permissions given to TESTDTA. I will try also assigning it 'public' permissions.
bigoteetoe
Active Member
This was the perfect question to ask. Once I set the permissions on the SQL Server view to the same as the base tables, everything worked great!
To recap for others, here are the steps that let me use SQL Server views transparently as regular tables in JDE.
(1) Design the table in JDE table designer. DO NOT generate the table or indexes. Save the table.
(2) Create the View on SQL Server. Be sure to assign the view the same permissions as the base table(s). Make sure the columns are the same type and name as the columns you designed in JDE. Name the view the same as the table you designe in JDE (Fxxxx)
(3) Design the business rule in JDE to access the table as you would any other, and use it in any report as you would any other.
Thanks again to everybody for pointing me in the right direction where I needed it.
To recap for others, here are the steps that let me use SQL Server views transparently as regular tables in JDE.
(1) Design the table in JDE table designer. DO NOT generate the table or indexes. Save the table.
(2) Create the View on SQL Server. Be sure to assign the view the same permissions as the base table(s). Make sure the columns are the same type and name as the columns you designed in JDE. Name the view the same as the table you designe in JDE (Fxxxx)
(3) Design the business rule in JDE to access the table as you would any other, and use it in any report as you would any other.
Thanks again to everybody for pointing me in the right direction where I needed it.
Wkunneke
Active Member
The reason was that the view was on a box that was NOT part of JDE, I see the confusion. Sorry I left that little tidbit out
googers
Member
Larry
I would very much be interested in seeing how you guys setup the datasource and Sqlserver view. We are running JdeOneworld SP20 on solrais 8 Oracle 8.1.6.3. We are trying to connect a new datasource to a sqlserver Database but are failing in getting JDE to recognize the New datasource when we attemp to generate a table on it.
Thanks for any of your help.
I would very much be interested in seeing how you guys setup the datasource and Sqlserver view. We are running JdeOneworld SP20 on solrais 8 Oracle 8.1.6.3. We are trying to connect a new datasource to a sqlserver Database but are failing in getting JDE to recognize the New datasource when we attemp to generate a table on it.
Thanks for any of your help.
Larry_Jones
Legendary Poster
Tom,
The context of this thread was placing a "database" view in your PRODDTA (CRPDTA, TESTDTA, etc) schema that in turn referenced a table somewhere else in the database - the table(s) doesn't need to be in the same schema as the view. Then define the view to OW as a table - using OW data dictionary fields. Your field definitions in OW MUST MATCH the database view's definitions/data. If you don't understand how OW handles/stores Dates and numeric fields then tell us - if you have dates or numbers with decimal places your database view has to handle that.
By using this method you do not not need to create and map a new Data Source in OW - although you certainly can and we have done so here.
Failing to generate/create a table in a new datasource could be because you haven't created an OCM mapping for the Environment/table/datasource? Or the data source is defined incorrectly?
More detail please.
The context of this thread was placing a "database" view in your PRODDTA (CRPDTA, TESTDTA, etc) schema that in turn referenced a table somewhere else in the database - the table(s) doesn't need to be in the same schema as the view. Then define the view to OW as a table - using OW data dictionary fields. Your field definitions in OW MUST MATCH the database view's definitions/data. If you don't understand how OW handles/stores Dates and numeric fields then tell us - if you have dates or numbers with decimal places your database view has to handle that.
By using this method you do not not need to create and map a new Data Source in OW - although you certainly can and we have done so here.
Failing to generate/create a table in a new datasource could be because you haven't created an OCM mapping for the Environment/table/datasource? Or the data source is defined incorrectly?
More detail please.
googers
Member
Larry
Thank you for the response. We have successfully
created a custom JDE table on OneWorld that reads data
from an Oracle view. This is a graet way to gather
summary data via the database engine.
To help better clarify my question to you I will try
to explain it this way:
All of our datasources are connected to oracle
databases.
We have another application in house that uses
Sqlserver as it's database. We were thinking that we
would like to create another datasource in JDE so that
we could view the Sqlserver database. (Perhaps this
would be a way to interface the two applications.) We
Generate a new datasource that points to sqlserver.
Then we create a new table definition. The problem
arises when we then try to generate that table using
the new datasource. The new datasource does not appear
in the datasource drop down,as a choice in the genrate
table form. This is true even if we bounce services.
The table also cannot be viewed view UTB.
I guess osme questions may be:
1)do we need to bounce services after creating a new
datasouce. If so where is the new entry placed (in
what directory on NT and/or Unix)?
2) You talk about OCM mapping for
Environment/Table/Datasource. What is the application
number for this (i.e. P9861151)? DO we need to bounce
services after updating this?
--- Larry_Jones <[email protected]> wrote:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OWDEV&Number=59693
Once again, all you help is very much appreciated
Tom Goehring
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
Thank you for the response. We have successfully
created a custom JDE table on OneWorld that reads data
from an Oracle view. This is a graet way to gather
summary data via the database engine.
To help better clarify my question to you I will try
to explain it this way:
All of our datasources are connected to oracle
databases.
We have another application in house that uses
Sqlserver as it's database. We were thinking that we
would like to create another datasource in JDE so that
we could view the Sqlserver database. (Perhaps this
would be a way to interface the two applications.) We
Generate a new datasource that points to sqlserver.
Then we create a new table definition. The problem
arises when we then try to generate that table using
the new datasource. The new datasource does not appear
in the datasource drop down,as a choice in the genrate
table form. This is true even if we bounce services.
The table also cannot be viewed view UTB.
I guess osme questions may be:
1)do we need to bounce services after creating a new
datasouce. If so where is the new entry placed (in
what directory on NT and/or Unix)?
2) You talk about OCM mapping for
Environment/Table/Datasource. What is the application
number for this (i.e. P9861151)? DO we need to bounce
services after updating this?
--- Larry_Jones <[email protected]> wrote:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OWDEV&Number=59693
Once again, all you help is very much appreciated
Tom Goehring
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
