Problems with distinct 1

[dwhalen]

Hello,

I am trying to port my application to be able to use Oracle (currently I use postgresql) I am trying to create the below view, however I am having problems with the sql. I think it is the DISTINCT that is causing the problems. I want a distinct on two columns. How can I do this in oracle?

CREATE OR REPLACE VIEW
performance_view_distinct AS
SELECT DISTINCT ON (t.location_name,t.order_id) a.client_id AS client,
a.order_id AS order_id, t.task_id, SUBSTR(a.client_id, 1, 3) AS org, t
.location_name, r.rpd_short_name AS task, r.rpd_sort_group AS
workbasket,t.close_date, t.creation_date, t.assigned_id, t.close_id,a.ordertype AS ordertype,c.cli_chain_id AS chain_id
FROM report_decode r, action a, task t, client c
WHERE t.order_id = a.order_id AND t.order_id = c.order_id AND t.location_name = r.reference
ORDER BY t.location_name,t.order_id,t.task_id;


Thanks

 

[Turkbear]

The DISTINCT clause in Oracle affects ALL columns in the select statement.

You will need to reexamine the logic and rewrite the query
( perhaps pre-creating a task table view with the distinct clause and using it instead of the table itself)