---------------------------------------------------
sqlplus d0read/reader@d0ofprd1

set pagesize 2000;
set linesize 256;
set heading on;
set echo on;
-----------------------------------------------------------------------------------
-- July 13, 2004
-- Look for multiple objects of the same name
-- with the same status but different versions

column NAME  format a25;
column stat_curr  format a10;
column stat_used  format a10;
column cvs_package format a22;
column version  format a10;

-- These first ones are the ones you really need to look at
-- (the complete list of objects with versions is in queries below this one):
-- these are the objects that have more than one of same name with stat_curr
-- of current,future or local
-- Level 2 !!!!!
SELECT COUNT(*),name,stat_curr FROM l2_objects
    group by name,stat_curr HAVING stat_curr<='local'  AND COUNT(*)>1;

  COUNT(*) NAME                      STAT_CURR
---------- ------------------------- ----------
         2 EM                        local
         2 EM                        current
         3 JET                       current
         2 MJT                       current
         2 MUON                      current
         2 INVMASS                   local

-- Level 3 !!!!!!!!!!
SELECT COUNT(*),name,stat_curr FROM l3_objects
    group by name,stat_curr HAVING stat_curr<='local'  AND COUNT(*)>1;

  COUNT(*) NAME                      STAT_CURR
---------- ------------------------- ----------
         2 L3FTau                    local
         2 L3Chi2Ele                 local
         3 L3FInvMass                local
         2 L3TIPTracker              local
         2 L3TIsolation              local
         2 L3TCalSeedUnp             local
         2 L3TMuoCalTrack            local

------------------------------------------------------------------
-- get the count of the number of used Objects with the same name and same
-- status and status<=local:
SELECT COUNT(*),NAME,stat_curr FROM l2_objects
    where stat_curr<='local' and stat_used!='unused'
    group by name,stat_curr;

  COUNT(*) NAME                      STAT_CURR
---------- ------------------------- ----------
         2 EM                        local
         2 EM                        current
         1 HT                        current
         1 ETA                       current
         3 JET                       current
         1 MET                       local
         1 MET                       current
         2 MJT                       current
         1 MUON                      future
         2 MUON                      current
         1 none                      local
         1 none                      current
         1 TRACK                     local
         1 TRACK                     future
         1 PHISEP                    current
         2 INVMASS                   local
         1 ETAPHISEP                 current
         1 TIMEDELAY                 current
         1 COMMISSION                current
         1 RANDOMPASS                current

SELECT COUNT(*),NAME,stat_curr FROM l3_objects
    where stat_curr<='local' and stat_used!='unused'
    group by name,stat_curr;

 COUNT(*) NAME                      STAT_CURR
---------- ------------------------- ----------
         1 none                      current
         1 L3FHt                     current
         1 L3FIP                     local
         1 L3FIP                     current
         1 L3FdR                     current
         1 L3FBID                    current
         1 L3FEle                    current
         1 L3FHta                    local
         1 L3FJet                    current
         1 L3FMEt                    local
         1 L3FMEt                    current
         1 L3FMHt                    current
         2 L3FTau                    local
         1 L3FTau                    current
         1 L3TCPS                    future
         1 L3TEle                    current
         1 L3TJet                    current
         1 L3FMuon                   current
         1 L3TMuon                   current
         1 L3FEJets                  local
         1 L3FJAcop                  current
         1 L3FTrack                  current
         1 L3TMcVtx                  local
         1 L3TPrVtx                  local
         1 L3TPrVtx                  future
         2 L3Chi2Ele                 local
         1 L3FPhysIP                 local
         1 L3TBTagIP                 current
         1 L3TCalMEt                 current
         1 L3TCalUnp                 current
         1 L3THTrack                 local
         3 L3FInvMass                local
         1 L3FInvMass                future
         1 L3ErrHandle               current
         1 L3FMuoLocal               future
         1 L3FPreScale               current
         1 L3TCFTTrack               local
         1 L3TCalCalib               local
         1 L3TMuoLocal               current
         1 L3TXYVertex               current
         1 SRDirective               local
         1 L3FCFTVertex              current
         1 L3TCFTUnpack              local
         1 L3TCFTUnpack              current
         1 L3TCFTVertex              current
         1 L3TCPSUnpack              local
         1 L3TCPSUnpack              future
         2 L3TIPTracker              local
         2 L3TIsolation              local
         1 L3TMuoUnpack              current
         1 L3TSmtUnpack              local
         1 L3TSmtUnpack              current
         1 L3TCalCluster             current
         1 L3TNullVertex             local
         1 L3TNullVertex             current
         1 L3TauHadronic             current
         1 L3TsmtCluster             local
         1 L3TsmtCluster             future
         1 L3FMarkAndPass            current
         1 L3RunConfigMgr            current
         1 L3TPhysTracker            current
         1 L3FPassFraction           current
         1 L3TGlobalTracker          current
         1 L3TPhysCFTVertex          current
         1 L3TMuoCentralMatch        current
         1 L3TSortSMTClusters        local
         1 L3FCorrPassFraction       local
         1 L3GeometryManagement      current

