Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unhappy Unanswered: pagination procedure not working on oracle 7

    Hello

    I have this problem, I need to paginate the answer from a request and order it before pagination. I came out with this procedure:

    CREATE OR REPLACE PROCEDURE pcylpup7 (cv IN OUT pcyqatp.GenericCurTyp,USINE_OID number,debut number,fin number) AS
    BEGIN
    OPEN cv FOR
    SELECT uepoid as UEP_OID, title as UEP_TITLE, secteuroid as UEP_SECTEUR, document as UEP_DOCUMENT, tourterrain as UEP_TOURTERRAIN
    FROM (
    SELECT uepoid,title,secteuroid,document,tourterrain,ROWNU M AS ROW_NUMBER FROM
    (
    SELECT uepoid,title,secteuroid,document,tourterrain
    FROM pcinfo.pcyqtuep
    where secteuroid in (select secteuroid from pcyqtse where usineoid=USINE_OID)
    ORDER BY secteuroid,title
    )
    )
    WHERE ROW_NUMBER BETWEEN debut AND fin;

    END;
    .
    /


    This procedure works ok with oracle 8i which is the database I use but now I need to make it work under Oracle 7 (the final server that will be used). The problem is that the procedure doesn't compile under oracle 7, if I take out the line saying:
    ORDER BY secteuroid,title
    the procedure compiles, but then the results are not ordered...

    Can somebody tell me how can I change this procedure to make it work under oracle7 ?

    Thanks a lot

    Alvaro

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Re: pagination procedure not working on oracle 7

    Originally posted by cuzuco
    Hello

    I have this problem, I need to paginate the answer from a request and order it before pagination. I came out with this procedure:

    CREATE OR REPLACE PROCEDURE pcylpup7 (cv IN OUT pcyqatp.GenericCurTyp,USINE_OID number,debut number,fin number) AS
    BEGIN
    OPEN cv FOR
    SELECT uepoid as UEP_OID, title as UEP_TITLE, secteuroid as UEP_SECTEUR, document as UEP_DOCUMENT, tourterrain as UEP_TOURTERRAIN
    FROM (
    SELECT uepoid,title,secteuroid,document,tourterrain,ROWNU M AS ROW_NUMBER FROM
    (
    SELECT uepoid,title,secteuroid,document,tourterrain
    FROM pcinfo.pcyqtuep
    where secteuroid in (select secteuroid from pcyqtse where usineoid=USINE_OID)
    ORDER BY secteuroid,title
    )
    )
    WHERE ROW_NUMBER BETWEEN debut AND fin;

    END;
    .
    /


    This procedure works ok with oracle 8i which is the database I use but now I need to make it work under Oracle 7 (the final server that will be used). The problem is that the procedure doesn't compile under oracle 7, if I take out the line saying:
    ORDER BY secteuroid,title
    the procedure compiles, but then the results are not ordered...

    Can somebody tell me how can I change this procedure to make it work under oracle7 ?

    Thanks a lot

    Alvaro


    I believe the code should still work. What is the compiler error you are getting? It could just be that the 2 data bases are not identical (different columns).

  3. #3
    Join Date
    Dec 2003
    Posts
    8

    Re: pagination procedure not working on oracle 7

    Originally posted by Todd Barkus
    I believe the code should still work. What is the compiler error you are getting? It could just be that the 2 data bases are not identical (different columns).
    the error is saying that the procedure has encounter an "ORDER BY" instead of "( " and other things that are proposed.

    the tables are the same, I made them myself. The problem comes from the order by, as I said when I take this line out it compiles without problem.

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Re: pagination procedure not working on oracle 7

    Originally posted by cuzuco
    Hello

    I have this problem, I need to paginate the answer from a request and order it before pagination. I came out with this procedure:

    CREATE OR REPLACE PROCEDURE pcylpup7 (cv IN OUT pcyqatp.GenericCurTyp,USINE_OID number,debut number,fin number) AS
    BEGIN
    OPEN cv FOR
    SELECT uepoid as UEP_OID, title as UEP_TITLE, secteuroid as UEP_SECTEUR, document as UEP_DOCUMENT, tourterrain as UEP_TOURTERRAIN
    FROM (
    SELECT uepoid,title,secteuroid,document,tourterrain,ROWNU M AS ROW_NUMBER FROM
    (
    SELECT uepoid,title,secteuroid,document,tourterrain
    FROM pcinfo.pcyqtuep
    where secteuroid in (select secteuroid from pcyqtse where usineoid=USINE_OID)
    ORDER BY secteuroid,title
    )
    )
    WHERE ROW_NUMBER BETWEEN debut AND fin;

    END;
    .
    /


    This procedure works ok with oracle 8i which is the database I use but now I need to make it work under Oracle 7 (the final server that will be used). The problem is that the procedure doesn't compile under oracle 7, if I take out the line saying:
    ORDER BY secteuroid,title
    the procedure compiles, but then the results are not ordered...

    Can somebody tell me how can I change this procedure to make it work under oracle7 ?

    Thanks a lot

    Alvaro
    I just looked closer at your code. I see you have sub-selects in sub-selects. I thought that was once an issue in 7 but I am not sure. I think what might be giving you a problem is that you are "ordering the results of a sub-select instead of your final select. Perhaps 8 is able to do that but not 7. Does the sub-select really need to be ordered? To order the outer select you might need to use ORDER BY UEP_SECTEUR, UEP_TITLE or ORDER BY 3, 2

    Give it a whirl. Good luck

  5. #5
    Join Date
    Dec 2003
    Posts
    8

    Re: pagination procedure not working on oracle 7

    Originally posted by Todd Barkus
    I just looked closer at your code. I see you have sub-selects in sub-selects. I thought that was once an issue in 7 but I am not sure. I think what might be giving you a problem is that you are "ordering the results of a sub-select instead of your final select. Perhaps 8 is able to do that but not 7. Does the sub-select really need to be ordered? To order the outer select you might need to use ORDER BY UEP_SECTEUR, UEP_TITLE or ORDER BY 3, 2

    Give it a whirl. Good luck

    Well the problem comes definitively from the sub-select I have being googling about it and oracle 7 can't order on sub-selects. the problem is that the order needs to be done there, otherwise it will be ordering only the page that is being visualised and not the whole stuff (which is not what I want).
    I need first to order the results, and then to do the pagination.
    Any ideas about how to do this under oracle 7?

    Thanks...

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I have to run to a meeting. I will think on it and check back. Might be tomorrow.

  7. #7
    Join Date
    Dec 2003
    Posts
    8
    Originally posted by Todd Barkus
    I have to run to a meeting. I will think on it and check back. Might be tomorrow.

    Well thanks anyway, if i find a way to do it I'll post it here also

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •