SQL query automatically removes duplicates (unwanted)

[bshim]

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:

Code Select Expand

SELECT
PROJ_ID,
FROM RPT_REVENUE_WKS

If I create a unique identifier, I can see the duplicate PROJ_ID values:

Code Select Expand

SELECT
PROJ_ID,
PROJ_ID + ACCT_ID + cast(POOL_NO as varchar(255)) as uniqueid
FROM RPT_REVENUE_WKS

[MFGF]

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:

Code Select Expand

SELECT
PROJ_ID,
FROM RPT_REVENUE_WKS

If I create a unique identifier, I can see the duplicate PROJ_ID values:

Code Select Expand

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.