Query 1 -->SELECT TOP 1 A458_cod_grp_area, A454_cod_area, A458_dsc_grp_area, A454_dsc_area,A458_dsc_grp_areaTexto, A454_dsc_areaTexto, A454_dsc_area_destaque, A454_tpo_area, A450_tpo_menu, A454_tpo_apresentacao, A454_configuracao, A450_ind_abrir_popup, A450_altura_frame, A450_url, A458_ordem_sel, A458_ordem, A454_ordem, A000_ordem_request FROM (SELECT T454.A458_cod_grp_area, T454.A454_cod_area, REPLACE(LOWER(ISNULL(A477_nom_area,A458_dsc_grp_area)), ' ', '-') collate sql_latin1_general_cp1251_cs_as A458_dsc_grp_area, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area), '/', ''), '-', ''), ' ', ' '), ' ', '-'),'.', ''), '-»-',''), '-»', '') collate sql_latin1_general_cp1251_cs_as A454_dsc_area, REPLACE(LOWER(ISNULL(A477_nom_area,A458_dsc_grp_area)),'_',' ') A458_dsc_grp_areaTexto, A454_dsc_area A454_dsc_areaTexto, A454_dsc_area_destaque, A454_tpo_area, 'GRP' A450_tpo_menu, ISNULL(A454_tpo_apresentacao,'SUP') A454_tpo_apresentacao, ISNULL(A454_configuracao,'') A454_configuracao,7 A450_ind_abrir_popup, A454_altura_area A450_altura_frame, ISNULL(A454_url,'') A450_url, (SELECT (ISNULL((SELECT Registro FROM dbo.Split(T0.Registro, '#') WHERE indice = 2 ), 99)) FROM dbo.Split(?, ',') T0 WHERE ISNULL((SELECT Registro FROM dbo.Split(T0.Registro, '#') WHERE indice = 1 ), 99) = CONVERT(VARCHAR, T458.A458_cod_grp_area)) A458_ordem_sel, ISNULL(A458_ordem,0) A458_ordem, ISNULL(A454_ordem,0) A454_ordem, (CASE WHEN REPLACE(ISNULL(A477_nom_area,A458_dsc_grp_area), ' ', '-') collate Latin1_General_CI_AI LIKE ? AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area), '/', ''), '-', ''), ' ', ' '), ' ', '-'), '.', ''),'-»-', ''), '-»',''), '&','e') collate Latin1_General_CI_AI LIKE ? THEN 0 WHEN REPLACE(ISNULL(A477_nom_area,A458_dsc_grp_area), ' ', '-') collate Latin1_General_CI_AI LIKE ? AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area), '/', ''), '-', ''), ' ', ' '), ' ', '-'), '.', ''),'-»-', ''), '-»',''), '&','e') collate Latin1_General_CI_AI LIKE ? THEN 1 WHEN REPLACE(ISNULL(A477_nom_area,A458_dsc_grp_area), ' ', '-') collate Latin1_General_CI_AI LIKE ? THEN 2 ELSE 3 END) A000_ordem_request FROM dbo.T458_PORTAL_GRUPO_AREA T458 WITH(NOLOCK) INNER JOIN dbo.T454_PORTAL_AREAS T454 WITH(NOLOCK) ON T458.A048_cod_site = T454.A048_cod_site AND T458.A449_cod_portal = T454.A449_cod_portal AND T458.A458_cod_grp_area = T454.A458_cod_grp_area OUTER APPLY(SELECT T477.A458_cod_grp_area, T477.A477_nom_area FROM dbo.T477_GRUPO_AREAS_AGENCIA_CUSTOMIZADA T477 WHERE T477.A048_cod_site = T458.A048_cod_site AND T477.A449_cod_portal = T458.A449_cod_portal AND T477.A458_cod_grp_area = T458.A458_cod_grp_area AND T477.A003_cod_empresa = 22483 ) TAB_T477 WHERE T454.A048_cod_site = ? AND T454.A449_cod_portal = ? AND ISNULL(A454_ind_ocultar_agencia,0) = 0 AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»',''),'&','e') collate Latin1_General_CI_AI LIKE ? OR ISNULL(A454_dsc_area,'') <> '') AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(ISNULL(A477_nom_area,A458_dsc_grp_area)),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»','') collate Latin1_General_CI_AI LIKE ? OR ISNULL(ISNULL(A477_nom_area,A458_dsc_grp_area),'') <> '') AND (ISNULL(T454.A454_tpo_acesso,'PRE') = 'PRE' OR (A454_tpo_acesso = 'AGE' AND '' = 'EXT_VENDEDOR') OR (A454_tpo_acesso = 'CON' AND '' = 'EXT_CONSUMIDOR') OR (A454_tpo_acesso = 'RES' AND ('' = 'EXT_VENDEDOR' OR '' = 'EXT_CONSUMIDOR')) OR (A454_tpo_acesso = 'PUB' AND '' = '')) AND T458.A458_cod_grp_area IN(SELECT (SELECT Registro FROM dbo.Split(T0.Registro, '#') WHERE indice = 1) FROM dbo.Split( ? , ',') T0) UNION ALL SELECT (CASE WHEN A450_cod_menu_site_pai IS NULL THEN A450_cod_menu_site ELSE A450_cod_menu_site_pai END) A458_cod_grp_area, A450_cod_menu_site A454_cod_area, A450_nome_menu A458_dsc_grp_area, A450_nome_menu A454_dsc_area, '' A458_cod_grp_areaTexto , '' A454_dsc_areaTexto, '' A454_dsc_area_destaque , 'LIN' A454_tpo_area, A450_tpo_menu, 'SUP' A454_tpo_apresentacao, '' A454_configuracao, A450_ind_abrir_popup, A450_altura_frame, A450_url, CASE WHEN A450_cod_menu_site_pai IS NULL THEN A450_num_ordenacao ELSE (SELECT T450B.A450_num_ordenacao FROM dbo.T450_PORTAL_MENU T450B WHERE T450B.A450_cod_menu_site = T450.A450_cod_menu_site_pai AND T450B.A048_cod_site = T450.A048_cod_site)+A450_num_ordenacao END A458_ordem_sel, CASE WHEN A450_cod_menu_site_pai IS NULL THEN A450_num_ordenacao ELSE (SELECT T450B.A450_num_ordenacao FROM dbo.T450_PORTAL_MENU T450B WHERE T450B.A450_cod_menu_site = T450.A450_cod_menu_site_pai AND T450B.A048_cod_site = T450.A048_cod_site)+A450_num_ordenacao END A458_ordem,99 A454_ordem, (CASE WHEN A450_cod_menu_site_pai IS NOT NULL THEN CASE WHEN REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( LOWER(A450_nome_menu), '/', ''), '-', ''), ' ', ' '), ' ', '-'), '.', ''), '-»-', ''), '-»', ''), '&', 'e') collate Latin1_General_CI_AI LIKE ? THEN 0 ELSE 7 END ELSE CASE WHEN REPLACE(A450_nome_menu, ' ', '-') collate Latin1_General_CI_AI LIKE ? THEN 1 ELSE 6 END END ) A000_ordem_request FROM dbo.T450_PORTAL_MENU T450 WITH(NOLOCK) WHERE A048_cod_site = ? AND A449_cod_portal = ? AND A003_cod_empresa = ? AND ISNULL(A450_tpo_menu,'MEN') = 'ABA' AND ISNULL(A450_ind_desativado,0) = 0 AND A321_seq_func_virtual = dbo.FN_DESCRIPTOGRAFA_PWD(?,3) AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A450_nome_menu),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»',''),'&','e') collate Latin1_General_CI_AI LIKE ? OR ISNULL(A450_nome_menu,'') <> '') AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A450_nome_menu),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»',''),'&','e') collate Latin1_General_CI_AI LIKE ? OR ISNULL(A450_nome_menu,'') <> '') ) GRP_MENU WHERE A458_cod_grp_area NOT IN(SELECT A458_cod_grp_area FROM T458_PORTAL_GRUPO_AREA T458C WITH(NOLOCK) WHERE ? NOT IN (SELECT registro FROM SplitR(A458_exclusivo_vendedor_virtual,',')) AND ISNULL(A458_exclusivo_vendedor_virtual,'') <> ''AND T458C.A048_cod_site = ? AND T458C.A449_cod_portal = ?) AND A458_cod_grp_area NOT IN(SELECT A458_cod_grp_area FROM T458_PORTAL_GRUPO_AREA T458C WITH(NOLOCK) WHERE ? IN (SELECT registro FROM SplitR(A458_bloqueado_vendedor_virtual,',') WHERE A458_bloqueado_vendedor_virtual <> '') AND T458C.A048_cod_site = ? AND T458C.A449_cod_portal = ?) AND GRP_MENU.A454_cod_area NOT IN(SELECT A454_cod_area FROM T454_PORTAL_AREAS T454C WITH(NOLOCK) WHERE ? NOT IN (SELECT registro FROM SplitR(A454_exclusivo_vendedor_virtual,',') ) AND ISNULL(A454_exclusivo_vendedor_virtual,'') <> '' AND T454C.A048_cod_site = ? AND T454C.A449_cod_portal = ?) AND GRP_MENU.A454_cod_area NOT IN(SELECT A454_cod_area FROM T454_PORTAL_AREAS T454C WITH(NOLOCK) WHERE ? IN (SELECT registro FROM SplitR(A454_bloqueado_vendedor_virtual,',') WHERE ISNULL(A454_bloqueado_vendedor_virtual,'') <> '') AND T454C.A048_cod_site = ? AND T454C.A449_cod_portal = ?) ORDER BY A000_ordem_request, A458_ordem_sel, A458_ordem, A454_ordem OPTION (RECOMPILE)
Query Final -->SELECT TOP 1 A458_cod_grp_area, A454_cod_area, A458_dsc_grp_area, A454_dsc_area,A458_dsc_grp_areaTexto, A454_dsc_areaTexto, A454_dsc_area_destaque, A454_tpo_area, A450_tpo_menu, A454_tpo_apresentacao, A454_configuracao, A450_ind_abrir_popup, A450_altura_frame, A450_url, A458_ordem_sel, A458_ordem, A454_ordem, A000_ordem_request FROM (SELECT T454.A458_cod_grp_area, T454.A454_cod_area, REPLACE(LOWER(ISNULL(A477_nom_area,A458_dsc_grp_area)), ' ', '-') collate sql_latin1_general_cp1251_cs_as A458_dsc_grp_area, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area), '/', ''), '-', ''), ' ', ' '), ' ', '-'),'.', ''), '-»-',''), '-»', '') collate sql_latin1_general_cp1251_cs_as A454_dsc_area, REPLACE(LOWER(ISNULL(A477_nom_area,A458_dsc_grp_area)),'_',' ') A458_dsc_grp_areaTexto, A454_dsc_area A454_dsc_areaTexto, A454_dsc_area_destaque, A454_tpo_area, 'GRP' A450_tpo_menu, ISNULL(A454_tpo_apresentacao,'SUP') A454_tpo_apresentacao, ISNULL(A454_configuracao,'') A454_configuracao,7 A450_ind_abrir_popup, A454_altura_area A450_altura_frame, ISNULL(A454_url,'') A450_url, (SELECT (ISNULL((SELECT Registro FROM dbo.Split(T0.Registro, '#') WHERE indice = 2 ), 99)) FROM dbo.Split('25#1,1#2,11#6,27#11,14#3,3#4,4#5,15#8,28#24,23#10,22#12,21#9', ',') T0 WHERE ISNULL((SELECT Registro FROM dbo.Split(T0.Registro, '#') WHERE indice = 1 ), 99) = CONVERT(VARCHAR, T458.A458_cod_grp_area)) A458_ordem_sel, ISNULL(A458_ordem,0) A458_ordem, ISNULL(A454_ordem,0) A454_ordem, (CASE WHEN REPLACE(ISNULL(A477_nom_area,A458_dsc_grp_area), ' ', '-') collate Latin1_General_CI_AI LIKE '%0%' AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area), '/', ''), '-', ''), ' ', ' '), ' ', '-'), '.', ''),'-»-', ''), '-»',''), '&','e') collate Latin1_General_CI_AI LIKE '%###%' THEN 0 WHEN REPLACE(ISNULL(A477_nom_area,A458_dsc_grp_area), ' ', '-') collate Latin1_General_CI_AI LIKE '%0%' AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area), '/', ''), '-', ''), ' ', ' '), ' ', '-'), '.', ''),'-»-', ''), '-»',''), '&','e') collate Latin1_General_CI_AI LIKE '%###%' THEN 1 WHEN REPLACE(ISNULL(A477_nom_area,A458_dsc_grp_area), ' ', '-') collate Latin1_General_CI_AI LIKE '%0%' THEN 2 ELSE 3 END) A000_ordem_request FROM dbo.T458_PORTAL_GRUPO_AREA T458 WITH(NOLOCK) INNER JOIN dbo.T454_PORTAL_AREAS T454 WITH(NOLOCK) ON T458.A048_cod_site = T454.A048_cod_site AND T458.A449_cod_portal = T454.A449_cod_portal AND T458.A458_cod_grp_area = T454.A458_cod_grp_area OUTER APPLY(SELECT T477.A458_cod_grp_area, T477.A477_nom_area FROM dbo.T477_GRUPO_AREAS_AGENCIA_CUSTOMIZADA T477 WHERE T477.A048_cod_site = T458.A048_cod_site AND T477.A449_cod_portal = T458.A449_cod_portal AND T477.A458_cod_grp_area = T458.A458_cod_grp_area AND T477.A003_cod_empresa = 22483 ) TAB_T477 WHERE T454.A048_cod_site = '4' AND T454.A449_cod_portal = '23' AND ISNULL(A454_ind_ocultar_agencia,0) = 0 AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A454_dsc_area),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»',''),'&','e') collate Latin1_General_CI_AI LIKE '%###%' OR ISNULL(A454_dsc_area,'') <> '') AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(ISNULL(A477_nom_area,A458_dsc_grp_area)),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»','') collate Latin1_General_CI_AI LIKE '%0%' OR ISNULL(ISNULL(A477_nom_area,A458_dsc_grp_area),'') <> '') AND (ISNULL(T454.A454_tpo_acesso,'PRE') = 'PRE' OR (A454_tpo_acesso = 'AGE' AND '' = 'EXT_VENDEDOR') OR (A454_tpo_acesso = 'CON' AND '' = 'EXT_CONSUMIDOR') OR (A454_tpo_acesso = 'RES' AND ('' = 'EXT_VENDEDOR' OR '' = 'EXT_CONSUMIDOR')) OR (A454_tpo_acesso = 'PUB' AND '' = '')) AND T458.A458_cod_grp_area IN(SELECT (SELECT Registro FROM dbo.Split(T0.Registro, '#') WHERE indice = 1) FROM dbo.Split( '25#1,1#2,11#6,27#11,14#3,3#4,4#5,15#8,28#24,23#10,22#12,21#9' , ',') T0) UNION ALL SELECT (CASE WHEN A450_cod_menu_site_pai IS NULL THEN A450_cod_menu_site ELSE A450_cod_menu_site_pai END) A458_cod_grp_area, A450_cod_menu_site A454_cod_area, A450_nome_menu A458_dsc_grp_area, A450_nome_menu A454_dsc_area, '' A458_cod_grp_areaTexto , '' A454_dsc_areaTexto, '' A454_dsc_area_destaque , 'LIN' A454_tpo_area, A450_tpo_menu, 'SUP' A454_tpo_apresentacao, '' A454_configuracao, A450_ind_abrir_popup, A450_altura_frame, A450_url, CASE WHEN A450_cod_menu_site_pai IS NULL THEN A450_num_ordenacao ELSE (SELECT T450B.A450_num_ordenacao FROM dbo.T450_PORTAL_MENU T450B WHERE T450B.A450_cod_menu_site = T450.A450_cod_menu_site_pai AND T450B.A048_cod_site = T450.A048_cod_site)+A450_num_ordenacao END A458_ordem_sel, CASE WHEN A450_cod_menu_site_pai IS NULL THEN A450_num_ordenacao ELSE (SELECT T450B.A450_num_ordenacao FROM dbo.T450_PORTAL_MENU T450B WHERE T450B.A450_cod_menu_site = T450.A450_cod_menu_site_pai AND T450B.A048_cod_site = T450.A048_cod_site)+A450_num_ordenacao END A458_ordem,99 A454_ordem, (CASE WHEN A450_cod_menu_site_pai IS NOT NULL THEN CASE WHEN REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( LOWER(A450_nome_menu), '/', ''), '-', ''), ' ', ' '), ' ', '-'), '.', ''), '-»-', ''), '-»', ''), '&', 'e') collate Latin1_General_CI_AI LIKE '###' THEN 0 ELSE 7 END ELSE CASE WHEN REPLACE(A450_nome_menu, ' ', '-') collate Latin1_General_CI_AI LIKE '%0%' THEN 1 ELSE 6 END END ) A000_ordem_request FROM dbo.T450_PORTAL_MENU T450 WITH(NOLOCK) WHERE A048_cod_site = '4' AND A449_cod_portal = '23' AND A003_cod_empresa = '22483' AND ISNULL(A450_tpo_menu,'MEN') = 'ABA' AND ISNULL(A450_ind_desativado,0) = 0 AND A321_seq_func_virtual = dbo.FN_DESCRIPTOGRAFA_PWD('MMDAAV',3) AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A450_nome_menu),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»',''),'&','e') collate Latin1_General_CI_AI LIKE '%###%' OR ISNULL(A450_nome_menu,'') <> '') AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(A450_nome_menu),'/',''),'-',''),' ',' '),' ','-'),'.',''),'-»-',''),'-»',''),'&','e') collate Latin1_General_CI_AI LIKE '%0%' OR ISNULL(A450_nome_menu,'') <> '') ) GRP_MENU WHERE A458_cod_grp_area NOT IN(SELECT A458_cod_grp_area FROM T458_PORTAL_GRUPO_AREA T458C WITH(NOLOCK) WHERE '8771' NOT IN (SELECT registro FROM SplitR(A458_exclusivo_vendedor_virtual,',')) AND ISNULL(A458_exclusivo_vendedor_virtual,'') <> ''AND T458C.A048_cod_site = '4' AND T458C.A449_cod_portal = '23') AND A458_cod_grp_area NOT IN(SELECT A458_cod_grp_area FROM T458_PORTAL_GRUPO_AREA T458C WITH(NOLOCK) WHERE '8771' IN (SELECT registro FROM SplitR(A458_bloqueado_vendedor_virtual,',') WHERE A458_bloqueado_vendedor_virtual <> '') AND T458C.A048_cod_site = '4' AND T458C.A449_cod_portal = '23') AND GRP_MENU.A454_cod_area NOT IN(SELECT A454_cod_area FROM T454_PORTAL_AREAS T454C WITH(NOLOCK) WHERE '8771' NOT IN (SELECT registro FROM SplitR(A454_exclusivo_vendedor_virtual,',') ) AND ISNULL(A454_exclusivo_vendedor_virtual,'') <> '' AND T454C.A048_cod_site = '4' AND T454C.A449_cod_portal = '23') AND GRP_MENU.A454_cod_area NOT IN(SELECT A454_cod_area FROM T454_PORTAL_AREAS T454C WITH(NOLOCK) WHERE '8771' IN (SELECT registro FROM SplitR(A454_bloqueado_vendedor_virtual,',') WHERE ISNULL(A454_bloqueado_vendedor_virtual,'') <> '') AND T454C.A048_cod_site = '4' AND T454C.A449_cod_portal = '23') ORDER BY A000_ordem_request, A458_ordem_sel, A458_ordem, A454_ordem OPTION (RECOMPILE)