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?

[actcognosuser]

WITH Current_Manager AS (
    -- Get the current salary of employees who are managers within the specified date range
    SELECT
        Seqno,
        Date,
        EmployeeID,
        Designation,
        Salary
    FROM
        REGEMP
    WHERE
        Designation = 'Manager'
        AND Date BETWEEN ?Start_Date? AND ?End_Date? -- Prompt macros for date range
),
Last_Salary_Change AS (
    -- Identify the most recent salary change for each employee
    SELECT
        EmployeeID,
        MAX(Date) AS Last_Change_Date
    FROM
        REGEMP
    WHERE
        Salary != LAG(Salary) OVER (PARTITION BY EmployeeID ORDER BY Date) -- Only include rows where the salary changed
    GROUP BY
        EmployeeID
),
Last_Changed_Salary AS (
    -- Get the details of the last salary change
    SELECT
        R.Seqno,
        R.Date,
        R.EmployeeID,
        R.Designation,
        R.Salary
    FROM
        REGEMP R
    JOIN
        Last_Salary_Change LSC
    ON
        R.EmployeeID = LSC.EmployeeID
        AND R.Date = LSC.Last_Change_Date
)
-- Combine the current salary and last changed salary
SELECT
    Seqno,
    Date,
    EmployeeID,
    Designation,
    Salary,
    'Current Manager Salary' AS Row_Type
FROM
    Current_Manager
UNION ALL
SELECT
    Seqno,
    Date,
    EmployeeID,
    Designation,
    Salary,
    'Last Changed Salary' AS Row_Type
FROM
    Last_Changed_Salary
ORDER BY
    EmployeeID, Row_Type DESC;

[dougp]

It looks like LAG() is the reason to write custom SQL.  But that's only for the Last_Salary_Change query.  The other three queries can all be built in the usual way and can be JOINed or UNIONed normally in Cognos. 

Date BETWEEN ?Start_Date? AND ?End_Date?

will work as a filter expression in Current_Manager, all built in the GUI.