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.
Quote from: actcognosuser on 20 Nov 2024 11:50:49 AMHi 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.
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.
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.
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?
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;
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.