Cognos 8.4.1 - ORA-00904: "DATEADD": invalid

[lecarlos]

Hello,

I need some help with converting this SQL expression into Oracle.  Tthe error message in this expression is:ORA-00904: "DATEADD": invalid identifier and I don't understand what that means.  This is a filter in report. I don't know what the Oracle function is for DATEADD. Can someone please help me to convert this into the Oracle function.

DATEADD({day}, 0, DATEDIFF({day}, 0, [Acceptance Date])) >= DATEADD({day}, 0, DATEDIFF({day}, 0, [FHASLEXT].[As Of Date]))

I appreciate your support and guidance. 

Thanks, lecarlos

[CognosPaul]

The DATEADD function is SQL Server specific. It adds n days to a specified date, in this case a numeric value returned by the DATEDIFF function. The date diff function is calculating the number of days between 0 and [Acceptance Date]. This is, effectively, truncating [Acceptance Date] to a date. The same procedure is being done with the [FHASLEXT].[As Of Date]. Are these fields datetime or timestamp? Did you replace other parts of the filter fo rthe purpose of posting here? There are more efficient ways of converting a datetime to a date.

Unless you've modified your filter for whatever reason, you could get the same result using:

trunc([Acceptance Date],'DD') >= trunc([FHASLEXT].[As Of Date],'DD')

[lecarlos]

CognosPaul,
This information I have supplied was directly from the filter in the report.  I have tried the suggested expression that you provided for me and I do thank you for your help. Here is the error message that I'm getting.

UDA-SQL-0115 Inappropriate SQL request.
UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.
UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)
UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'Tabular_Model1'. (SQLSTATE=42000, SQLERRORCODE=102)
UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]'trunc' is not a recognized built-in function name. (SQLSTATE=42000, SQLERRORCODE=195)

Can you help me to correct this error?

Also, I really appreciate you taking the time to explain this expression to me.

Please let me know if there is anything else you need to help me with this issue.
Thanks in advance for your support.
Thanks, lecarlos

[CognosPaul]

Are you doing this over Oracle or SQL Server? The error you posted just now indicates an SQL Server database, while your first post had an Oracle.

How about this, use Cognos functions. Cognos should automatically translate them to the correct flavor for each database.

cast(Acceptance Date],date) >= cast([FHASLEXT].[As Of Date],date)

[lecarlos]

CognosPaul,

I have attached the XML for this report and I have made the original filter required again so that you can see what I'm talking about.  You are correct that these two queries are pointing to the SQL database. Query 1 and Tabular Model1 are both pointing to SQL database.  The query named FHASLEXT is pointing to Oracle and I not sure why its referring to the query 1 filter that is giving me the error message when I validate it.  I hope this isnt too confusing.

I also tried the last code that you send to me and I recieved this message:
QE-DEF-0260 Parsing error before or near position: 21 of: "cast(Acceptance Date"
QE-DEF-0261 QFWP - Parsing text: cast(Acceptance Date],date) >= cast([FHASLEXT].[As Of Date],date)

-<queryProblems> <message code="901"location="./queries/query[1]/detailFilters/detailFilter[3]/filterExpression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0260 Parsing error before or near position: 21 of: "cast(Acceptance Date"
QE-DEF-0261 QFWP - Parsing text: cast(Acceptance Date],date) >= cast([FHASLEXT].[As Of Date],date)

Now I'm going to disable this filter and apply the first one:
-<queryProblems>
-<messageFolder componentID="RQP"maxSeverity="error">
<message severity="warning"title="QE-DEF-0469 QE Message"type="governor">RQP-DEF-0104 Warning: There is a cross join for the user who has the identity '{020-User Profile, 030-User Profile, 000-FDM Team, 200-Security Management, All Authenticated Users, Everyone, 030-Professional Authors, 999-All Licensed Users, 060-Report Recipients, Authors, Query Users, Consumers, Metrics Authors, Metrics Users, Planning Contributor Users, Controller Users, Analysis Users, Data Manager Authors, Adaptive Analytics Users, 020-System Administrators, MDR_USR, Password Manager Group, U-Remove Restrictive Policies, Financial Data Mart - OCFO Users, MSTR UAT, Domain Users, OCIO Share Point, hhq_g1, CSG01, MSTR DEV, HHQ Developers, CSG-AMISA, MCSG01, HQBldgApps}'.

</message>
<message severity="warning"title="QE-DEF-0469 QE Message"type="governor">RQP-DEF-0104 Warning: There is a cross join for the user who has the identity '{020-User Profile, 030-User Profile, 000-FDM Team, 200-Security Management, All Authenticated Users, Everyone, 030-Professional Authors, 999-All Licensed Users, 060-Report Recipients, Authors, Query Users, Consumers, Metrics Authors, Metrics Users, Planning Contributor Users, Controller Users, Analysis Users, Data Manager Authors, Adaptive Analytics Users, 020-System Administrators, MDR_USR, Password Manager Group, U-Remove Restrictive Policies, Financial Data Mart - OCFO Users, MSTR UAT, Domain Users, OCIO Share Point, hhq_g1, CSG01, MSTR DEV, HHQ Developers, CSG-AMISA, MCSG01, HQBldgApps}'.

</message>
-<messageFolder maxSeverity="error">
<message severity="error"title="UDA-QOS-0006 Error"type="nativeSQL">UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00904: "DATEADD": invalid identifier


I really hope that your able to see my issue now. Looking forward to see what you come up with...

Thanks, lecarlos

[lecarlos]

CognosPaul,

I think that I have a better understanding now. The issue is that part of this expression is has a dataitem that is SQL and the other part of this expresion has Oracle and the dataitem for Oracle is (FHASLEXT).  Now that I know this how do I make the change.?

DATEADD({day}, 0, DATEDIFF({day}, 0, [Acceptance Date])) >= DATEADD({day}, 0, DATEDIFF({day}, 0, [FHASLEXT].[As Of Date]))

I'm sorry for the confusion but I think that I'm sarting to get some of this stuff.

Have a great day and I look forward to hearing from you.

Regards, lecarlos

[CognosPaul]

I see the issue. You have three queries in your report, at least two of them from separate databases, and you're trying to include the results of all in a single list.

Let's focus on Query1 and FHASLEXT. Remove the tabular model query for the time being. Once the first part works, then you can add it back in.

Because the two queries are from separate databases, Cognos can only join the two queries on the Cognos server. Since you're using the Oracle query as a filter, it needs to bring in all the data before it can filter it. The work-around is to use a master detail relationship to insert the result from the Oracle query as a parameter.

I don't have access to your system so I'm making several assumptions. First, I'm assuming that the query only returns one row, second I'm assuming that the As Of Date data item is a timestamp, so let's convert that to something that SQL Server would use with: to_char([FDM Production].[Data Load Dates].[As Of Date],'yyyy-mm-dd')

Now, create a singleton with FHASLEXT as the source, and put As Of Date in the properties.

Next, remove the dateadd filter. Instead use: [Acceptance Date] >= #prompt('As of date','date')#
You also have a As of Date data item in the query. Replace the expression with the prompt as well. Again, remove Year and Close Month to keep the query simple for now.

With all references to FHASLEXT from Query1, drag the list into the singleton. Set the master detail on the list to connect As of Date to the As of Date parameter. This way the singleton runs first, and the SQL For the list is changed accordingly.

If this works, you can use a similar method to pass the data from the tabular query down.