Rajiv Choudhary Rajiv Choudhary - 1 year ago 44
SQL Question

oracle query return duplicate data

I have written a oracle query to fetch data and it's return duplicate data,So that i use distinct keyword.But I get an error:- ORA-01791: not a SELECTed expression

Oracle Query:-

Select Distinct pi.first_name || ' ' || pi.last_name initiator
, pi.user_id
, c.country_name
, e.eform_name
, w.date_stamp_utc
, decode (
waRating.attr_value, 4, 'Very Positive',
3, 'Positive',
2, 'Neutral',
1, 'Negative',
0, 'Very Negative', '???'
) rating
, waEnv.attr_value environment
, nvl(ua.agent_name, '???') browser
, nvl(ua.agent_version, '???') version
, waFeedback.attr_value negative_feedback

From request_workflow w
Inner Join request_workflow_attribute waRating
On waRating.request_workflow_id = w.row_id
And waRating.attr_name = 'UserRating'
Inner Join request_workflow_attribute waEnv
On waEnv.request_workflow_id = w.row_id
And waEnv.attr_name = 'Environment'
Inner Join request_workflow_attribute waBrowser
On waBrowser.request_workflow_id = w.row_id
And waBrowser.attr_name = 'UserAgent'
Left Join request_workflow_attribute waFeedback
On waFeedback.request_workflow_id = w.row_id
And waFeedback.attr_name = 'UserFeedback'
Inner Join person_info pi
On w.performer_id = pi.row_id
Inner Join request_common rc
On w.request_id = rc.row_id
Inner Join eform e
On rc.eform_id = e.row_id
Left Join user_agent ua
On waBrowser.attr_value = ua.user_agent_raw
Inner Join country c
On pi.country_code = c.country_code

Where w.date_stamp_utc
Between '1-dec-2014' And '31-dec-2015'
Order By waRating.attr_value desc, eform_name;

Please suggest how to make it work.

Answer Source

The error message is caused by the field in your order by clause. It is

order by waRating.attr_value desc, eform_name

In your select clause, you have this:

waEnv.attr_value environment

While the field name is the same, they are coming from different tables. However, that probably doesn't matter because you also used an alias in your select clause. Use the same one in your order by clause:

order by environment desc, eform_name