Results 1 to 6 of 6

Thread: Combining query with upper row_number

  1. #1
    Join Date
    Jan 2010
    Posts
    339

    Combining query with upper row_number

    This is the last time I worked on a db2 database. I want to say that I am no expert in this kind of DBMS, but recently I used to develop a site in php.

    For the purpose of the site, I make a request via a search form with a pagination on a web page. The goal is to return the results of a search results with pagination whether to have page numbering.

    To do so I make a motion that resembles the kind of model:
    Code:
    SELECT * FROM (
    SELECT rownumber() OVER (ORDER BY ID) AS ID_NEXT, NAME FROM MYTABLE WHERE NAME LIKE '%Test%' OR UPPER(NAME) LIKE '%TEST%'
    ) AS TABLE WHERE ROW_NEXT BETWEEN 1 AND 15
    In this sample query that I present, I use the function rownumber to simplify the results returned by the query based paging which I find myself on the screen of my site. And so, I use the UPPER function in order to limit the damage of uppercase, lowercase for the search that is integrated into my web page.

    However, in this post or this forum during the execution of my application it fails to run. If I remove the upper function, the query returns results to me. From where I said that to a couple upper rownumber can run.

    Hence my question. Is there a possibility of linking with an upper rownumber performing a different query or are there an equivalent in db2 could meet my needs?

  2. #2
    Join Date
    Oct 2005
    Posts
    2,393

    Re: Combining query with upper row_number

    Try:
    Code:
    ... FROM MyTable WHERE UPPER(NAME) LIKE '% TEST%')
    I think we also need an additional level of nesting just for UPPER OLAP function if used.

  3. #3
    Join Date
    Jan 2010
    Posts
    339

    Re: Combining query with upper row_number

    Thank you for your reply, but I think I didn't understand.

    In fact the sample query that I presented is not the actual query, I'm trying to effect but one example of the sort. But I still try to rectify it according to your response but still encountered the same trouble.

    For info I am developing in Zend Studio and I test my queries through it via a request module offered by that product.

    Here are the error codes returned by it when making my request. Hoping that it might enlighten you in trouble I encounter:

    AMIDB2 -> [SQL0255] Incompatibility with the request. Cause. . . . . : The reason code is 6: - Code 1 - scalar subqueries and the lateral correlation from a nested table expression are not allowed with distributed files. - Code 2 - An error occurred while using a shared temporary file. - Code 3 - EXCEPT or INTERSECT is not supported for this query. - Code 4 - The reference sequence is not supported with distributed files. - Code 5 - An expression recursive common table is not compatible with this request. - Code 6 - An OLAP function is not compatible with this request. - Code 7 - ORDER OF A function is not compatible with this request. - Code 8 - A statement FULLSELECT scalar is not compatible with this request. - Code 9 - A distributed file is processed in a job to multiple execution units, or it is not the initial implementation unit. What to do. . . : A list of corrective actions: - Code 1: Modify the query so it does not use subqueries or scalar correlation from a nested table expression. - Code 2: For more information, refer to previous messages. - Code 3: Remove EXCEPT or INTERSECT query. - Code 4: Remove the reference sequence of the query. - Code 5: Remove the term recursive common table of the query. - Code 6: Remove the function of the OLAP query. - Code 7: Remove the ORDER function of the query. - Code 8: Enter the query again without instruction FULLSELECT. - Code 9: Do not use multiple threads to execute the query.

  4. #4
    Join Date
    Nov 2008
    Posts
    1,054

    Re: Combining query with upper row_number

    Your application does not pass on my db2, because it contains errors. I could get a result with:
    Code:
    SELECT * FROM (
     SELECT rownumber() OVER () AS ID_NEXT, NAME
     FROM MYTABLE
     WHERE NAME LIKE '%Test%' OR UPPER(NAME) LIKE '%TEST%'
     ORDER BY ID_NEXT
    ) AS TABLE WHERE ID_NEXT BETWEEN 1 AND 15

  5. #5
    Join Date
    Jan 2010
    Posts
    339

    Re: Combining query with upper row_number

    I just tried the answer that you have indicated, but unfortunately the result is same and however I notice that I forget to say something about my query. Indeed it is based on different elements from multiple tables. Items that I do not think is responsible for my execution errors but I think I can say that yes, when I rephrase my query:
    Code:
    SELECT * FROM (
     SELECT rownumber() OVER (ORDER BY ID) AS ID_NEXT, ID, NAME, PRENAME
     FROM MYTABLE1, MYTABLE2
     WHERE NAME LIKE '%Test%' OR UPPER(NAME) LIKE '%TEST%'
    ) AS TABLE WHERE ID_NEXT BETWEEN 1 AND 15

  6. #6
    Join Date
    May 2008
    Posts
    685

    Re: Combining query with upper row_number

    Currently working on a version AS400 V5R4 ,I have had no desire to use the functions UPPER and ROW_NUMBER ().

    I put in red or I think there is a syntax error in code

    Code:
    SELECT * FROM (
     SELECT row_number() OVER (ORDER BY ID) AS ID_NEXT, ID, NAME, PRENAME
     FROM MYTABLE1, MYTABLE2
     WHERE NAME LIKE '%Test%' OR UPPER(NAME) LIKE '%TEST%'
    ) AS TABLE WHERE ID_NEXT BETWEEN 1 AND 15
    To see if the problem is there ....

Similar Threads

  1. Combining VPN with JonDo
    By ittiandro in forum Networking & Security
    Replies: 1
    Last Post: 08-06-2011, 03:54 AM
  2. 955 Cannot Go upper than 16X
    By mich43 in forum Overclocking & Computer Modification
    Replies: 5
    Last Post: 18-12-2010, 10:15 AM
  3. Combining SSH and Cron
    By void in forum Software Development
    Replies: 3
    Last Post: 06-08-2010, 01:44 AM
  4. Turn on MySQL query cache to speed up query performance
    By DMA2Superman in forum Software Development
    Replies: 3
    Last Post: 07-07-2009, 10:26 AM
  5. SQL - bp insert special characters with UPPER
    By ADISH in forum Software Development
    Replies: 4
    Last Post: 27-10-2008, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,620,764.66360 seconds with 17 queries