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?
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;
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');
Best Answer
In your case your subquery is returning two columns to the first query expecting one column. You can change your query as follow.
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.