-- get a list of used Object names, version, ordered by status:
SELECT name,version,stat_curr,stat_used,cvs_package FROM l2_objects
    where stat_curr<='local' and stat_used!='unused'
    order by name,stat_curr;

NAME                      VERSION    STAT_CURR  STAT_USED  CVS_PACKAGE
------------------------- ---------- ---------- ---------- ----------------------
COMMISSION                1          current    used       l2gblgeneric
EM                        1          current    used       l2gblem
EM                        1          current    used       l2gblem
EM                        2          local      used       l2gblem
EM                        4          local      used       l2gblem
ETA                       1          current    used       l2gblgeneric
ETAPHISEP                 1          current    used       l2gblgeneric
HT                        1          current    used       l2gblgeneric
INVMASS                   1          local      used       l2gblgeneric
INVMASS                   1          local      used       l2gblgeneric
JET                       2          current    used       l2gbljet
JET                       1          current    used       l2gbljet
JET                       1          current    used       l2gbljet
MET                       v00-03-08  current    used       l2gbljet
MET                       v00-03-08  local      used       l2gbljet
MJT                       1          current    used       l2gbljet
MJT                       1          current    used       l2gbljet
MUON                      1          current    used       l2gblmuon
MUON                      1          current    used       l2gblmuon
MUON                      2          future     used       l2gblmuon
PHISEP                    1          current    used       l2gblgeneric
RANDOMPASS                1          current    used       l2gblgeneric
TIMEDELAY                 1          current    used       l2gblgeneric
TRACK                     1          future     used       l2gbltrack
TRACK                     4          local      used       l2gbltrack
none                      v00-00-00  current    used       saveme
none                      v00-00-00  local      used       dummy_package


SELECT name,version,stat_curr,stat_used,cvs_package FROM l3_objects
    where stat_curr<='local' and stat_used!='unused'
    order by name,stat_curr;

