locked
Ajuda em SELECT RRS feed

  • Pergunta

  • Boas..
    Tenho uma tabela onde registo os serviços prestados a pessoas, identificado pelo campo NProcesso e outro campo em que defino se alguem da sua famila também foi atendido.


    Nprocesso ElementoFamilia Servico
    1 1,2 asdasd
    1 1 sada
    2 2,4 asdasd
    3 1,3 asdasd
    3 1,2 d
    3 1 d

    Preciso de mostrar o número de pessoas atendidas..
    Referente ao NProcesso 1, ele teria que contar 2 pessoas, o elementofamilia 1 e o 2
    Referente ao NProcesso 2, ele teria que contar 2 pessoas, o elementofamilia 2 e o 4
    Referente ao NProcesso 3, ele teria que contar 3 pessoas, o elementofamilia 1, o 2 e o 3

    Como posso fazer isto?
    sexta-feira, 23 de janeiro de 2009 10:51

Respostas

  • Olá Vitor Martins,

     

    Você declarou a CTE ElementoFamiliaXML, mas usou a tabela servicos_prestados para executar o método Nodes. Tente da seguinte forma:

     

    Code Snippet

    CREATE PROCEDURE dbo.experiencia

     

    AS

    BEGIN

     

    SET NOCOUNT ON;

     

    ;With ElementoFamiliaXML (EFXML) AS

    (select CAST('<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

    REPLACE(elementosaf,',','</e><e>') + '</e></E>' AS XML)

    from servicos_prestados),

     

    Res (NProcesso, elementosaf) AS

    (select X.value('../@NProcesso','int') As NProcesso,

    X.value('.','int') As elementosaf

    from ElementoFamiliaXML As EFXML1

    CROSS APPLY EFXML.nodes('/E/e') As EFXML2 (X))

     

     

    select NProcesso, count(distinct elementosaf) as qtd

    from Res

    group by NProcesso

     

    END

     

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

     

    sexta-feira, 23 de janeiro de 2009 16:21

