I try the following select:

select qmptc120.*, qmptc049.*
from qmptc120, qmptc049
where qmptc049.orgn refers to qmptc120
selectdo
endselect

and get the "Error: Degree Mismatch in reference predicate...." error message. I know that there are some discussions about portingsets, concerning this error.
Is there a possibility to rewrite this select, that it works the same way, but without compile errors ?

Thanks
Spartacus

Briefly:
the error is given when you refer from one record to multiple records using refers to.

In the past this was accepted, but it violates the SQL standard and it yields unpredictable results.

refers to may only be used in a 1 to 1 relation, not in a 1 to many relation.

So solution in this case:
1) Add more fields in the selection so there becomes a 1 to 1 situation (if possible)
2) Do not use refers to.

You can revise your SQL statement. I'm not familiar with the table below but SQL interprets qmptc120 as index1 so make sure that your index1 is qmptc120.orgn otherwise it will not work.

"where qmptc049.orgn refers to qmptc120"

For example, the statement

"where {tdsls045.orno,tdsls045.pono} refers to tdsls041._index1"

is correct because tdsls041 index1 is made up of orno and pono fields.

Spartacus,

This is due to the changes in the BaaN's New Query Processor (NQP).

1.4 Degree mismatch in refers to expression
Description
In classic QP it is (or was) possible to have a “refers to” expression of which the number of parts in the left operand does not match the number of parts in the right operand. On (Oracle) level-2 this construction works as expected, but on level-1 it does not.
Proposal
With NewQP it is possible to have a “refers to” expression of which the number of parts in the left operand does not match the number of parts in the right operand.
Rational
This construction is mapped onto ANSI syntax.

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.

Hello,

Is there a way to identify the scripts that have illegal refers statements in them? A way other that putting eyes on each one?

Thanks,

philpom

Mark,

When BaaN notifies you that with some latest patches and porting sets that the NQP is delivered to you. You need to do a global compile on all the scripts and fix the compilation errors.

Alternatively, BaaN also has an internal utility (depends on BaaN if they choose to deliver to a customer or not), which can fix the code and report warnings for suspicious SQL usage.

To check your custom scripts, since it generates a compile error when you try to compile these without changing, try Compile Program Scripts (ttadv2230m000) for your VRC and put in all packages, all scripts. Send the output to the display, and search for "Degree" or whatever the error is.

Of course, best done in test. Make sure there is no junk in there you don't want compiled, or do this for ranges that you want to check.

That was the plan. Do level 2 or 3 compile wide open and pick the errors from the file. The problem was we couldn't find any errors.

Baan says the porting set 61c0502 and higher has the refers clause error. This may be true but what we found though was that you don't get the error on compile unitl you are on 61c07. That was where the confusion set in.

Doing a global compile on 61c07 will show you all affected scripts. Doing so on 61c05xx doesn't produce the compile error but still has the refers problem.

We found a workaround for our bx6.1 problem in 61c07 and upgraded. That is when we found this out.

Thanks for the comments, have a nice day.

Mark Philpott :D

WARNING -

Be careful with the different compile options.

This was in a test environment. We deleted everything after genrating a list of scripts and reports with the 'predicate' error. It seemed like the right thing to do.

Thanks,


Mark Philpott :cool: