?
Solved

This is my function, it is not working

Posted on 2011-03-03
8
Medium Priority
?
301 Views
Last Modified: 2012-05-11
i have this query
INSERT INTO   SEGUIMIENTO
          (
              NUMEROINSTRUCCION,
            ...
              ,SECUENCIA
              ,OBSERVACION
              ,CODIGOROL
          )
select
              CodSolicitud,
            ...
              NumSecuencia,
              null,
              30
from tmp_SolicitudFlujo;

I replace the column by a corresponding NumSecuencia starting at 1, ie, if the numeroinstruccion this 6 times, then the NumSecuencia should go from 1 to 6. See graphic


This is my function, but it works well
CREATE OR REPLACE FUNCTION fn_SECUENCIASEGUIMIENTO(
                            pic_NUMEROINSTRUCCION IN INSTRUCCIONOPERATIVA.NUMEROINSTRUCCION%TYPE
                          )
    RETURN INTEGER
  IS
      v_result INTEGER;
  BEGIN

    SELECT NVL(COUNT(*),0)
    INTO v_result
    FROM SEGUIMIENTO S
    WHERE S.NUMEROINSTRUCCION = pic_NUMEROINSTRUCCION;

    IF v_result  = 0 THEN
       v_result  := 1;
    ELSE
       v_result  := v_result + 1;
    END IF;

    IF v_result IS NULL THEN
          RETURN (NULL);
     ELSE
          RETURN (v_result);
     END IF;

  EXCEPTION
      WHEN OTHERS THEN
          RAISE;

END fn_SECUENCIASEGUIMIENTO;
instruccionSECUENCIA.jpg
useSECUENCE.jpg
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 668 total points
ID: 35030829
what is the picture?  Is that input or expected output?

if input, what is the expected output?
If output, what is the input?

Also,  how are you expecting to use this function?  You won't be able to do it with a select statement.

If you simply want sequential numbers, why not use ROWNUM?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35030881
I have to assume you are looking for gap-less numbers continuing on from the total number of rows in the database.

Even if you could use it in a insert/select like you want, that function would not guarantee unique or gapless.

Go with an Oracle sequence.

Also in the function as you have it, the following code doesn't really do anything:

    IF v_result IS NULL THEN
          RETURN (NULL);
     ELSE
          RETURN (v_result);
     END IF;

0
 

Author Comment

by:enrique_aeo
ID: 35031021
VIEW FILE
and use the rownum in the insert into?

sequenceROWNUM.jpg
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35031056
sdstuber has already stated you cannot use that function the way you want to:  in an insert/select.

The reason:  The function gets called only once when the statement is parsed and never again.

It's the same as:
select sysdate from dba_objects;

No matter how long it takes to select all the objects, the sysdate value stays the same.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1332 total points
ID: 35031094
try something like this using a count and getting rid of your function all together.

Note: It still might not ensure unique numbers.

INSERT INTO   SEGUIMIENTO
          (
...
          )
select
(count(1) over() + rownum)
...
from tmp_SolicitudFlujo;

0
 

Author Comment

by:enrique_aeo
ID: 35031106
RANK() OVER (PARTITION BY CodSolicitud ORDER BY fechorainicio)
0
 

Author Comment

by:enrique_aeo
ID: 35031129
please, confirm
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1332 total points
ID: 35031158
>>RANK() OVER (PARTITION BY CodSolicitud ORDER BY fechorainicio)

That should work if you want to start over with 1 every time.  It that's the case, rownum will work.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question