I am wondering how to query this table to find out if a field is part of any index. I have all I need to query by index1, but I am wondering how I would handle the ttadv421.part()- which is part of an array table field. I did not want to return all 24 fields and search thru them individually, but I will if that is the only solution. Open to suggestions - even using a tools function I do not know about.

Thanks!

Mark

Mark,

I would ask you $$$ to tell you the solution to this query ;)

But it was an interesting issue and I will tell you the logical steps and tips to make a generic function.

1. Set Package VRC.

2. Check if the Table is - present, not found or expired.

3. Find the correct VRC of the last found table.

4. Find the number of indexes for that table VRC.

5. Read each index and the fields included in each index.

6. Find number of parts for each index.

7. Verify if each field is the requested field from the parts array. Then you found that your field is used in index.

8. If a combined field is used in the index, then find the child fields of that combined field and verify if it is the requested field. Then you found that your field is used in index.

You can use the rdi.* functions to make this algorithm work.

Good Luck!

Well it never hurts to try. :)

BUT I already have the solution you described, well except for using the RDI commands and those return information about tables and specific indexes. What I was hoping for was a way to build the ttadv421.part() into the where clause. After looking at the table long enough I realized that this could not be done because the field I am looking for could be in any of the parts. And even worse than that is the field could be part of a combined index which is then in the table and not the specified field. So actually my program almost mirrors your algorithm. Great minds think alike. Here are the differences free of charge. :)

1. Set current VRC to users VRC. I do this so I can skip what developers are working on and start with the production layer.

2. Find all tables for a given domain.

3. Check table to see if it is the correct table for the VRC struture. I have to do this because none of our environments are the same.

4. Check table field to see if it is used.

5. If the stars are aligned from 3 and 4, then count the rows in the table.

6. This is where I do what you mention in steps 5, 6, 7, 8.

7. All done so write my table and get the next table and field.

8. The last step will be to write Oracle scripts to do mass data changes on this fields.

Mark

Since you dont seem to pay me $$$ I will still help you being my BaaN buddy on here.

Here are the differences free of charge.

Do you mean fee or free? :D

After looking at the table long enough I realized that this could not be done because the field I am looking for could be in any of the parts. And even worse than that is the field could be part of a combined index which is then in the table and not the specified field.

you have to use these functions - rdi.index() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_runtime_dictionary_information_rdi_index) and rdi.column.combined() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_runtime_dictionary_information_rdi_column_combined). Then you can check in the part field - if it is a table field or a combined field. If combined field you should then get the child fields.

2. Find all tables for a given domain.
4. Check table field to see if it is used.

This looks like a performance intensive process.
3. Check table to see if it is the correct table for the VRC struture. I have to do this because none of our environments are the same.
In our view, every environment has to be similar VRC, database, OS structure. We ensure this because we like to catch all the issues or simulate a production software code migrations when we do a code migration from Dev to QA. This gives us a downtime estimation required for us for a production migration. We have all the production companies refreshed from Production to Dev and QA, and we have all the table counts and time estimation for a table reconfigs.
8. The last step will be to write Oracle scripts to do mass data changes on this fields.
I am not sure whats your requirement is but what kind of changes are being made and why do you like to use Oracle scripts ?

Hi NPR,


you have to use these functions - rdi.index() and rdi.column.combined() . Then
you can check in the part field - if it is a table field or a combined field. If
combined field you should then get the child fields.


Not if I do not want to. :) It only takes a couple of queries to find what I want. Not too bad.


This looks like a performance intensive process.


Not really. I was suprised on how quick it ran for the projects domain. The worst part is counting the rows - everyone just has to know how many rows are in these tables that are going to be changed.

In our view, every environment has to be similar VRC, database, OS structure.
We ensure this because we like to catch all the issues or simulate a production
software code migrations when we do a code migration from Dev to QA. This
gives us a downtime estimation required for us for a production migration. We
have all the production companies refreshed from Production to Dev and QA, and
we have all the table counts and time estimation for a table reconfigs.

Well also like to keep our test environments in sync with production also - BUT we have a problem in our sync patch company and in production. Baan has been working on this for about 2 months now, and we are hesitant to break our dev environment. It causes some problems but we have been able to work around it.


I am not sure whats your requirement is but what kind of changes are being
made and why do you like to use Oracle scripts ?


The changes that are being made are things like re-naming ALL of the following: projects, dimensions, CPC codes, Cost pools, etc. We are doing a Baan finance implementation and are attempting to "improve" things from our manufacturing implementation 4 years ago. Oracle scripts appear to be the fastest way to make massive data changes on these tables. Plus this also gives my Oracle DBA's time to play with our test environments while doing this.

The original program actually did the export, used awk/sed to replace data, then imported the table back. This took way to long, but worked great for small tables. So I started looking at how the DBA's refreshed a company. They run scripts to change the company number, so we are going to apply that same technique to doing these mass changes.

This has been a fun type program to work on, but I am always open to suggestions. :)


Mark

I am wondering how I would handle the ttadv421.part()- which is part of an array table field. I did not want to return all 24 fields and search thru them individually


Mark,

You might consider making a generic function to convert a multi-dimensional array to a single array and do a perfect pattern match using the - pos() (http://www.baanboard.com/programmers_manual_baanerp_help_functions_string_operations_pos_rpos)

I made a function in my generic dll which does exactly the opposite of this.

function extern convert.array.1dto2d( const string sourcestr(),
ref string result(,) )
{
DLLUSAGE
This function takes a single one dimension array of string with a separater (,)
and then makes a double dimensional array with the number of elements and the
column size equivalent to the longest element. After the return of the function
call Use - standard tools function array.info(array, nr.dims, dim.array ) to
find the array dimensions.
ENDDLLUSAGE

found another function in the tools dll - ttdllextint


get.tablefield.info
long get.tablefield.info( domain ttadv.cpac cpac, domain ttadv.cmod cmod,
domain ttadv.flno flno, domain ttadv.vers vers, domain ttadv.rele rele,
domain ttadv.cust cust, domain ttadv.fdnm fdnm, ref domain ttadv.cpac pacd,
ref domain ttadv.cdom cdom, ref domain ttyeno mand, ref domain ttadv.clab clab,
ref domain ttadv.cpac rfpa, ref domain ttadv.dept dept, ref domain ttyeno acti,
ref domain ttyeno comb, ref domain ttadv.expr defa mb )


Desc: get properties of table-field
Par : cpac - package code
cmod - module code
flno - tablename
vers - version
rele - release
cust - customer
fdnm - fieldname
pacd (output) - package code domain
cdom (output) - domaincode
mand (output) - mandatory field
clab (output) - label code
rfpa (output) - package of ref-table (if empty no ref-table)
dept (output) - nr of array-elements (depth) of field
acti (output) - active?
comb (output) - combined field?
defa (output) - default expression
Returns: 0 when field found
-1 when not found