RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions

[fishercat]

First of all I have to say, this is my first post and I apologize for the length of it, it's a bit convoluted so it needs some explaining...

This is the situation I have a grouped report with some calculated data items.  The report is working and all the calculated data items are working fine. 

I have to have group totals for 4 of the columns
   Group On-Call Days Paid – this group total is working fine   
   Fee_Paid_Amt   - this group total is working fine
        Tot Variance Days – when I add this group total (using the summarize function on the toolbar),  and run it, I'm getting an sql -126 error.  I ran a validation which returned the error below**
        Variance as Percent - (we won't worry about this one yet)

**<queryProblems><messageFolder componentID="RQP" maxSeverity="error"><messageFolder maxSeverity="error"><message severity="error" title="UDA-QOS-0006 Error" type="nativeSQL">UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "add" is invalid for the following combination of data types: "integer" and "character"
</message></messageFolder><message code="-126" severity="error" title="QE-DEF-0459 CCLException" type="general">RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "add" is invalid for the following combination of data types: "integer" and "character"
</message></messageFolder></queryProblems>

Is there any way I can pinpoint which data items are causing the issue?  This data item traces back to another data item – Committed Level of Coverage – which has the following expression – other data items are using results from the expression below with no issues so I'm quite confused. If anyone could shed some light on this I would really appreciate it.

case

#/*1 - Active for entire fiscal – no end date*/#
when
[BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] <= (?Fiscal_Year?)+'-04-01'
and
[BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] is null
then
_days_between (Cast((?Fiscal_Year?+1)+'-03-31' AS DATE), Cast((?Fiscal_Year?)+'-03-31' AS DATE))

#/*2 - Active for entire fiscal – future end date*/#
when
[BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] <= (?Fiscal_Year?)+'-04-01'
and
[BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] > (?Fiscal_Year?+1)+'-03-31'
then
_days_between (Cast((?Fiscal_Year?+1)+'-03-31' AS DATE), Cast((?Fiscal_Year?)+'-03-31' AS DATE))

#/*3 - Active at fiscal start – ended on or before fiscal end*/#
when
[BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] <= (?Fiscal_Year?)+'-04-01'
and
[BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] <=(?Fiscal_Year?+1)+'-03-31'
then
_days_between (Cast([BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] AS DATE), Cast((?Fiscal_Year?)+'-03-31' AS DATE))

#/*4 - Not active at fiscal start – no end date*/#
When
[BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] > (?Fiscal_Year?)+'-04-01'
and
[BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] is null
then
_days_between (Cast((?Fiscal_Year?+1)+'-03-31' AS DATE), Cast([BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] AS DATE))

#/*5 - Not active at fiscal start – future end date*/#
When
[BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] > (?Fiscal_Year?)+'-04-01'
and
[BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] > (?Fiscal_Year?+1)+'-03-31'
then
_days_between (Cast((?Fiscal_Year?+1)+'-03-31' AS DATE), Cast([BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] AS DATE))

#/*6 - Not active at fiscal start – ended on or before fiscal end*/#
when
[BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] > (?Fiscal_Year?)+'-04-01'
and
[BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] <=(?Fiscal_Year?+1)+'-03-31'
then
_days_between (Cast([BUSINESS LAYER].[CPOC Annual Summary Report].[END_DATE] AS DATE), Cast([BUSINESS LAYER].[CPOC Annual Summary Report].[EFFECTIVE_DATE] AS DATE))

end

[bus_pass_man]

In general, a fairly useful method for troubleshooting expressions would be breaking the expression into its component parts to isolate where the problem might originate.

Some times the error message might be useful too.

It's complaining about the expression trying to add an integer and a string. So look in your expression for the addition operator.  This is where the problem might be.

The addition operator in addition (sorry) to its primary function also operates as a concatenation operator.

You have several instances of the addition operator in your expression.  It looks like you are actually trying to concatenate stuff -- the results of the prompt and a string.  In most, but not all, cases you are trying to cast the prompt.  The method of your casting varies.  In 7 or 8 instances you're not casting the prompt.

I'd guess that the data type of your prompt is some numeric. 

One thing to try would be to change your concatenation operator to the || operator.  Since it has only one function -- concatenation-- it does not have the possibility of the query planner thinking that it needs to try to add the two operands but to concatenate them. 

Just changing the operators might be enough to fix the problem.  It might still complain about trying to concatenate the integer and the string.  If so, you might want to try to edit the prompts to explicitly make the value of the prompt to be a string.

A third thing to try would be to cast all instances of your prompt to string.  As I mentioned earlier, if you break the expression into its component parts it would help identify where the error is coming from.  I'm guessing that one or all of the non-cast prompts are triggering the error. 

Another thing to consider using a different the prompt type.

Do you get different results for each of your different casting expressions?  You might want to revisit them and see if there could be better approaches.

These approaches probably should be tried in isolation.

[fishercat]

Thank you so much.  Your answer gives me some new insight.  I will make the changes and test them in isolation and see where I land.  :)

[BigChris]

Whilst in no way adding to the answer, can Just say that it's a joy to see such a complete question and such a thorough answer? Puts me to shame with some of my one-liners

[MFGF]

...it's a joy to see such a complete question and such a thorough answer...

I agree completely!! It must have taken fishercat a while to formulate the initial post, but the effort was so worthwhile - it meant anyone reading the question knew exactly what issue to try to address. As a result, the very first response could be (and was) useful and comprehensive. As for the response by bus_pass_man, it was another example of an outstanding, thoughtful, helpful answer - pretty much like every other answer he posts up on Cognoise.

MF.

[Lynn]

Happy to give an applaud to both of them!

[bus_pass_man]

Just in time for year end reviews.  Mind if I quote you?

[Lynn]

Just in time for year end reviews.  Mind if I quote you?

Of course, kudos are well deserved and there will be a huge pay increase for you as a result  :D

[BigChris]

Provided we're on a percentage of course!

[MFGF]

Just in time for year end reviews.  Mind if I quote you?

Lol - you definitely should! If you want more, drop me a line - happy to give you a five-star reference! :)

MF.