1 Reply Latest reply: Jul 2, 2013 5:35 AM by Archana.D RSS

    If duplicating value exist, do something to the field.

    1011308

      Ladies and gentlemen, lets assume my report will display data from the following database table

       

      Column_1     Column_2     Column_3

                   1     data1           ABC

                   2     data2           DEF

                   3     data3           GHI

                   4     data4           DEF

       

      In my report, for the field that display the value of Column_3, I would like to make it either being bold or append the "*" symbol to the value, when there is any duplicating or same value in the same column, but remain normal for other values. For example, as you can see from the above table, for 2nd and 4th rows, Column_3 has the same value "DEF". For the column that display the value of Column_3, make it bold or append the "*" symbol when displaying "DEF", while remain normal when displaying other rows.

       

      The following are the examples of wanted output.

       

      Either make it bold:

       

      Column_1     Column_2     Column_3

                   1     data1           ABC

                   2     data2           DEF

                   3     data3           GHI

                   4     data4           DEF

       

      Or, append a "*" symbol:

       

      Column_1     Column_2     Column_3

                   1     data1           ABC

                   2     data2           *DEF

                   3     data3           GHI

                   4     data4           *DEF

       

      Is this possible? Can you suggest me any way to do it?

       

      If you do not understand my question, please say so. I don't mind to make further explanation.

        • 1. Re: If duplicating value exist, do something to the field.
          Archana.D

          Hi user,

           

          Hope you are expecting the following query..

          SQL Statement which produced this data:
          
            WITH data AS (SELECT 1 Column_1, 'data1' Column_2, 'ABC' Column_3 FROM DUAL
                          UNION ALL
                          SELECT 2 Column_1, 'data2' Column_2, 'DEF' Column_3 FROM DUAL
                          UNION ALL
                          SELECT 3 Column_1, 'data3' Column_2, 'GHI' Column_3 FROM DUAL
                          UNION ALL
                          SELECT 4 Column_1, 'data4' Column_2, 'DEF' Column_3 FROM DUAL)
            SELECT column_1,
                   column_2,
                   (  SELECT CASE
                                WHEN COUNT (column_3) > 1 THEN ('*' || Column_3)
                                ELSE column_3
                             END
                        FROM data
                       WHERE column_3 = b.column_3
                    GROUP BY column_3)
                      column_3
              FROM data b
          
          Output:
          
          COLUMN_1,COLUMN_2,COLUMN_3
          1,'data1','ABC'
          2,'data2','*DEF'
          3,'data3','GHI'
          4,'data4','*DEF'
          
          

           

           

          Regards,

          Archana