Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: FETCH FIRST ROW ONLY not working in subquery

    Any ideas why this works:

    SELECT NETINT.NETWORKID
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
    WHERE NETINT.NODEID=8713
    ORDER BY NETWORK.NETMASK
    FETCH FIRST ROW ONLY


    but this does not:

    SELECT HOSTNAME
    FROM MSTRESRC.NETWORKINTERFACES
    WHERE NODEID=8713
    AND NETWORKID=(
    SELECT MSTRESRC.NETWORKINTERFACES.NETWORKID
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
    WHERE NETINT.NODEID=8713
    ORDER BY NETWORK.NETMASK
    FETCH FIRST 1 ROWS ONLY)

    I intend to use the second version to create a column in a bigger query
    where each row of the bigger query would be for per NODE. The net
    is I cannot get FETCH FIRST to work in a subquery...

    Thanks in advance for any help///

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    "Fetch first n rows only" in subquery is a new feature of db2 v.8.1. You are probably using some older version.

    Please specify the version of db2 and fixpack level and the OS version and fixpack level.

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Aug 2003
    Posts
    58
    I am running on both AIX and Win2000 DB2 UDB V7.?
    lslpp on AIX does not tell me much, and the Win version
    just says Version 7.

    I have had to work around the problem by ignoring extra
    rows while scanning the result.

  4. #4
    Join Date
    Aug 2003
    Posts
    58
    Is there another way in DB2 to join a table and get just the
    row with a maximum or minimum column? Lets say I have
    a table of Departments, and a table of Employees which
    have both a Department and a salary. How would I list
    Departments and the highest paid Employee in each Department?

  5. #5
    Join Date
    Mar 2004
    Posts
    448
    First you can find information about your db2 software with db2level command
    You can use where exists, like where exists (select * ....)
    Also I am giving you an example of using inline view

    WITH
    DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
    (SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
    FROM EMPLOYEE OTHERS
    GROUP BY OTHERS.WORKDEPT
    ),
    DINFOMAX AS
    (SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
    SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
    DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
    FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
    WHERE THIS_EMP.JOB = 'SALESREP'
    AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

  6. #6
    Join Date
    Aug 2003
    Posts
    58
    db2level returns the following on Win2000:

    DB21085I Instance "DB2" uses DB2 code release "SQL07025" with level identifier
    "03060105" and informational tokens "DB2 v7.1.0.68", "n020616" and "WR21311".

    I am using a WHERE IN (SELECT ...) but the subselect needs
    to return a MAX value of one row and that's what I can't get to work.
    I don't need the MAX value of a column in a table, I need an
    ID of of the row with the MAX value.

    Your example below demonstrates the WITH clause I have not
    used before, but does not solve the example problem I mention.
    I want to list departments, and for each department row in the
    result I want to have a column which contains the employee
    who is the top paid for that department.

  7. #7
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    How's this? Can this help u?

    Cheers, Bill


    SELECT HOSTNAME
    FROM NETWORKINTERFACES
    WHERE NODEID=8713
    AND NETWORKID=(
    SELECT NETWORKID
    FROM NETWORKINTERFACES AS A
    LEFT OUTER JOIN NETWORKS AS B
    ON B.NETWORKID = A.NETWORKID
    WHERE NETINT.NODEID=8713
    AND B.NETMASK = (
    SELECT MIN(NETMASK)
    FROM NETWORK C
    WHERE C.NETWORKID = B.NETWORKID
    )
    )
    ;

  8. #8
    Join Date
    Aug 2003
    Posts
    58
    Bill,
    Your inner query (when corrected to run on my server)

    SELECT B.NETWORKID
    FROM MSTRESRC.NETWORKINTERFACES AS A
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS B
    ON B.NETWORKID = A.NETWORKID
    WHERE A.NODEID=8713
    AND B.NETMASK = (
    SELECT MIN(NETMASK)
    FROM MSTRESRC.NETWORKS C
    WHERE C.NETWORKID = B.NETWORKID
    )

    produces:

    NETWORKID
    1000
    1001
    1002

    which is 3 results and breaks the outer query which must have one.

    Node 8713 has three network interfaces, each of which has only one
    network associated with it. Your MIN predicate is finding the minimum
    from a set of ONE so it does not really do anything. I need the
    minimum of all three interfaces, Here is a little more data:

    NETWORKID NETMASK
    1000 255.255.254.000
    1001 255.255.255.000
    1002 255.255.255.128

    The inner query needs to return 1000, and without a FETCH FIRST ROW
    ONLY, I don't know how to do it.

  9. #9
    Join Date
    Nov 2003
    Location
    Jakarta, Indonesia
    Posts
    32
    Can u try this out:

    SELECT NETINT.HOSTNAME
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
    WHERE NETINT.NODEID=8713 AND NETWORK.NETMASK=
    (SELECT MIN(NETWORK1.NETMASK)
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT1
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK1 ON NETWORK1.NETWORKID=NETINT1.NETWORKID
    WHERE NETINT1.NODEID=8713)

    Originally posted by wayneb64
    Bill,
    Your inner query (when corrected to run on my server)

    SELECT B.NETWORKID
    FROM MSTRESRC.NETWORKINTERFACES AS A
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS B
    ON B.NETWORKID = A.NETWORKID
    WHERE A.NODEID=8713
    AND B.NETMASK = (
    SELECT MIN(NETMASK)
    FROM MSTRESRC.NETWORKS C
    WHERE C.NETWORKID = B.NETWORKID
    )

    produces:

    NETWORKID
    1000
    1001
    1002

    which is 3 results and breaks the outer query which must have one.

    Node 8713 has three network interfaces, each of which has only one
    network associated with it. Your MIN predicate is finding the minimum
    from a set of ONE so it does not really do anything. I need the
    minimum of all three interfaces, Here is a little more data:

    NETWORKID NETMASK
    1000 255.255.254.000
    1001 255.255.255.000
    1002 255.255.255.128

    The inner query needs to return 1000, and without a FETCH FIRST ROW
    ONLY, I don't know how to do it.
    Regards,
    Adam Ac

  10. #10
    Join Date
    Aug 2003
    Posts
    58
    This works for the data in my example, but it does not insure that
    only one hostname will be returned per node. If a node is on
    two networks that have equal netmasks, both hostnames will
    be returned. This is why I want the where clause in the outer
    query to be on a networkid. My original inner query sorted by
    netmask and in case of a tie, the FETCH FIRST ROW ONLY made
    sure that only one was selected.

  11. #11
    Join Date
    Nov 2003
    Location
    Jakarta, Indonesia
    Posts
    32
    You can just add FETCH FIRST ROW ONLY on the outer query and change the inner query to a INNER JOIN ?

    SELECT NETINT.HOSTNAME
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
    WHERE NETINT.NODEID=8713 AND NETWORK.NETMASK=
    (SELECT MIN(NETWORK1.NETMASK)
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT1
    JOIN MSTRESRC.NETWORKS AS NETWORK1 ON NETWORK1.NETWORKID=NETINT1.NETWORKID
    WHERE NETINT1.NODEID=8713) FETCH FIRST ROW ONLY

    Originally posted by wayneb64
    This works for the data in my example, but it does not insure that
    only one hostname will be returned per node. If a node is on
    two networks that have equal netmasks, both hostnames will
    be returned. This is why I want the where clause in the outer
    query to be on a networkid. My original inner query sorted by
    netmask and in case of a tie, the FETCH FIRST ROW ONLY made
    sure that only one was selected.
    Regards,
    Adam Ac

  12. #12
    Join Date
    Nov 2003
    Location
    Jakarta, Indonesia
    Posts
    32
    And i think you can change the outer query to a INNER JOIN
    cuz i dont see why you need to use LEFT OUTER JOIN.

    Originally posted by adam_ac
    You can just add FETCH FIRST ROW ONLY on the outer query and change the inner query to a INNER JOIN ?

    SELECT NETINT.HOSTNAME
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT
    LEFT OUTER JOIN MSTRESRC.NETWORKS AS NETWORK ON NETWORK.NETWORKID=NETINT.NETWORKID
    WHERE NETINT.NODEID=8713 AND NETWORK.NETMASK=
    (SELECT MIN(NETWORK1.NETMASK)
    FROM MSTRESRC.NETWORKINTERFACES AS NETINT1
    JOIN MSTRESRC.NETWORKS AS NETWORK1 ON NETWORK1.NETWORKID=NETINT1.NETWORKID
    WHERE NETINT1.NODEID=8713) FETCH FIRST ROW ONLY
    Regards,
    Adam Ac

  13. #13
    Join Date
    Aug 2003
    Posts
    58
    The example I am giving has a predicate that grabs only one Node
    with an ID of 8713. The actual production query grabs a bunch
    of Nodes based on other predicates I am not including. My production
    query is returning multiple rows per node and I have to post
    process the results to take only the first one.

    I am gathering other information also not shown here for the Node.
    All I want is a subquery to grab the hostname on the biggest
    network to populate one column of the result and not cause
    mutiple rows per node.

Posting Permissions

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