Discussion Locked

How to write DISTINCT in sql's CASE ?,For that below query i am trying

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

SELECT DISTINCT
TO_CHAR(creation_dt,'MM/DD/YYYY') "Date of Creation",
first_names "First Name", surname "Last Name",
Section_Name "SSO ID",
(CASE WHEN Authority_id = 'LEAD1' THEN 'Reconciler'
WHEN Authority_id = 'LEADREV' THEN 'Reviewer'
WHEN Authority_id = 'LEADRR' THEN 'Reconciler/Reviewer'
WHEN Authority_id = 'LEADADMN' THEN 'Administrator'
WHEN Authority_id = 'LEAD1' THEN 'Reconciler'
WHEN Authority_id = 'LEADVIEW' THEN 'Browser'
WHEN Authority_id = 'LEADCRE' THEN 'CREAdmin'
WHEN Authority_id = 'LEADCS' THEN 'CSAdmin' END) "User Role",
(CASE WHEN user_status = 1 THEN 'ACTIVE'
WHEN user_status = 2 THEN 'INACTIVE'END) "Status", user_id "Hex Id"
FROM usrr
WHERE user_id!='SETUP' AND user_id NOT LIKE 'LEAD%'

1 reply

by tony hopkinson

No way I can tell from simply this query

May not solve your problem and the syntax is DBMS dependant sometimes but you can do this

Select Distinct "Date Of Creation","First Name", "Last Name","SSO ID","User Role","Status","Hex id"
From(
SELECT
TO_CHAR(creation_dt,'MM/DD/YYYY') "Date of Creation",
first_names "First Name", surname "Last Name",
Section_Name "SSO ID",
(CASE WHEN Authority_id = 'LEAD1' THEN 'Reconciler'
WHEN Authority_id = 'LEADREV' THEN 'Reviewer'
WHEN Authority_id = 'LEADRR' THEN 'Reconciler/Reviewer'
WHEN Authority_id = 'LEADADMN' THEN 'Administrator'
WHEN Authority_id = 'LEAD1' THEN 'Reconciler'
WHEN Authority_id = 'LEADVIEW' THEN 'Browser'
WHEN Authority_id = 'LEADCRE' THEN 'CREAdmin'
WHEN Authority_id = 'LEADCS' THEN 'CSAdmin' END) "User Role",
(CASE WHEN user_status = 1 THEN 'ACTIVE'
WHEN user_status = 2 THEN 'INACTIVE'END) "Status", user_id "Hex Id"
FROM usrr
WHERE user_id!='SETUP' AND user_id NOT LIKE 'LEAD%') dummytable

Note dummytable is an alias for the 'table' returned by the inner query.

PS you've got Lead1 to reconciler in twice.

Personally I'd consider a little look up table for that lot as well, it would be way more efficient.

This discussion is closed to new replies.