Hierarchical query-Array in SQL

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 26 Feb 2008 7:48 am

Good day folks.
I have a table with the following fields dt_pedido cd_produto qt_previsto qt_pedido need to do a query to return more or less it _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ dt_pedido _ _ _ _ _ _ _ _ _ _ _ dt_pedido cd_produto _ _ _ qt_previsto qt_pedido _ _ _ qt_previsto qt_pedido cd_produto ___ qt_previsto ___ qt_pedido qt_previsto qt_pedido Example _ _ _ _ _ _ _ _ 2/26/2008 _ _ _ _ 2/27/2008 _ _ _ 132 _ _ _ 35 _ _ _ 150 _ _ _ _ _ _ _ 110200456 _ _ _ _ _ _ _ _ _ _ 3 _ 15 _ _ _ _ _ _ _ _ 20 25 try to use the hierarchical query with level and connect bybut I'm not even close to something coherent.

If anyone can help me I appreciate it a lot.

Thanks João Luís Amorim
jlamorim
Location: Florianópolis - SC

Poston Tue, 26 Feb 2008 9:27 am

One question: the dates are fixed?
And the query must always return two dates for each product?
Ricardo Carmo
Location: Maceió-AL

Poston Tue, 26 Feb 2008 10:32 am

Good morning.
The dates are not fixed. Are the days you do have orders, happens to some day have no application.
The query can return multiple dates per product. I could see all the requests for a week, fortnight, etc.

Thank you
jlamorim
Location: Florianópolis - SC

Poston Tue, 26 Feb 2008 11:56 am

brow, what popped into my head at the time was the creation of two functions: status_produto : Returns the value of the qt_previsto or qt_pedido for a given product on a specific day, depending on the variable p_tipo;

minha_consulta : Returns the data that you want to, by restricting the dates to a range provided. As for performance, it may leave something to be desired, test, maybe you could improve this code.

A detail: the header, with the values of dates, you're on your own.

The first function:
Code: Select all
FUNCTION status_produto (
   p_cd_produto   IN   NUMBER,
   p_dat               VARCHAR2,
   p_tipo         IN   NUMBER
)
   RETURN NUMBER IS
   p_quant   NUMBER;
BEGIN
   SELECT NVL (DECODE (p_tipo, 0, qt_previsto, qt_pedido), 0)
     INTO p_quant
     FROM tabela
    WHERE cd_produto = p_cd_produto
      AND TRUNC (dt_pedido) = TO_DATE (p_dat, 'dd/mm/yyyy');

   RETURN p_quant;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN 0;
END;
the main function:
Code: Select all
FUNCTION minha_consulta (dat_inicial IN DATE, dat_final IN DATE)
   RETURN CR_CURSOR IS
   cmd   VARCHAR2 (20000);
   dat   DATE;
   p_cursor cr_cursor;
BEGIN
   cmd := 'select cd_produto';
   dat := TRUNC (dat_inicial);

   WHILE dat <= TRUNC (dat_final) LOOP
      cmd :=
            cmd
         || ', status_produto(cd_produto,'''
         || TO_CHAR (dat, 'dd/mm/yyyy')
         || ''',0)'
         || ', status_produto(cd_produto,'''
         || TO_CHAR (dat, 'dd/mm/yyyy')
         || ''',1)';
      dat := dat + 1;
   END LOOP;
   cmd:=cmd||' from tabela';

   OPEN vcr_cursor FOR cmd;

   RETURN vcr_cursor;

END;
Yes, CR_CURSOR is a generic cursor: type CR_CURSOR IS REF CURSOR;

I hope this helps.
Ricardo Carmo
Location: Maceió-AL

Poston Tue, 26 Feb 2008 3:54 pm

I'vê done it with a fixed number of columns. See example at this link: viewtopic.php?t=1651:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 26 Feb 2008 7:08 pm

Here's a tip for those who is at version 11 g (or higher = P) [url = http//www : . oracle . com/technology/pub/articles/oracle database 11 g-top-features/11 g-pivot . html] Pivot [/url]
rogenaro
Location: Londrina - PR

Rafael O. Genaro


  • See also
    Replies
    Views
    Last Post


          Return to SQL

          Who is online

          Users browsing this forum: No registered users and 5 guests