SELECT DISTINCT issue

Danielle_Ridge

I was hoping someone might be able to explain an issue I am having with my SQL Livereport.

I have slightly modified the 'My Recently Viewed Documents' report and have been unable to get the query to return unique rows. I would it to return a list of the users most recent documents in Livereport format ( with links, functions menu), but when I change the SELECT statement to SELECT DISTINCT, I get this error

"Content Server Error: Error processing request.

[ORA-01791: not a SELECTed expression - SELECT DISTINCT..."

The report works once I change it back to SELECT, but the rows are not unique. Any suggestions? Here is the query:

SELECT DISTINCT %4 from DTree, DAuditNew where (DTree.DataID=DAuditNew.DataID) and (DAuditNew.PerformerID=%1) and (DAuditNew.AuditDate>%2) and ( (DAuditNew.AuditStr='Fetch') OR (DAuditNew.AuditStr='View') ) and %3 and not dtree.dataid='1206181' and %5 order by DAuditNew.AuditDate desc

 

Thanks for your help!

Nizar Ghazal

 

Quoted Danielle Ridge on 10/06/2017 10:24 AM:

 

I was hoping someone might be able to explain an issue I am having with my SQL Livereport.

I have slightly modified the 'My Recently Viewed Documents' report and have been unable to get the query to return unique rows. I would it to return a list of the users most recent documents in Livereport format ( with links, functions menu), but when I change the SELECT statement to SELECT DISTINCT, I get this error

"Content Server Error: Error processing request.

[ORA-01791: not a SELECTed expression - SELECT DISTINCT..."

The report works once I change it back to SELECT, but the rows are not unique. Any suggestions? Here is the query:

SELECT DISTINCT %4 from DTree, DAuditNew where (DTree.DataID=DAuditNew.DataID) and (DAuditNew.PerformerID=%1) and (DAuditNew.AuditDate>%2) and ( (DAuditNew.AuditStr='Fetch') OR (DAuditNew.AuditStr='View') ) and %3 and not dtree.dataid='1206181' and %5 order by DAuditNew.AuditDate desc

 

Thanks for your help!

 

 

Hi Danielle,

Looks like it's failing because of your ORDER BY clause. If you remove it, it should work, or you need to order by something that is selected by the distinct in your query.

Maybe ORDER BY %2?