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

What kind of error do you get?
Helle

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

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?