Concatenate Campaign and CampaignNo and prompt for it in ReportNet?

[dudden]

Hi everyone,
I am having trouble with concatenating Campaign and CampaignNo into 1 and then make a prompt where the users can select wich Campaign they want to look at and in the prompt be able to see both the Campaign name and the CampaignNo.
I created a databaseobject in my query like this:

.[Campaign] + ' - '+

.[CampaignNo] and when I try to "View tabular data" then I get a message telling me I can´t do this request. And then offcourse I am not able to make the prompt either but how should I deal with this?

Hope for all of you out there for some expertise.

BR
Dudden

 

[Draoued]

Maybe the data types are not the same??

If your campaign is a string field and CampaignNo is a number field then you can not combined them.

Try something like :
[Campaign] + '-' + to_char([CampaignNo])

 

[dudden]

Hi there,
I tried to do this but then I get the error message that I can´t do this request and the information about the problem is pretty long, but here it is:

QE-DEF-0177 An error occurred when the action 'sqlPrepareWithOptions' status='-9' was done.

UDA-SQL-0107 A custom exception occurred at the operation "prepare".

[IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

Trace backwards:

WPBIBusMethod.cpp(190): WPDataRetrievalException: CCL_CAUGHT: WPBIBusMethod::run

WPReportExecutionMethod.cpp(174): WPDataRetrievalException: CCL_RETHROW: WPReportExecutionMethod::checkRequestForExceptions

WPExecuteRequestThread.cpp(169): WPDataRetrievalException: WPExecuteRequestThread::checkException

WPEngine.cpp(946): WPDataRetrievalException:

WPController.cpp(263): WPDataRetrievalException: CCL_RETHROW: WPController::executeRendering()

LWDataRetrievalEngine.cpp(305): WPDataRetrievalException: CCL_RETHROW: LWDataRetrievalEngine:repare

LWDataRetrievalEngine.cpp(1838): WPDataRetrievalException: CCL_THROW: LWDataRetrievalEngine::runQSQuery

Source/QE_QsApi.cpp(3337): QSException: CCL_THROW: QE

I have also tried to only make a new databaseobject like this: to_char([CampaignNo]) and put this out on a report but then I get the same error. Do you guys have any idea how and why? Does not the to_char function works or...? I also tried with to_string but the same...

Glad for any help in this topic, thanks in advance!

BR Dudden

 

[mfgf]

Hmmm - looks to me like you are reporting off a DB2 database, for which to_char is not a recognized function. In that case, use either the char function or the cast_char function instead (both of which DB2 supports), or alternatively to make it completely independent of your database, use ReportNet's cast function to cast your Campaing Number to a char datatype.

Regards,

MF.

 

[dudden]

Thanks for the advice and your really quick answer!
I tried cast_char([CampaignNo]) and putted this into a report and it works fine but I only get the first number in CampaignNo for example 3456 = 3, 4567=4... how to fix this?

BR Dudden

 

[mfgf]

Try cast_char([CampaignNo],10) to get a 10-character return. Or just try char([CampaignNo]) instead?

Regards,

MF.

 

[dudden]

Thanks for your time and knowledge. When just using char it works just fine!

Thanks again!
BR Dudden