NAME                      VERSION    STAT_CURR  STAT_USED  CVS_PACKAGE
------------------------- ---------- ---------- ---------- ----------------------
L3Chi2Ele                 v00-00-00  local      used       dummy_package
L3Chi2Ele                 v00-00-00  local      used       saveme
L3ErrHandle               p09-05-00  current    used       l3exceptions
L3FBID                    p15.02.00  current    used       l3filters
L3FCFTVertex              p15.02.01  current    used       l3filters
L3FCorrPassFraction       p15.02.00  local      used       l3filters
L3FEJets                  p16-00-01  local      used       l3filters
L3FEle                    p12-br-02  current    used       l3filters
L3FHt                     p12.05.00  current    used       L3FHt
L3FHta                    p16-00-00  local      used       l3filters
L3FIP                     p15.02.00  current    used       l3filters
L3FIP                     p16.00.00  local      used       l3filters
L3FInvMass                p09-10-00  future     used       l3filters
L3FInvMass                v00-00-00  local      used       saveme
L3FInvMass                p16        local      used       l3filters
L3FInvMass                p16v2      local      used       l3filters
L3FJAcop                  p12.05.00  current    used       l3filters
L3FJet                    p13.02.00  current    used       l3filters
L3FMEt                    p09-10-00  current    used       l3filters
L3FMEt                    v00-00-00  local      used       saveme
L3FMHt                    p12.06.00  current    used       l3filters
L3FMarkAndPass            p09-10-00  current    used       l3filters
L3FMuoLocal               p09-10-00  future     used       l3filters
L3FMuon                   p11-04-01  current    used       l3filters
L3FPassFraction           p15.02.00  current    used       l3filters
L3FPhysIP                 p16.00.00  local      used       l3filters
L3FPreScale               p09-10-00  current    used       l3filters
L3FTau                    p09-10-00  current    used       l3filters
L3FTau                    v00-00-00  local      used       saveme
L3FTau                    p16-00-00  local      used       l3filters
L3FTrack                  p12.04.00  current    used       l3filters
L3FdR                     p15        current    used       l3filters
L3GeometryManagement      v00-01-00  current    used       l3geometry_management
L3RunConfigMgr            p09-04-00  current    used       l3run_config
L3TBTagIP                 p15.02.00  current    used       l3fbtag_ip
L3TCFTTrack               v00-00-00  local      used       dummy_package
L3TCFTUnpack              v00-02-09  current    used       l3fcftunpack
L3TCFTUnpack              v00-00-00  local      used       saveme
L3TCFTVertex              p12.04.00  current    used       l3fcft_vertex
L3TCPS                    p09-02-00  future     used       l3fcps
L3TCPSUnpack              p09-09-00  future     used       l3fcpsunp
L3TCPSUnpack              v00-00-00  local      used       saveme
L3TCalCalib               v00-00-00  local      used       dummy_package
L3TCalCluster             p09-10-00  current    used       l3fcalcluster
L3TCalMEt                 p12-00-00  current    used       l3fCalMEt
L3TCalUnp                 p11-08-00  current    used       l3tCalUnpTool
L3TEle                    v00-01-82  current    used       l3femtools
L3TGlobalTracker          p09-02-00  current    used       l3ftrack_global
L3THTrack                 p16.00.02  local      used       l3ftrack_htrk
L3TIPTracker              p16.00.00  local      used       l3fip_track
L3TIPTracker              p16.00.01  local      used       l3fip_track
L3TIsolation              p16-00-00  local      used       l3fisolation
L3TIsolation              p16-01-00  local      used       l3fisolation
L3TJet                    p13.02.00  current    used       l3fjettools
L3TMcVtx                  v00-00-00  local      used       dummy_package
L3TMuoCentralMatch        p09-04-00  current    used       l3fmuo_centralmatch
L3TMuoLocal               p11-04-01  current    used       l3fmuo_local
L3TMuoUnpack              p11-04-01  current    used       l3fmuo_unpack
L3TMuon                   p11-04-01  current    used       l3fMuonTools
L3TNullVertex             v00-00-00  current    used       saveme
L3TNullVertex             v00-00-00  local      used       saveme?
L3TPhysCFTVertex          p15.02.00  current    used       l3fcft_vertex_phys
L3TPhysTracker            p15.03.00  current    used       l3ftrack_phys
L3TPrVtx                  p09-04-00  future     used       l3fvertex
L3TPrVtx                  v00-00-00  local      used       l3fvertex
L3TSmtUnpack              v00-03-10  current    used       l3fsmtunptool
L3TSmtUnpack              v00-00-00  local      used       saveme
L3TSortSMTClusters        v00-00-00  local      used       dummy_package
L3TXYVertex               p15.02.00  current    used       l3fxy_vertex
L3TauHadronic             p11-08-00  current    used       l3fTauTools
L3TsmtCluster             v00-02-16  future     used       l3fsmtcluster
L3TsmtCluster             v00-00-00  local      used       saveme
SRDirective               v00-00-00  local      used       dummy
none                      v00-00-00  current    used       saveme

-- Count the number of each type of stat_curr Objects
SELECT COUNT(*),stat_curr FROM l2_objects
    group by stat_curr HAVING NVL(stat_curr,'?')<='zzz'  AND COUNT(*)>1;

        18 current
         2 future
        20 local
         5 nameold
        19 obsolete

SELECT COUNT(*),stat_curr FROM l3_objects
    group by stat_curr HAVING NVL(stat_curr,'?')<='zzz'  AND COUNT(*)>1;

        39 current
         6 future
        58 local
         2 nameold
        48 obsolete
        24 scrap


-- Single-Row Functions:
-- NVL(expr1,expr2)
-- If expr1 is null, NVL returns expr2; if expr1 is not null, NVL returns expr1. 
-- The arguments expr1 and expr2 can have any datatype. 
-- If their datatypes are different, 
-- Oracle converts expr2 to the datatype of expr1 before comparing them. 
-- The datatype of the return value is always the same as the datatype of expr1, 
-- unless expr1 is character data, 
-- in which case the return value's datatype is VARCHAR2.

-- example from SB
-- -- look for people with the same whod0 name
-- SELECT COUNT(*),NAME FROM PEOPLES
-- GROUP BY NAME HAVING NVL(NAME,'?')<>'?' AND COUNT(*)>1;
-- -- another way
-- SELECT count(*),NAME FROM PEOPLES GROUP BY NAME HAVING COUNT(*)=2;

