Categories
- All Categories
- 141 Oracle Analytics News
- 26 Oracle Analytics Videos
- 14.7K Oracle Analytics Forums
- 5.6K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 52 Oracle Analytics Trainings
- 11 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Concatenate Date values with Presentation Variable
Hi All,
I have created a dashboard prompt with 1 presentation variable date prompt(Start date) showing First day of quarter and few column prompts in a dashboard. My requirement is to show the date selected in the prompt concatenated with end day of quarter as 1 column in 'MM/DD/YYYY' format in the report. I tried to concatenate the values using the below syntax in column format but its giving me a syntax error:
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'}))||'-'||TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'}))))
I also tried using Evaluate function also but its giving an error with presentation variables.
Can anyone please help me how can I achieve this.
Note : I am using OBIEE 12.2.1.4.0 version.
Thanks in Advance for the help!!!!
Answers
-
You can only concatenate text fields. If CAST( XXXX TO DATE) doesn't do it, you can use EVALUATE with the TO_CHAR function to get your specific format.
0 -
Thank you for your response.
I have used Evaluate Function but its giving me the below error:
Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
Please have your System Administrator look at the log for more details on this error. (HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.
Please have your System Administrator look at the log for more details on this error. (HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed:
Please have your System Administrator look at the log for more details on this error. (HY000)
State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE.
Please have your System Administrator look at the log for more details on this error. (HY000)
I have used the below syntax for Evaluate function:
Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,CAST(TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'})) AS CHAR(10)),'MM/DD/YYYY')
Can you please help to resolve this issue.
Thanks in Advance for the help!!!!
0 -
You may have several issues here. One is that you are using both the CAST and TO_CHAR functions for the same purpose. Pick one. You also may not have access to the EVALUATE function, depending on your system, but there is no detail in those error messages, so I don't know about that.
I would start by creating a column that only contains your TIMESTAMPADD function to make sure that is working correctly. Then, your final column would look like one of the two versions below:
CAST(timestampfunction1 AS CHAR(10))||' - '||CAST(timestampfunction2 AS CHAR(10))
OR
EVALUATE('TO_CHAR(%1,%2)' as char,timestampfunction1,'MM/DD/YYYY')||' - '||EVALUATE('TO_CHAR(%1,%2)' as char,timestampfunction2,'MM/DD/YYYY')
0 -
Thanks for your reply. I have used both CAST and Evaluate functions as per your suggestion but faced the below issues:
With Evaluate function got the same error as yesterday:
Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'})) ,'MM/DD/YYYY')
State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE.
Please have your System Administrator look at the log for more details on this error. (HY000)
With CAST(timestampfunction1 AS CHAR(10)) I am getting format as 2016/01/01 instead of 1/1/2016.I have used the below syntax for CAST function:
cast(TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( (@{pv_start_date}{date '2016-01-01'})) * -(1) + 1, (@{pv_start_date}{date '2016-01-01'})) as char(10))
Can you please help to resolve this issue.
Thanks in Advance for the help!!!!
0