Hi,
Please take note that I am new and still learning at any moment ....

I try to validate the resulting field type of a query.

I figured out that the field type comes from the related domain entry.
So I try to get the related domain (ttadv400) entry for a field, from Table Fields (ttadv422).

If I use the ttadv420.cdom field to relate to the ttadv400.cdom, it works slowly ..
So I tried with the combined field ttadv400.cmba.

The problem is the following : we have 2 VRCs. The fields comes generally from the BASE version, some other from the LAB. We are running the LAB. It seems that the current (working) "version - release - custumer" are not the same as the one used by the domain entries.

So I wonder, how the relation between a table field (ttadv420) and the corresponding domain entry (ttadv400) is done ?

I hope it is understandable !
Thank you,
Fred

Just wondering if you have looked rdi.column instead? Try searching the forum on it.

Not sure about the other question.. Are you saying the table and the fields are in one VRC and the domain in another? In that case the table in LAB VRC will use the domain in the LAB VRC. But if someone logged into the base VRC then that table would use the base VRC domain.

Hi mark_h,
I can not use rdi.column since I am using SQL queries through qptool.

The main problem is that the not domain refer to the LAB VRC and we are using it. So there must be some kind of inheritance between the old and new VRC domain, that makes a field get is properties from not the current VRC but the inherited one ?

Normally doing such things are better done in baan with rdi functions and dynamic query . Still if u have valid reasons for qptool , u can go ahead.

Here are some problems with solutions
1. VRC's reading order should be had from ttadv112.
2. For enum fields , u will need domain to be read further from ttadv400/1 along with ttadv112
3. If u need field labels , u may need to search ttadv140 (very complex ) .
4. For combined fields in ttadv422 , u may need to search child field in ttadv423 and then the same algo for real fields .
6. Ideally if u use index , it will be faster for qptool .For index fields , u may need to scan ttadv421 , ttadv423 and then ttadv400.
6. Further field alignment , case conversion , legal and illegal characters , string length and range validation , range message etc are the things that u may have to take care of in program writing sql for qptool before calling the same.
7. Finally all this is application dictionary and not runtime dictionary which may differ some times leading to errors .
8. Further table level normal user authorizations will be ignored by application outside baan .

I figured out some things :
- the field from ttadv422 is defined by is cdom entry in ttadv400.cdom
- the cdom entry in ttadv400 depend on the packages VRC
- the related ttadv422.cdom package is in ttadv422.pacd
- the right VRC for a packages is defined in ttadv111 ... right?
- So I think that the right VRC for a package is set in the fields : ttadv111.tver, ttadv111.trel, ttadv111.tcus
- those fields got multiplicated physical length

I wonder if the ttadv111.tdep would point the right entry ?
How to gather this information using simple SQL ?

I found out that "SELECT tdep, tver(3) from ttadv111" give the third elem in the array. How to make the vector dynamic ?

Thanks a lot Hitesh Shah,
I don't know why, but the table ttadv112 doesn't exist.

I will test the other suggestions as soon as I can.

Thanks a lot Hitesh Shah,
I don't know why, but the table ttadv112 doesn't exist.

I will test the other suggestions as soon as I can.

The table ttadv112 was added long back (probably 9-10 years back as under project called rapiditas to improve performance of tools programs) . If u dont have that table , u r left with ttadv111 only .

If ur VRC list is short and it's also in alphabetical order (combined) , then u can search everything in descending order of VRC skipping ttadv111 .

It 's seems that I get correct performance with this query :

SELECT ttadv422.fdnm, ttadv422.cdom, ttadv422.fdno, ttadv400.type, ttadv400.desc, ttadv400.leng, ttadv400.type FROM ttadv422, ttadv400 WHERE |
ttadv422.cmbd = {"cpac","cmod","flno","vers","rele","cust"} |
AND ( ttadv422.cpac = ttadv400.cpac and |
ttadv422.cdom = ttadv400.cdom ) |
ORDER BY ttadv422.fdno ASC

If you someone have an idea about how to optimize it without the ttadv112 table, It would be appreciate.

It 's seems that I get correct performance with this query :

SELECT ttadv422.fdnm, ttadv422.cdom, ttadv422.fdno, ttadv400.type, ttadv400.desc, ttadv400.leng, ttadv400.type FROM ttadv422, ttadv400 WHERE |
ttadv422.cmbd = {"cpac","cmod","flno","vers","rele","cust"} |
AND ( ttadv422.cpac = ttadv400.cpac and |
ttadv422.cdom = ttadv400.cdom ) |
ORDER BY ttadv422.fdno ASC

If you someone have an idea about how to optimize it without the ttadv112 table, It would be appreciate.

OK after further testing, it doesn't work : still need to specify the right cdom's "vers","rele","cust" to join the ttadv400 ... shit..