Jason Nichols K Absent Member.
Absent Member.
193 views

SQL Rule gives message "Wrong number of value returned by SQL"

Jump to solution

This is a very strange one that I haven't seen before.  In our development environment, all the rules on a request type are working perfectly, but after a recent migration to test, some of the rules are now giving an error message that the results are returning the wrong number of results.  There is only one field in the Results section and the SQL in question is SELECT NULL, NULL FROM DUAL.  Last time I checked, there are two results being returned so it should be working perfectly.  Has anyone else seen anything like this, and if so, what did you do to fix this issue?

 

Jason

0 Likes
1 Solution

Accepted Solutions
Absent Member.. alex-h Absent Member..
Absent Member..

Re: SQL Rule gives message "Wrong number of value returned by SQL"

Jump to solution

Hello,

 

I got this issue in the past.

 

The root cause is there is a "hole" in the Rule sequence number.

 

Go to PPM workbench => request types => the request type where you have the issue

On Rules table, please focus on the sequence number and follow it one by one.

 

1

2

3

 

and you will find a missing number

 

5

7

8

9

 

So here the rule #6 is missing.

 

The solution is to move the hole on top or on the bottom.

You can do this by moving neighbour rules.

 

This is the only solution. And check before your DEV environment. It's possible that the DEV environment is the first location where this issue happens.

This issue happens sometimes, perhaps when a crash from the workbench when the developer wants to "save" on the request type page.

 

Kind regards,

François-Alexandre

3 Replies
Absent Member.. Alonso_A_PPM Absent Member..
Absent Member..

Re: SQL Rule gives message "Wrong number of value returned by SQL"

Jump to solution

Hello Jason,

 

Could you please add some snapshots of the rules and the error in question as well? Also, if you can attach the request type here so I can test in-house would be great.

 

Which PPM version you are using?

 

Alonso

 

 

0 Likes
Absent Member.. alex-h Absent Member..
Absent Member..

Re: SQL Rule gives message "Wrong number of value returned by SQL"

Jump to solution

Hello,

 

I got this issue in the past.

 

The root cause is there is a "hole" in the Rule sequence number.

 

Go to PPM workbench => request types => the request type where you have the issue

On Rules table, please focus on the sequence number and follow it one by one.

 

1

2

3

 

and you will find a missing number

 

5

7

8

9

 

So here the rule #6 is missing.

 

The solution is to move the hole on top or on the bottom.

You can do this by moving neighbour rules.

 

This is the only solution. And check before your DEV environment. It's possible that the DEV environment is the first location where this issue happens.

This issue happens sometimes, perhaps when a crash from the workbench when the developer wants to "save" on the request type page.

 

Kind regards,

François-Alexandre

Jason Nichols K Absent Member.
Absent Member.

Re: SQL Rule gives message "Wrong number of value returned by SQL"

Jump to solution

Because of a time constraint, we ended up migrating the Request Type from Production back down to Dev and then reapplying the changes that were made and this did take care of the issue.  However, we had migrated the request type into a Sand Box environment and was able to verify the "hole" theory of the Rules.  There was a hole in the sequence and I was able to move a Rule down to the bottom of the list and back up to where it was originally located.  After saving the Request Type and reloading a request that was having the issue, the issue is no longer happening.  Thanks for that tip!

 

Jason

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.