Select multiple columns from subquery - Connect by

Question

I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy:

   SELECT  folder_name name,
      document_count,
      (SELECT SUM(e2.document_count) 
        FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
      )
      sum_sal
    FROM
      folder_mapping e1

Now I have a requirement to calculate the sum of one more column

    SELECT  folder_name name,
      document_count,
      (SELECT SUM(e2.document_count), **sum(e2.folder_count)**

        FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
      )
      sum_sal
    FROM
      folder_mapping e1

This query throws error:>ORA-00913: too many values.

But what can be done to achieve the same?

Database structure with data:

Intended output:

Query with help of JSapkota

SELECT
  fm.folder_mapping_id,
  fm_main.folder_name,
  fm.Total_Doc_Count,
  fm.Total_Folder_Count,
  --  fm.shared_flag
  DECODE(fm.shared_flag, fm_main.shared_flag
  ||'', (DECODE(fm.shared_flag, '0', 'Not Shared', '1', 'Fully Shared',
  'Partially Shared')), 'Partially Shared') shared_flag
FROM
  (
    SELECT
      folder_mapping_id,
      SUM(document_count) Total_Doc_Count,
      SUM(folder_count) Total_Folder_Count,
      SUM(SHARED_FLAG) SHARED_FLAG,
      SUM(attachment_flag) attachment_flag
    FROM
      (
        SELECT
          CONNECT_BY_ROOT folder_mapping_id AS folder_mapping_id,
          document_count,
          folder_count,
          SHARED_FLAG,
          attachment_flag
        FROM
          (
            SELECT
              *
            FROM
              folder_mapping
            WHERE
              organization_id='Org_498'
          )
          --where organization_id='Org_498'
          CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id
      )
    GROUP BY
      folder_mapping_id
  )
  fm,
  (
    SELECT
      folder_mapping_id,
      document_count,
      folder_name,
      folder_count,
      SHARED_FLAG
    FROM
      folder_mapping
    WHERE
      organization_id='Org_498'
  )
  fm_main
WHERE
  fm_main.folder_mapping_id = fm.folder_mapping_id
ORDER BY
  fm.folder_mapping_id;

With input as follows:

Current output is:

The output is pretty much OK except the fact that we want root9 to be displayed as fully shared since it is a subfolder of root8 which is fully shared.

Convention followed for shared flag
--0 - not shared
--1 - fully shared
--2 - partially shared

In case a folder is fully shared its subfolders are fully shared as well.

In case a folder is partially shared or fully shared its parent is partially shared.

Insert queries:

Insert into     FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (1,'root1','111',0,2,3,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (2,'root2','111',1,2,33,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (3,'root3','111',1,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (4,'root4','111',2,2,2,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (5,'root5','111',3,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (6,'root6','111',5,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (7,'root7','111',6,2,1,'0','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (8,'root8','111',7,2,1,'1','0','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');
Insert into FOLDER_MAPPING (FOLDER_MAPPING_ID,FOLDER_NAME,FOLDER_FILENET_ID,PARENT_FOLDER_MAPPING_ID,FOLDER_COUNT,DOCUMENT_COUNT,SHARED_FLAG,ATTACHMENT_FLAG,ORGANIZATION_ID,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,TYPE) values (9,'root9','111',8,2,1,'0','1','Org_498',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'11',to_timestamp('02-MAY-16 03.06.24.352000000 PM','DD-MON-RR HH.MI.SS.FF AM'),'1','1');

Answer 1

ORA-00913 too many values

Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT.
Action: Check the number of items in each set and change the SQL statement to make them equal.

In your case your subquery is returning two columns to the first query expecting one column. You can change your query as follow.

SELECT  folder_name name,
  document_count,
  (SELECT SUM(e2.document_count)
    FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
  )
  sum_sal, (SELECT SUM(e2.document_count)
    FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
  ) folder_count_total
FROM
  folder_mapping e1

That was just the fix for your error. Actually you can use the following query to get the desired output which is better(In terms of cost) than the original query.

SQL> desc folder_mapping;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FOLDER_MAPPING_ID                  NUMBER
 FOLDER_NAME                        VARCHAR2(20)
 FOLDER_FILENET_ID                  NUMBER
 PARENT_FOLDER_MAPPING_ID               NUMBER
 FOLDER_COUNT                       NUMBER
 DOCUMENT_COUNT                     NUMBER
 SHARED_FLAG                        NUMBER
 ATTACHMENT_FLAG                    NUMBER

SQL> select * from folder_mapping;

FOLDER_MAPPING_ID FOLDER_NAME          FOLDER_FILENET_ID
----------------- -------------------- -----------------
PARENT_FOLDER_MAPPING_ID FOLDER_COUNT DOCUMENT_COUNT SHARED_FLAG ATTACHMENT_FLAG
------------------------ ------------ -------------- ----------- ---------------
        4 root3                  111
               2        2          2           0           0

        1 root                    11
               0        2          3           0           0

        2 root1                  111
               1        2         33           0           0



SQL> SELECT folder_name, SUM(document_count) "Total_Doc_Count", SUM(folder_count) "Total_Folder_Count"  FROM(
   SELECT CONNECT_BY_ROOT folder_name as folder_name, document_count, folder_count
      FROM folder_mapping
      CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id)
      GROUP BY folder_name;   

FOLDER_NAME      Total_Doc_Count Total_Folder_Count
-------------------- --------------- ------------------
root1                 35              4
root                  38              6
root3                  2              2

SQL>