According to documentation I read, the Right Function will work correctly even on a numeric value.
We have tried ...
1 - Right(extract(year,?p_EndDate?),2)
and
2 - Right(Cast(extract(year,?p_EndDate?),varchar(50)),2)
Both attempts result in an error ...
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0219 The function "right" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
DB is SQL Server.
Ideas? TIA, Bob
Hi,
Try to cast the param as date and then extract, if you haven't tried it yet. And change the query processing to limited local if you still face
Good luck
New guy
Thank you New guy.
I changed to Limited Local processing, and still got ...
The function "right" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
Not sure what you mean by ...
QuoteTry to cast the param as date and then extract, if you haven't tried it yet.
The parameter ?p_EndDate? is a Date Prompt. When I extract for the year only, for 30 June 2017, it does
report 2017. Was hoping a Right,2 of 2017 would result in 17, but something amiss.
Ideas? TIA, Bob
Hi,
Below is the sample I tried with and works
Date column year extract exp output
Jan 4, 2017 12:00:00 AM cast(extract(year,?Parameter1?),varchar(4)) 2017
Good luck
New guy
Thank you.
I agree that works returning 2017.
I tried (my similar code) ...
Right(Cast(extract(year,?p_EndDate?),varchar(4)),2)+' Hours'
.. which gets the same error.
UDA-SQL-0219 The function "right" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
Thank you for your patience, Bob
Quote from: Cognos_Jan2017 on 21 Jun 2017 03:56:29 PM
UDA-SQL-0219 The function "right" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
Hi,
There is no right() function in the list of inbuilt functions in Cognos. You can see this in the expression editor if you click on the Functions tab and expand the Common Functions section. You're trying to use a local function that doesn't exist - exactly as the error message indicates.
Your database may or may not support the right() function, but it looks like your report is forcing local processing based on other things you are doing, so the function call is not being passed to the database.
Cheers!
MF.
QuoteThere is no right() function in the list of inbuilt functions in Cognos. You can see this in the expression editor if you click on the Functions tab and expand the Common Functions section. You're trying to use a local function that doesn't exist - exactly as the error message indicates.
Your database may or may not support the right() function, but it looks like your report is forcing local processing based on other things you are doing, so the function call is not being passed to the database.
Thank you MFGF.
I agree. Under Vendor Specific Functions, we have SQL Server which includes Right and Left functions, among others. I noted there is no Mid Function (apparently) like I have used in Microsoft Access and Excel.
About a month ago, I was able to utilize Right in a Cognos Report. Apparently that Report has NO local processing is why it worked.
2 thoughts ...
1 - Is there a way, within a Report, to see where local processing is occurring? Then turning that off, and hoping it doesn't cause a problem. That way, we can learn how we can utilize Vendor Specific SQL Server functions.
2 - If the above doesn't work ... with no Mid and Right functions available, is there a way to "extract" 17 from 2017 (or similar years) from a Date Prompt? I could use a Value Prompt to 'Use' ...
15, 16,17,18 ... , and display 2015, 2016, 2017, 2018 ... That SHOULD work.
Your thoughts? TIA, Bob
Hi,
Is extract 'year' working for you? if so cast that as varchar and use a substring to get what you need from it.
New guy
Thank you New guy.
I just posted a reply, w/ thoughts, to MFGF.
Yes, I can successfully extract year from the Date Prompt.
As I am still a novice, I think casting the year as a varchar(50) should definitely
qualify that as a string ... so substring should work.
Wondering if placing the years in a Value Prompt as ...
Use 15, 16, 17, 18, ... and displaying 2015, 2016, 2017, 2018, ...
is a better approach?
Will examine both.
New guy and MFGF ...
Trying to use Right(2017,2) does WORK if make a Data Item 'FY-lbl' as
Right([Fiscal Year],2).
I then place that 'FY_lbl' in the Data Item Label for a Column Header
for 'Fiscal Year' nested in a Crosstab.
This is part of the current Topic Pivot List to Crosstab where tjohnson3050
is helping me. Now if I can get the 2 Data Item calculations placed next to
the 4 Measures in the Crosstab.
Always good to discuss different ways to reach a destination ... and pursuing
that objective.
What I usually do is to just make Cognos use the database.
Data Item 1: right(cast(year(?p_EndDate?),varchar(4)),2)
Data Item 2: [Namespace].[SmallQuerySubject].[SmallQueryItem]
Filter: [Namespace].[SmallQuerySubject].[SmallQueryItem] = 'OneValueFromIndexedColumn'
QuoteWhat I usually do is to just make Cognos use the database.
Data Item 1: right(cast(year(?p_EndDate?),varchar(4)),2)
Data Item 2: [Namespace].[SmallQuerySubject].[SmallQueryItem]
Filter: [Namespace].[SmallQuerySubject].[SmallQueryItem] = 'OneValueFromIndexedColumn'
So 'Data Item 1' won't error?
Thank you, Bob
Did you try?
It shouldn't error because now the query is not using only local processing.
QuoteWhat I usually do is to just make Cognos use the database.
Data Item 1: right(cast(year(?p_EndDate?),varchar(4)),2)
Data Item 2: [Namespace].[SmallQuerySubject].[SmallQueryItem]
Filter: [Namespace].[SmallQuerySubject].[SmallQueryItem] = 'OneValueFromIndexedColumn'
Just tried. Don't know how to set the Filter for ...
[Namespace].[SmallQuerySubject].[SmallQueryItem] = 'OneValueFromIndexedColumn'[/quote]
Got error ...
QE-DEF-0260 Parsing error before or near position: 64 of: "[Data Item2]=[Namespace].[SmallQuerySubject].[SmallQueryItem] ="
QE-DEF-0261 QFWP - Parsing text: [Data Item2]=[Namespace].[SmallQuerySubject].[SmallQueryItem] = 'OneValueFromIndexedColumn'
QE-DEF-0260 Parsing error before or near position: 64 of: "[Data Item2]=[Namespace].[SmallQuerySubject].[SmallQueryItem] ="
QE-DEF-0261 QFWP - Parsing text: [Data Item2]=[Namespace].[SmallQuerySubject].[SmallQueryItem] = 'OneValueFromIndexedColumn'
RSV-SRV-0042 Trace back:
:P
Agree ... we aren't Happy Campers at the moment ... but we are both trying.
Thank you.
How do I Filter on your recommendation?
You're kidding right? Did you really copy and paste pseudocode into a filter expression?
Not kidding. When trying to learn, trial-and-error is sometimes necessary