CODEWITHSUNDEEP
sqloracle-databasebi-publisher
Hailey
Hailey

Reputation: 69

Suppress Repeating values in Oracle sql

I'm using Oracle BIPublisher to build a sql query. Right now my query output is:

ControlID  Role
1          ABC
1          SJD
1          DKF
2          LLL
2          IJK

How do I write the sql so that the output comes out like this?

ControlID  Role   
1          ABC
           SJD
           DKF
2          LLL
           IJK

*** Edit: I was able to suppress using Tim's solution:

SELECT DISTINCT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY GRCC_CONTROL_ID  ORDER BY erp_user_name) = 1
         THEN GRCC_CONTROL_ID  ELSE NULL END AS GRCC_CONTROL_ID ,
    ,role
from GRC_CTRL_AAC_INCIDENTS 
ORDER BY GRCC_CONTROL_ID, role

I want to replace the control_id with the actual control name but that is in a separate table. When I replace c.name in the above query, I get an error that says

'FROM' keyword not found where expected

:

SELECT DISTINCT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY c.name  ORDER BY role) = 1
         THEN c.name  ELSE NULL END AS c.name ,
    role
from GRC_CTRL_AAC_INCIDENTS 
,GRC_CTRL_CCM_CONTROL_TL c
where GRCC_CONTROL_ID = c.id
ORDER BY c.name, role

Upvotes: 0

Views: 2641

Answers (3)

Kurt
Kurt

Reputation: 1

this used to work

Break on GRCC_CONTROL_ID

Upvotes: 0

EdHayes3
EdHayes3

Reputation: 1953

I'm going to attempt to answer your end goal.

With the approach you are taking, it's going to be difficult to display the data correctly by not exposing the controlID in the XML. If the ControlID is not coming over in the XML, you wont be able to group the roles together with others in the same ControlID on the report. Role SJD has the same ControlID as LLL.

This is a good time to use the "regrouping" or "for-each-group" functionality in BI Publisher. 

<?for-each-group: BASE-GROUP;GROUPING-ELEMENT?>

Assuming these are two elements are in a ROW element:

<?for-each-group: ROW;ControlID?>
<?sort:ControlID?>
<?ControlID?>
<?for-each:current-group()?>
<?sort:Role?>
<?Role?>
<?end for-each?>
<?end for-each-group?>

You could easily add this to repeating table rows, and use a nested table for the inner for-each.

There's quite a bit of additional documentation and examples on the internet for BI Publisher regrouping functionality.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

This is really a presentation requirement, and so might be best handled in your presentation layer (e.g. something like PHP or Java). That being said, we could handle this using ROW_NUMBER:

SELECT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY ControlID ORDER BY Role) = 1
         THEN ControlID ELSE NULL END AS ControlID,
    t.Role
FROM yourTable t
ORDER BY
    t.ControlID,
    t.Role;

enter image description here

Demo

Edit:

You are using the old style pre ANSI-92 join syntax is your actual updated query attempt. Don't do that. Instead, use an explicit join, with aliases:

SELECT DISTINCT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY role) = 1
         THEN c.name ELSE NULL END AS name,  -- NOT c.name
    role
FROM GRC_CTRL_AAC_INCIDENTS t
INNER JOIN GRC_CTRL_CCM_CONTROL_TL c
    ON t.GRCC_CONTROL_ID = c.id
ORDER BY
    c.name,
    role;

Upvotes: 1

Related Questions