Results 1 to 12 of 12
  1. #1
    Join Date
    May 2008
    Posts
    6

    Unanswered: row_number not working when using multiple joins [db2]

    Hello,
    I have a complex join the result of which I am going to insert in another table. I would like to number the rows from 1 to n as they are fetched by my sql query. I tried using the row_number to no avail. Can anyone please help?

    Here is the query:

    Code:
    SELECT
    accounts.YTAXID AS BVNIF,
    accounts.YNA1 AS BVNAME,
    transactions.TTDATE || row_number() over () AS BVORNO
    FROM
    QS36F.GMITH1F1 transactions
    INNER JOIN
    QS36F.WITCMFF1 accounts
    ON
    accounts.YFIRM = transactions.TFIRM
    AND accounts.YOFFIC = transactions.TOFFIC
    AND accounts.YACCT = transactions.TACCT
    INNER JOIN
    QS36F.GMIPPML1 pricing
    ON
    pricing.SCUSIP = transactions.TCUSIP
    AND pricing.SCUSP2 = transactions.TCUSP2
    LEFT OUTER JOIN
    OUTPUTF.MADBOSL701 isins
    ON
    isins.MIEXCH = transactions.TEXCH
    AND isins.MIFC = transactions.TFC
    AND isins.MISYMB = transactions.TSYMBL
    AND isins.MIEXCH = transactions.TCTYM
    AND isins.MIPTCL = transactions.TSUBTY
    AND isins.MISTR2 = transactions.TSTRIK
    WHERE
    0 = 0
    AND transactions.TFIRM || transactions.TOFFIC || transactions.TACCT NOT IN
    (
    SELECT
    excluded_accounts.MAFIRM|| excluded_accounts.MAOFFC ||excluded_accounts.MAACCT
    FROM
    OUTPUTF.MADEXAL10 excluded_accounts
    )
    AND transactions.TFIRM = 'I'
    Can anyone tell me why it says:

    "an olap function is not supported for this query"?

    Any clue welcome,

    Julien.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by balteo
    SELECT
    accounts.YTAXID AS BVNIF,
    accounts.YNA1 AS BVNAME,
    transactions.TTDATE || row_number() over () AS BVORNO
    FROM ...
    Have you tried with "," instead of "||" ?
    Afaik, row_number() is numeric and can thus not be concatenated.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    May 2008
    Posts
    6
    thanks for the reply.
    Unfortunately it has nothing to do with concatenation as I have tried as follows:
    Code:
     row_number() over () AS BVORNO
    without the concatenation and it gives me the same error number.
    Julien.

  4. #4
    Join Date
    May 2008
    Posts
    6
    Any other idea please?

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Did you try
    Code:
    SELECT accounts.YTAXID AS BVNIF,
           accounts.YNA1 AS BVNAME,
           transactions.TTDATE,
           row_number() over (order by transactions.TTDATE) AS BVORNO
    FROM   QS36F.GMITH1F1 transactions
           INNER JOIN QS36F.WITCMFF1 accounts
           ON accounts.YFIRM = transactions.TFIRM
              AND accounts.YOFFIC = transactions.TOFFIC
              AND accounts.YACCT = transactions.TACCT
           INNER JOIN QS36F.GMIPPML1 pricing
           ON pricing.SCUSIP = transactions.TCUSIP
              AND pricing.SCUSP2 = transactions.TCUSP2
           LEFT OUTER JOIN OUTPUTF.MADBOSL701 isins
           ON isins.MIEXCH = transactions.TEXCH
              AND isins.MIFC = transactions.TFC
              AND isins.MISYMB = transactions.TSYMBL
              AND isins.MIEXCH = transactions.TCTYM
              AND isins.MIPTCL = transactions.TSUBTY
              AND isins.MISTR2 = transactions.TSTRIK
    WHERE  NOT EXISTS (
      SELECT 1
      FROM OUTPUTF.MADEXAL10
      WHERE MAFIRM = transactions.TFIRM
        AND MAOFFC = transactions.TOFFIC
        AND MAACCT = transactions.TACCT
      )
      AND  transactions.TFIRM = 'I'
    (Note the "order by" added to "row_number()", but also the replacement of "NOT IN" with concatenation by "NOT EXISTS": this could potentially be a million times faster ...)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    May 2008
    Posts
    6
    thanks,
    the
    Code:
    not exists
    works a charm but the change to the row_number arguments i.e.
    Code:
     row_number() over (order by transactions.TTDATE) AS BVORNO
    returns the same error code i.e. sqlstate 42999 and sql0255
    I suspect it is a problem with the joins but I am not sure. What do you think?
    Julien.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by balteo
    I suspect it is a problem with the joins but I am not sure. What do you think?
    No idea. The original query looks syntactically correct to me. I just tried to execute (a simplifiied version of) it on my own database, and it worked. (DB2 9.1 ExpressC for MSwin32.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    May 2008
    Posts
    6
    thanks anyway, I really appreciate your giving me some of your time.
    all the best
    J.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    > returns the same error code i.e. sqlstate 42999 and sql0255
    What is the platform and version/release of DB2?
    I couldn't find sqlstate 42999 nor sql0255 in "Message Reference, Volume 2" for LUW 9.1 and "Meesages and Codes" for z/OS Version 8.
    I found them in "SQL messages and codes" for iSeries V5R4.

    DB2 for iSeries V5R3 or earlier desn't support OLAP specifications.
    V5R4 supports OLAP. But there are some restrictions described in "SQL Reference".

    An OLAP specification is not allowed if the query specifies:
    - lateral correlation,
    - a sort sequence,
    - an operation that requires CCSID conversion,
    - a UTF-8 or UTF-16 argument in a CHARACTER_LENGTH, POSITION, or SUBSTRING scalar function,
    - a distributed table,
    - a table with a read trigger, or
    - a logical file built over multiple physical file members.
    Last edited by tonkuma; 05-31-08 at 14:24.

  10. #10
    Join Date
    May 2008
    Posts
    6
    Tonkuma,
    Thanks a lot for this detailed reply. Yes I do use db2 for iseries and I believed it did not matter which platform I used. As far as my query is concerned it must be a problem with one of the restriction you listed. Do you know any workaround?
    Julien.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry. I have no idea.
    Most of my knowledge about DB2 iSeries came from Manuals.

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    DB2 comes with a predefined UDF CTR() that you can use here. If you use it in a SELECT, it will number all the rows starting from 1 up to ROW_COUNT. It is not active after a standard DB2 install though. You have to install it explicitly.

    The SQL script then becomes :
    Code:
    SELECT accounts.YTAXID AS BVNIF,
           accounts.YNA1 AS BVNAME,
           transactions.TTDATE,
           ctr() AS BVORNO
    FROM   QS36F.GMITH1F1 transactions
           INNER JOIN QS36F.WITCMFF1 accounts
           ON accounts.YFIRM = transactions.TFIRM
              AND accounts.YOFFIC = transactions.TOFFIC
              AND accounts.YACCT = transactions.TACCT
           INNER JOIN QS36F.GMIPPML1 pricing
           ON pricing.SCUSIP = transactions.TCUSIP
              AND pricing.SCUSP2 = transactions.TCUSP2
           LEFT OUTER JOIN OUTPUTF.MADBOSL701 isins
           ON isins.MIEXCH = transactions.TEXCH
              AND isins.MIFC = transactions.TFC
              AND isins.MISYMB = transactions.TSYMBL
              AND isins.MIEXCH = transactions.TCTYM
              AND isins.MIPTCL = transactions.TSUBTY
              AND isins.MISTR2 = transactions.TSTRIK
    WHERE  NOT EXISTS (
      SELECT 1
      FROM OUTPUTF.MADEXAL10
      WHERE MAFIRM = transactions.TFIRM
        AND MAOFFC = transactions.TOFFIC
        AND MAACCT = transactions.TACCT
      )
      AND  transactions.TFIRM = 'I'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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