AnsweredAssumed Answered

Sql quey to get only folder names

Question asked by arroju.srikanth on May 6, 2011
Latest reply on May 6, 2011 by arroju.srikanth
Hello All
Im  generating the reports through pentaho
i require to get only folder names list through sql query not the document names

CREATE ALGORITHM=UNDEFINED DEFINER=`alfresco`@`localhost` SQL SECURITY DEFINER VIEW `ZKT_VIEWSUBFOLDER` AS select `alf_node`.`id` AS `node_id`,`alf_node_properties`.`string_value` AS `sub_folder_name` from (`alf_node_properties` join `alf_node` on(((`alf_node_properties`.`node_id` = `alf_node`.`id`) and (`alf_node_properties`.`qname_id` = 280))));


CREATE ALGORITHM=UNDEFINED DEFINER=`alfresco`@`localhost` SQL SECURITY DEFINER VIEW `ZKT_VIEWSUBFOLDER2` AS select `ZKT_VIEWSUBFOLDER`.`node_id` AS `node_id`,`alf_node_properties`.`string_value` AS `folder`,substring_index(substring_index(substring_index(`ZKT_VIEWSUBFOLDER`.`sub_folder_name`,'|',-(2)),',',-(1)),'|',1) AS `name` from (`ZKT_VIEWSUBFOLDER` join `alf_node_properties` on(((`alf_node_properties`.`node_id` = `ZKT_VIEWSUBFOLDER`.`node_id`) and (`alf_node_properties`.`qname_id` = 280))));

but wth this query im also getting document names. and some names which doesn' t have .extension are also in the
result.
is there any other way of getting folder names in sql
Thanks,
Srikanth A

Outcomes