Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Difference between dates
Hi.
I need to calculate difference between colum BIRTH_DATE (type is datetime) and parameter @{p_date_till}.
This is what I've tried:
1. TIMESTAMPDIFF(SQL_TSI_YEAR , BIRTH_DATE, Cast(Cast(@{p_data_iki} as Char) as Timestamp))
--It gives correct formula, but in results tab gives error:
ORA-01847: day of month must be between 1 and last day of month at OCI call OCIStmtFetch.
2. TIMESTAMPDIFF(SQL_TSI_YEAR , BIRTH_DATE, @{p_data_iki} )
--It gives incorrent formula:
Function TimestampDiff is called with an incompatible type.
--Also when I ignore formula error and press results tab I get the same error.
--But when I open analysis in view mode (open from catalog) and type value 2016-01-01 in parameter @{p_data_iki} then I get results which I needed.
The problem is that i can get results, but I cannot edit table, because of error in results tab.
--obiee 12c
Answers
-
Simple. You didnt supply a default value for your presentation variable: @{pdataiki}{01-01-2016}
0 -
+1 Christian
But you can still edit the table opening it from the bottom-left part of your result tab screen where you have the list of all the views included in your analysis.
0 -
TIMESTAMPDIFF(SQL_TSI_YEAR , BIRTH_DATE, @{p_date_till}{01-01-2016})
--On results tab:
State: HY000. Code: 22025. [nQSError: 22025] Function TimestampDiff is called with an incompatible type. (HY000
SQL Issued: {call NQSGetQueryColumnInfo('SELECT TIMESTAMPDIFF(SQL_TSI_YEAR , "BIRTH_DATE", 01-01-2016) FROM "Table"')SQL Issued: SELECT TIMESTAMPDIFF(SQL_TSI_YEAR , "BIRTH_DATE", 01-01-2016) FROM "Table"0 -
Why do you not cast it as a timestamp anymore? Come on you almost had this.
0 -
When I want to add a pivot table view I could not do this because of error
0 -
TIMESTAMPDIFF(SQL_TSI_YEAR , "Apdraustieji"."Gimimo data", Cast(Cast(@{p_data_iki}{01-01-2016} as Char) as Timestamp))
ORA-01847: day of month must be between 1 and last day of month at OCI call OCIStmtFetch. (HY000)
0 -
I think that error is given because of wrong date format of default parameter value {01-01-2016}. Because when I try formula with default parameter value {2016-01-01} (which should be default in my Locale) I get different error:
ORA-01843: not a valid month at OCI call OCIStmtFetch.
0 -
-
have you tried TIMESTAMPDIFF(SQL_TSI_YEAR , BIRTH_DATE, DATE '@{p_data_iki}' )?
0
