Unable to use dynamic prompting in pass through SQL

[actcognosuser]

Hi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

[MFGF]

Hi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

Hi,

Have you tried using prompt macros in your SQL rather than Cognos parameters? For example, replacing ?Start_Date? with #prompt('Start_Date','Date')#

Is there a reason you are coding this as SQL rather than adding detail filters to each query that feeds into your Union object? If you must use SQL, why are you using pass-thru SQL rather than Cognos SQL (or Native SQL)?

Cheers!

MF.

[actcognosuser]

I have not tried using prompt macros. Let me try and provide feedback.

Irrespective of the SQL Syntax( Native or cognos or pass through the prompt doesn't work)

The reason for using the union all in the same query is .

Example - query 1 or first part select employees with designation Manager for a given date range .
query 2 select a record for those employees where the salary was last changed.
So in the second query there's no date or designation filter.

Seqno    Date           EmployeeID   Designation    Salary
1       11/20/2024         RON.B       Manager       $140,000
2       09/20/2023         RON.B       Manager       $140,000
3       08/15/2022         RON.B       Analyst       $120,000
4       09/12/2021         RON.B       Jr Analyst    $95,000

In this scenario the output shoud display row 1 and row 3.

If I separate the queries out and add detail filter . How to add query 1 as the source for 2 without union all in 1 query.

So current query is pulling employees that are managers and then maximum of the date for the changed salary record.

[actcognosuser]

Update:

Encountered the following error while using prompt macro.
RSV-SRV-0001
 
      The request is missing the objectPath element.
 
 

But theres no error validating the SQL.

[dougp]

You appear to be confused about the difference between a union, a join, and a filter.

Can you post a minimal example of what you are trying to do as SQL code?