Untitled
unknown
sqlserver
2 years ago
1.9 kB
8
Indexable
DECLARE @work_item_id_inicial INT; DECLARE @board_id_inicial INT; DECLARE @item_id_inicial INT SET @item_id_inicial = 1; SET @work_item_id_inicial = 182948; SET @board_id_inicial = 376; WITH RECURSIVA AS ( SELECT @item_id_inicial AS nivel, dir.directory_name AS directory_name_nivel_1, CAST('' AS NVARCHAR(800)) as directory_name_nivel_2, CAST('' AS NVARCHAR(800)) as directory_name_nivel_3, dir.work_item_id, dir.directory_id, dir.father_directory_id, dir.board_id, arc.archive_id, arc.archive_original_name, arc.archive_azure_name, arc.archive_ocean_url_description, arc.archive_type FROM oce_files_directories dir INNER JOIN oce_files_directories_archives dia ON dir.directory_id = dia.directory_id INNER JOIN oce_files_archives arc ON dia.archive_id = arc.archive_id WHERE dir.work_item_id = @work_item_id_inicial AND dir.board_id = @board_id_inicial UNION ALL SELECT rec.nivel + 1 AS nivel, rec.directory_name_nivel_1, case when rec.nivel + 1 = 2 then dir.directory_name when rec.nivel + 1 = 3 then rec.directory_name_nivel_2 else '' end as directory_name_nivel_2, case when rec.nivel + 1 = 2 then '' when rec.nivel + 1 = 3 then dir.directory_name else '' end as directory_name_nivel_3, dir.work_item_id, dir.directory_id, dir.father_directory_id, dir.board_id, arc.archive_id, arc.archive_original_name, arc.archive_azure_name, arc.archive_ocean_url_description, arc.archive_type FROM oce_files_directories dir INNER JOIN RECURSIVA rec ON dir.father_directory_id = rec.directory_id AND dir.board_id = rec.board_id INNER JOIN oce_files_directories_archives dia ON dir.directory_id = dia.directory_id INNER JOIN oce_files_archives arc ON dia.archive_id = arc.archive_id ) SELECT DISTINCT * FROM RECURSIVA;
Editor is loading...