The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row

Question

I have a problem about my query and i dont know what to do about it.Here is my query.and error i take.

select prd.product_id,prd.prd_name ,prd.prd_longname ,prd.prd_brand ,prd.prd_picture ,prd.market_comment ,prd.categ ,prd.status_id ,prd.status ,prd.active_stock ,prd.slot_date ,prd.currency ,prd.selling_price ,prd.old_price ,prd.type_of_sell ,prd.catalog_id ,prd.catalog_name ,prd.demo ,prd.demo_id, 
    (select coalesce(count(prd_attribute_id),0) from PRD_ATTRIBUTE where status_id = 1 and product_id = prd.product_id and batch_code <> '0000') as ATTR_CNT  ,
    (select prd_attribute_id from PRD_ATTRIBUTE where product_id = prd.product_id and batch_code = '0000' and status_id = 1),
    (select categ_url from DBNAME.PRD_CATEGORY 
where parameter_id = prd.categ_id)||'/'||    (select prd_url from DBNAME.PRODUCT_URL where product_id = prd.product_id) as CATEG_URL 
from TEMP_WEB_PRD prd 
order by slotdate desc
fetch first 12 rows only

Error:

 [IBM][CLI Driver][DB2/AIX64] SQL0811N  The result of a scalar fullselect, SELECT INTO    statement, or VALUES INTO statement is more than one row.  SQLSTATE=21000

Solution 1

The error message is pretty self-explanatory. One of your sub-selects is returning more than one row back, and the database doesn't know how to handle that. I'm guessing your database is DB2 on Linux/Unix/Windows, based on the error message, so here's the Info Center article on your error.

Solution 2

Yes in short it's due to you are using "=" but duplicate rows returned from sub-select statement.

Suppose you have a simple table: create table T1 (ID int not null primary key, FID int);

The following statement may return SQL0811N if FID column references the same ID values multiple times.

db2 "select id from T1 where ID=(select fid from T1)"

ID

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000

The following statement will run successfully: db2 "select id from T1 where ID IN (select fid from T1)"