One of my SQL queries is removing duplicate rows automatically. How can I stop this from happening? I've not experience this in any other type of SQL.
I've tried each of the 3 SQL syntaxes (Native, Pass-Through, IBM Cognos) and have toggled the detail/summary aggregations for the data item.
Removes duplicates:
SELECT
PROJ_ID,
FROM RPT_REVENUE_WKS
If I create a unique identifier, I can see the duplicate PROJ_ID values:
SELECT
PROJ_ID,
PROJ_ID + ACCT_ID + cast(POOL_NO as varchar(255)) as uniqueid
FROM RPT_REVENUE_WKS
Quote from: bshim on 16 Feb 2021 07:43:56 PM
One of my SQL queries is removing duplicate rows automatically. How can I stop this from happening? I've not experience this in any other type of SQL.
I've tried each of the 3 SQL syntaxes (Native, Pass-Through, IBM Cognos) and have toggled the detail/summary aggregations for the data item.
Removes duplicates:
SELECT
PROJ_ID,
FROM RPT_REVENUE_WKS
If I create a unique identifier, I can see the duplicate PROJ_ID values:
SELECT
PROJ_ID,
PROJ_ID + ACCT_ID + cast(POOL_NO as varchar(255)) as uniqueid
FROM RPT_REVENUE_WKS
Hi,
Within your report, navigate to the query the SQL feeds into. Open the properties pane and you will see an Auto Group & Summarize property for the query. Set this to "No" and see if it now behaves as you would like.
On a related note, is there a reason you're adding manually-coded SQL to your report? Generally this would be a last resort when all else fails?
Cheers!
MF.