SQL Queries - CAST Knowledge Base - Queries on objects - How to list the technologies present ordered by number of objects

 SELECT    c.TECHNOLOGY AS "Technology",
          CASE
                    WHEN c.TECHNOLOGY_TYPE = c.TECHNOLOGY_CUSTOM_TYPE
                    THEN c.TECHNOLOGY_TYPE
                    ELSE c.TECHNOLOGY_TYPE
                                        || '('
                                        || c.TECHNOLOGY_CUSTOM_TYPE
                                        || ' customized)'
          END AS "Technology Type",
          CASE
                    WHEN kb.OBJECTS_NB IS NULL
                    THEN 0
                    ELSE kb.OBJECTS_NB
          END AS "Objects Nb"
FROM      (SELECT  c.IdCat               ,
                   c.CatDsc AS TECHNOLOGY,
                   CASE
                            WHEN c.IdCat < 1000000
                            THEN 'Core'
                            WHEN c.IdCat < 1500000
                            THEN 'CoE'
                            WHEN c.IdCat < 2000000
                            THEN 'Prepackaged'
                            WHEN c.IdCat >= 2000000
                            THEN 'Client'
                   END AS TECHNOLOGY_TYPE,
                   CASE
                            WHEN MAX(tc.IdTyp) < 1000000
                            THEN 'Core'
                            WHEN MAX(tc.IdTyp) < 1500000
                            THEN 'CoE'
                            WHEN MAX(tc.IdTyp) < 2000000
                            THEN 'Prepackaged'
                            WHEN MAX(tc.IdTyp) >= 2000000
                            THEN 'Client'
                   END AS TECHNOLOGY_CUSTOM_TYPE
          FROM     Cat c
                   JOIN CatCat cc
                   ON       cc.IdCat       = c.IdCat
                   AND      cc.IdCatParent = 6000
                   JOIN TypCat tc
                   ON       tc.IdCatParent = c.IdCat
          GROUP BY c.IdCat,
                   c.CatDsc
          )
          c
          LEFT JOIN
                    (SELECT  cc.IdCat,
                             COUNT(1) AS OBJECTS_NB
                    FROM     CatCat cc
                             JOIN TypCat tc
                             ON       tc.IdCatParent = cc.IdCat
                             AND      cc.IdCatParent = 6000
                             JOIN Keys k
                             ON       k.ObjTyp = tc.IdTyp
                    WHERE    tc.IdCatParent    = cc.IdCat
                    GROUP BY cc.IdCat
                    )
                    kb
          ON        kb.IdCat = c.IdCat
ORDER BY
          CASE
                    WHEN kb.OBJECTS_NB IS NULL
                    THEN 0
                    ELSE kb.OBJECTS_NB
          END DESC,
          c.TECHNOLOGY;

Enter the SQL query

Enter the SQL query