?
Solved

This is my function, it is not working

Posted on 2011-03-03
8
Medium Priority
?
302 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 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