Results 1 to 4 of 4

Thread: db2 subquery

  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Exclamation Unanswered: db2 subquery

    i'm having problems executing a db2 query in my IBM DB2 command center v7.

    i'm trying to execute a select statement with a subquery. i constructed it like this:

    SELECT DISTINCT
    COMPUTER_SYS_ID,
    (Select MANUFACTURER from PROCESSOR_DATA_VIEW where PROCESSOR_NUM=1
    AND COMPUTER_SYS_ID = T1.COMPUTER_SYS_ID FETCH FIRST ROW ONLY)
    AS PROCESSOR1
    FROM
    PROCESSOR_DATA_VIEW as T1
    GROUP BY
    COMPUTER_SYS_ID

    ---> but it doesn't work. the equivalent statement (that works) of this in SQL is:

    SELECT DISTINCT COMP_SYS_ID,
    (SELECT TOP1 MANUFACTURER + ' ' + LTRIM(STR(MAX_SPEED))
    FROM PROCESSOR_DATA_VIEW WHERE PROCESSOR_NUM=1 AND COMP_SYS_ID=T1.COMP_SYS_ID) AS PROCESSOR1
    FROM PROCESSOR_DATA_VIEW T1
    GROUP BY COMP_SYS_ID


    What is wrong with my db2 query? pls advise

  2. #2
    Join Date
    Oct 2004
    Posts
    3
    What kind of error do you get?
    Helle

  3. #3
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    SELECT DISTINCT
    T1.COMPUTER_SYS_ID,
    SUBSET.MANUFACTURER

    FROM
    PROCESSOR_DATA_VIEW T1,

    (SELECT
    T2.MANUFACTURER AS MANU
    T2.PROCESSOR_NUM AS PNUM

    FROM
    PROCESSOR_DATA_VIEW T2) AS SUBSET

    WHERE
    SUBSET.PNUM = 1

    GROUP BY
    T1.PROCESSOR_DATA_VIEW

    ---

    However, I don't understand why you build your statement like this ...

    ---

    Regards

    Filip Poverud

  4. #4
    Join Date
    Oct 2004
    Posts
    2

    Exclamation

    To: Filip Poverud

    I copied the query that you posted but i got an error statement that says:

    DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL0206N "T1.PROCESSOR_DATA_VIEW" is not valid in the context where it is used. SQLSTATE=42703

    What do you recommend that I alter in my statement. my dilemma is that i'm only extracting from one table and the thing that i want to do is that instead of having a table of multiple entries, i want it to only have one so what i need to do is add rows. ex. COMP1 has 4 mac addresses. instead of the table having 5 rows of COMP1 with different mac addresses, i want it to only have one row with additional 4 columns...

    INSTEAD OF:
    COMP1 MAC1
    COMP1 MAC2
    COMP1 MAC3
    COMP1 MAC4

    I want it to be
    COMP1 MAC1 MAC2 MAC3 MAC4


    To Helle:

    when i executed the query that i posted here, this is the error statement that i get:

    DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "FETCH" was found following "= T1.COMPUTER_SYS_ID". Expected tokens may include: ")". SQLSTATE=42601

    What is the equivalent of Top 1 (from SQL) to db2?

Posting Permissions

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