Pular para o conteúdo
  • Este tópico contém 2 respostas, 2 vozes e foi atualizado pela última vez 10 anos, 1 mês atrás por Avatar de vanessa mortago amatovanessa mortago amato.
Visualizando 3 posts - 1 até 3 (de 3 do total)
  • Autor
    Posts
  • #74166
    Anônimo

      Galera, estou precisando fazer uma busca no oracle 9i onde os itens existentes na pesquisa devem vir posicionados na coluna. Por exemplo:

      select nome, tipo from clientes where cidade = xx

      como resposta preciso obter:

      joao fisica maria fisica bretas juridica

      existe algum comando que faz esta invers?o?

      Rog?rio

      #106474
      Avatar de vanessa mortago amatovanessa mortago amato
      Participante

        Rogerio,

        Voce pode fazer o seguinte:

        1 . Criar o type string_agg_type
        [spoiler]create or replace type string_agg_type as object ( total varchar2(4000),
        static function
        ODCIAggregateInitialize(sctx IN OUT string_agg_type )
        return number,
        member function
        ODCIAggregateIterate(self IN OUT string_agg_type ,
        value IN varchar2 )
        return number,
        member function
        ODCIAggregateTerminate(self IN string_agg_type,
        returnValue OUT varchar2,
        flags IN number)
        return number,
        member function
        ODCIAggregateMerge(self IN OUT string_agg_type,
        ctx2 IN string_agg_type)
        return number
        );
        /

        Type created.

        create or replace type body string_agg_type
        is

        static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
        return number
        is
        begin
        sctx := string_agg_type( null );
        return ODCIConst.Success;
        end;

        member function ODCIAggregateIterate(self IN OUT string_agg_type,
        value IN varchar2 )
        return number
        is
        begin
        self.total := self.total || ‘,’ || value;
        return ODCIConst.Success;
        end;

        member function ODCIAggregateTerminate(self IN string_agg_type,
        returnValue OUT varchar2,
        flags IN number)
        return number
        is
        begin
        returnValue := ltrim(self.total,’,’);
        return ODCIConst.Success;
        end;

        member function ODCIAggregateMerge(self IN OUT string_agg_type,
        ctx2 IN string_agg_type)
        return number
        is
        begin
        self.total := self.total || ctx2.total;
        return ODCIConst.Success;
        end;

        end;
        /

        Type body created.[/spoiler]

        1. Criar a função para fazer a agregação:
          [spoiler]CREATE or replace
          FUNCTION stragg(input varchar2 )
          RETURN varchar2
          PARALLEL_ENABLE AGGREGATE USING string_agg_type;
          /

        Function created.
        [/spoiler]

        3.Usar a funçao:
        select stragg(nome||’ ‘||tipo) from clientes where cidade=xx;

        Essa dica eu peguei no site do AskTom. Achei muito bacana.

        Boa sorte,
        Vanessa

        #106475
        Avatar de vanessa mortago amatovanessa mortago amato
        Participante

          Rogerio,

          Voce pode fazer o seguinte:

          1 . Criar o type string_agg_type
          [spoiler]create or replace type string_agg_type as object ( total varchar2(4000),
          static function
          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
          return number,
          member function
          ODCIAggregateIterate(self IN OUT string_agg_type ,
          value IN varchar2 )
          return number,
          member function
          ODCIAggregateTerminate(self IN string_agg_type,
          returnValue OUT varchar2,
          flags IN number)
          return number,
          member function
          ODCIAggregateMerge(self IN OUT string_agg_type,
          ctx2 IN string_agg_type)
          return number
          );
          /

          Type created.

          create or replace type body string_agg_type
          is

          static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
          return number
          is
          begin
          sctx := string_agg_type( null );
          return ODCIConst.Success;
          end;

          member function ODCIAggregateIterate(self IN OUT string_agg_type,
          value IN varchar2 )
          return number
          is
          begin
          self.total := self.total || ‘,’ || value;
          return ODCIConst.Success;
          end;

          member function ODCIAggregateTerminate(self IN string_agg_type,
          returnValue OUT varchar2,
          flags IN number)
          return number
          is
          begin
          returnValue := ltrim(self.total,’,’);
          return ODCIConst.Success;
          end;

          member function ODCIAggregateMerge(self IN OUT string_agg_type,
          ctx2 IN string_agg_type)
          return number
          is
          begin
          self.total := self.total || ctx2.total;
          return ODCIConst.Success;
          end;

          end;
          /

          Type body created.[/spoiler]

          2. Criar a função para fazer a agregação:
          [spoiler]CREATE or replace
          FUNCTION stragg(input varchar2 )
          RETURN varchar2
          PARALLEL_ENABLE AGGREGATE USING string_agg_type;
          /

          Function created.
          [/spoiler]

          3.Usar a funçao:
          select stragg(nome||’ ‘||tipo) from clientes where cidade=xx;

          Essa dica eu peguei no site do AskTom. Achei muito bacana.

          Boa sorte,
          Vanessa

        Visualizando 3 posts - 1 até 3 (de 3 do total)
        • Você deve fazer login para responder a este tópico.
        plugins premium WordPress