Todas as Respostas

  • Bom Dia,

     

    Se os dados são gravados delimitados por "," isso é um mal sinal, pois, denota um erro de modelagem e tipicamente torna as consultas mais complicadas (a exemplo dessa). O correto seria não ter esses dados multivalorados em um único campo.

     

    Há como fazer e pode ser mais fácil ou mais difícil. Qual a versão do seu SQL Server ?

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

     

    sexta-feira, 23 de janeiro de 2009 12:12
  • SQL EXPRESS 2005

    O mais correcto seria ter um registo para cada elemento da familia?
    sexta-feira, 23 de janeiro de 2009 12:14
  • Olá Vitor Martins,

     

    Seguramente o mais correto é fazer a separação. Os princípios de normalização não aceitam valores multivalorados em uma única coluna de um único registro. Veja como a separação torna as coisas bem mais simples

     

    Code Snippet

    declare @ElementoFamilia table (

    Nprocesso int, ElementoFamilia int, Servico varchar(20))

     

    insert into @ElementoFamilia values (1,1,'asdasd')

    insert into @ElementoFamilia values (1,2,'asdasd')

    insert into @ElementoFamilia values (1,1,'sada')

    insert into @ElementoFamilia values (2,2,'asdasd')

    insert into @ElementoFamilia values (2,4,'asdasd')

    insert into @ElementoFamilia values (3,1,'asdasd')

    insert into @ElementoFamilia values (3,3,'asdasd')

    insert into @ElementoFamilia values (3,1,'d')

    insert into @ElementoFamilia values (3,2,'d')

    insert into @ElementoFamilia values (3,1,'d')

     

    select NProcesso, COUNT(DISTINCT ElementoFamilia) As QTD

    FROM @ElementoFamilia GROUP BY NProcesso

     

     

    E veja como ter tudo junto torna as coisas bem mais difíceis e menos performáticas

     

    Code Snippet

    declare @ElementoFamilia table (

    Nprocesso int, ElementoFamilia varchar(10), Servico varchar(20))

     

    insert into @ElementoFamilia values (1,'1,2','asdasd')

    insert into @ElementoFamilia values (1,'1','sada')

    insert into @ElementoFamilia values (2,'2,4','asdasd')

    insert into @ElementoFamilia values (3,'1,3','asdasd')

    insert into @ElementoFamilia values (3,'1,2','d')

    insert into @ElementoFamilia values (3,'1','d')

     

    ;With ElementoFamiliaXML (EFXML) AS (

    select CAST(

    '<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

    REPLACE(ElementoFamilia,',','</e><e>') + '</e></E>' AS XML)

    from @ElementoFamilia),

     

    Res (NRProcesso, ElementoFamilia) AS (

    select

    X.value('../@NProcesso','int') As NRProcesso,

    X.value('.','int') As ElementoFamilia

    from ElementoFamiliaXML As EFXML1

    CROSS APPLY

    EFXML.nodes('/E/e') As EFXML2 (X))

     

    select nrprocesso, count(distinct ElementoFamilia) as qtd

    from Res group by nrprocesso

     

    Isso sem contar os problemas de atualização e exclusão. Fica complicado atualizar somente um elemento em uma cadeia de elementos separada por ;

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

    sexta-feira, 23 de janeiro de 2009 13:21
  • Pois..É muito mais fácil..quando construia tabela não me lembrei disso..O objectivo era ter um campo descritivo que permitisse fazer consultas, mas não me lembrei como as iria fazer..

    Não percebi bem que parte do código tenho de introduzir..

    ;With ElementoFamiliaXML (EFXML) AS (

    select CAST(

    '<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

    REPLACE(ElementoFamilia,',','</e><e>') + '</e></E>' AS XML)

    from @ElementoFamilia),

     

    Res (NRProcesso, ElementoFamilia) AS (

    select

    X.value('../@NProcesso','int') As NRProcesso,

    X.value('.','int') As ElementoFamilia

    from ElementoFamiliaXML As EFXML1

    CROSS APPLY

    EFXML.nodes('/E/e') As EFXML2 (X))

     

    select nrprocesso, count(distinct ElementoFamilia) as qtd

    from Res group by nrprocesso

    sexta-feira, 23 de janeiro de 2009 13:46
  • Olá Vitor,

     

    Esse código é para o meu exemplo. Você terá que adaptar ao seu exemplo.

    Esse código converter os elementosfamilia para xml e faz a delimitação e para posteriormente aplicar o COUNT.

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

     

    sexta-feira, 23 de janeiro de 2009 13:50
  • Adaptei e coloquei num SP

    Dá os seguinte erro
    Msg 207, Level 16, State 1, Procedure experiencia, Line 21
    Invalid column name 'EFXML'.
    Msg 9506, Level 16, State 1, Procedure experiencia, Line 21
    The XMLDT method 'nodes' can only be invoked on columns of type xml.
    sexta-feira, 23 de janeiro de 2009 13:57
  • Tenho assim
    CREATE PROCEDURE dbo.experiencia 

    AS
    BEGIN

    SET NOCOUNT ON;

    ;With ElementoFamiliaXML (EFXML) AS 
    (select CAST('<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +
    REPLACE(elementosaf,',','</e><e>') + '</e></E>' AS XML)
    from servicos_prestados), Res (NProcesso, elementosaf) AS 
    (select X.value('../@NProcesso','int') As NProcesso,
    X.value('.','int') As elementosaf
    from servicos_prestados As EFXML1
    CROSS APPLY EFXML.nodes('/E/e') As EFXML2 (X))
     
    select NProcesso, count(distinct elementosaf) as qtd
    from Res 
    group by NProcesso

    END
    sexta-feira, 23 de janeiro de 2009 14:47
  • Olá Vitor Martins,

     

    Você declarou a CTE ElementoFamiliaXML, mas usou a tabela servicos_prestados para executar o método Nodes. Tente da seguinte forma:

     

    Code Snippet

    CREATE PROCEDURE dbo.experiencia

     

    AS

    BEGIN

     

    SET NOCOUNT ON;

     

    ;With ElementoFamiliaXML (EFXML) AS

    (select CAST('<E NProcesso="' + cast(NProcesso as varchar(4)) + '"><e>' +

    REPLACE(elementosaf,',','</e><e>') + '</e></E>' AS XML)

    from servicos_prestados),

     

    Res (NProcesso, elementosaf) AS

    (select X.value('../@NProcesso','int') As NProcesso,

    X.value('.','int') As elementosaf

    from ElementoFamiliaXML As EFXML1

    CROSS APPLY EFXML.nodes('/E/e') As EFXML2 (X))

     

     

    select NProcesso, count(distinct elementosaf) as qtd

    from Res

    group by NProcesso

     

    END

     

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

     

    sexta-feira, 23 de janeiro de 2009 16:21
  • Obrigado..
    Era isso mesmo..

    Obrigado Mestre..


    Só uma ultima pergunta qual seria a melhor estrutura para a tabela?
    sexta-feira, 23 de janeiro de 2009 17:18
  • Olá Vitor,

     

    Que bom que lhe atendeu. Por favor classifique a resposta.

    Uma estrutura mais adequada vai depender das regras (não posso arbitrar sem conhecer suas regras de negócio). Se esse for o caso, especifique-as e abra uma nova thread (são dúvidas separadas).

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

     

    sexta-feira, 23 de janeiro de 2009 17:51