Hi,

the problems with new porting sets 6.1.c.05.02 or higher have already been discussed here and by baan support.

But I have a piece of code that is a bit more tricky:


...
and {:tiedm110.comp,:sitm} refers to tiedm100 unref clearunref
...


Any suggestions to rewrite the code?

Is there a reason you can not separate this into a new query inside the selectdo statement?

Mark

Yes. The code is a select with 12 tables, a big selectdo part, written 3 years ago by a person that is no longer in our company ... So I would like to change as least as possible.

Günther

Maybe if you post all of the select someone with better skills than me will see something that can be done. On a side note can you post a link to the discussion on the porting set so I know which thread you are referring to and maybe even a case number at Baan that I can look at.

Thanks

Mark

günther,

I found this in our help manual -

REFERS TO syntax
A REFERS TO statement has the following form:

<from> REFERS TO <to> [PATH <path> [,<path>...]] [UNREF<mode>]
The following table explains the various parts of the statement:

<from>
The referring table field or table.

<to>
The table referred to.

<path>
The path via which reference is reached (always table fields). If PATH is specified, specifying a table field for <from> is mandatory. For example:

WHERE table1.field REFERS TO table4.field
PATH table2.field, table3.field

<mode>

A mode indicating system action if reference does not exist; possible values are:

SKIP If a reference cannot be found, the record is skipped.
CLEAR If a reference is empty or absent, the referring record is filled with spaces or 0 (numeric).
SETUNREF The value of an undefined reference is filled with an 'undefined reference' sign, defined in the data dictionary, or with 0 (numeric).
CLEARUNREF The referred record is filled with spaces or 0 (zero) when reference fields are empty. When the reference is undefined the referred record is filled with an 'undefined reference' sign.

Depending on the reference definition in the data dictionary, the default reference mode is:

reference mode in DD UNREF mode
mandatory SETUNREF
mandatory unless empty CLEARUNREF
not mandatory CLEAR

Example
SELECT ttadv100.*
FROM ttadv100, ttadv101
WHERE ttadv101.cmod BETWEEN "aaa" and "azz"
AND ttadv101 REFERS TO ttadv100 UNREF <UNREF_mode>
AND ttadv100.cpac BETWEEN " " and "zz"

The evaluation order of this query may be as follows:

Find all rows / records in ttadv101 that match the BETWEEN "aaa" and "azz" condition.
Find all references from each row from step 1 in table ttadv100.
Find all rows selected in step 2 which match the BETWEEN " " and "zz" condition
When some reference does not exist in ttadv100 (step 2 fails), because of the condition on the referenced table, the whole row is rejected.

You would have to change some of the SQL codes to be compatible with the New Query Processor (NQP). The code you pasted seems like a inrange or between on an index, I am not sure if index is allowed and the documentation says table/table-field.

1.4 Degree mismatch in refers to expression
Explanation:
Functionality will change when the number of fields at the left hand of the REFERS TO is different from the right side.
In the old QP, this always results in an inner join.
It will become an outer join.

Example:
cisli205.itbp refers to tccom112

Because the main index of tccom112 has two key parts, this query can now result in multiple records of tccom112.
In old QP (level 1 driver) always only ONE record was given.