'OTBI analytic to function value is not null in any one of the columns for a particular employee

My OTBI(Oracle Transactional Bi report) report have the following columns -

   Person number  Date_to          Person Name      comp              Specialization    CERT           
          10          10-jan-2021      XYZ               ABC                                                          
          10          01-JAN-2022      XyZ                                                      
          10          01-jun-2022      Xyz                                                                             
          
          11         01-jan-2022       Smith                                    Yes                                      
          11         01-jan-2022       Smith                                                
    
          12         01-jan-2022        Jen                                                                                    
          12         02-jan-2022        Jen

I want the profile_updated column to be updated as Y if any of the values for the employee - comp, specialization, cert is not null. i.e. Person number 10 has value in comp and hence in all the other three rows it should have value Y.

For 11, it will be Y. For 12, since comp, specialization or CERT does not have any value in all two rows it will be N

 Person number  Date_to             comp              Specialization    CERT           profile_updated
  10          10-jan-2021        ABC                                                   Y         
  10          01-JAN-2022                                                              Y
  10          01-jun-2022                                                              Y                           
  
  11         01-jan-2022                            Yes                                Y             
  11         01-jan-2022                                                               Y

  12         01-jan-2022                                                               N                                         
  12         02-jan-2022                                                               N
sqloracle-sqldeveloperoracle-bi


Solution 1:[1]

You can handle it in database easily and add a column to target table then use this column in OBI to present. you should use partitioning in your query. for instance:

select t.*,
   case sum(case
          when t.comp || t.Specialization || t.CERT is null then
           0
          else
           1
        end) over(partition by t.Person_number)
     when 1 then
      'Y'
     else
      'N'
   end profile_updated from TABLE_NAME t

also you can use this method by EVALUATE function in OBIEE directly.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Saeed J

Related Questions