Absent Member.. kruegke Absent Member..
Absent Member..
154 views

CASE Statement Errors in Request Type Rule

I have a Request Type with three Table Components, and a rule to add up specific values entered on those tables.  The rule is configured as below, and works perfectly as long as there is a row entry in each table:

SELECT
  '[REQD.T.BCW_BENEFITS_TABLE.TC.VP.BCW_BENE_YEAR_0.TOTAL]' - '[REQD.T.BCW_OC_TABLE.TC.VP.BCW_OC_YEAR_0.TOTAL]' - '[REQD.T.BCW_OTC_TABLE.TC.VP.BCW_OTC_YEAR_0.TOTAL]',
  '[REQD.T.BCW_BENEFITS_TABLE.TC.VP.BCW_BENE_YEAR_0.TOTAL]' - '[REQD.T.BCW_OC_TABLE.TC.VP.BCW_OC_YEAR_0.TOTAL]' - '[REQD.T.BCW_OTC_TABLE.TC.VP.BCW_OTC_YEAR_0.TOTAL]'
FROM DUAL

However the rule errors the RT if one of the tables contains no entries.  So I decided to add a CASE statement to the rule to check for the presence of entries in the tables, which I configured as follows:

SELECT
CASE
 WHEN TO_NUMBER('[REQD.P.BCW_BENEFITS_TABLE]') > 0 AND TO_NUMBER('[REQD.P.BCW_OC_TABLE]') > 0 AND TO_NUMBER('[REQD.P.BCW_OTC_TABLE])' > 0 THEN
  NVL('[REQD.T.BCW_BENEFITS_TABLE.TC.VP.BCW_BENE_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OC_TABLE.TC.VP.BCW_OC_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OTC_TABLE.TC.VP.BCW_OTC_YEAR_0.TOTAL]',0),
  NVL('[REQD.T.BCW_BENEFITS_TABLE.TC.VP.BCW_BENE_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OC_TABLE.TC.VP.BCW_OC_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OTC_TABLE.TC.VP.BCW_OTC_YEAR_0.TOTAL]',0) 

ELSE
  0,0
END
FROM DUAL

For some reason, this errors out as well, although the associated values shown in the failing rule error message look to be valid.

Any idea what the problem is, or is there another way to check for table content in a rule?

Thanks.

 

0 Likes
3 Replies
Natalia_R_PPM Absent Member.
Absent Member.

Re: CASE Statement Errors in Request Type Rule

Hello Kruegke.

Could you please attached the error here.

Thanks 

Natalia 

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
Community Manager changl Community Manager
Community Manager

Re: CASE Statement Errors in Request Type Rule

looks good maybe a syntax error?

 

TO_NUMBER('[REQD.P.BCW_OTC_TABLE])' > 0

                                                                

 

 

0 Likes
Highlighted
Absent Member.. Oscar_Pereira Absent Member..
Absent Member..

Re: CASE Statement Errors in Request Type Rule


Hello,

I do not think that the case is needed, the token REQD.P.BCW_OC_TABLE is not returning anything...thats why it fails.


Can you test just by adding the NVL:

SELECT
NVL('[REQD.T.BCW_BENEFITS_TABLE.TC.VP.BCW_BENE_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OC_TABLE.TC.VP.BCW_OC_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OTC_TABLE.TC.VP.BCW_OTC_YEAR_0.TOTAL]',0),
NVL('[REQD.T.BCW_BENEFITS_TABLE.TC.VP.BCW_BENE_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OC_TABLE.TC.VP.BCW_OC_YEAR_0.TOTAL]',0) - NVL('[REQD.T.BCW_OTC_TABLE.TC.VP.BCW_OTC_YEAR_0.TOTAL]',0)
FROM DUAL

 

Thanks,
Oscar P

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.