SubQuery returning Multiple rows

Question

I have got Two Tables

Product (Id, Name, CCode)

Category (CCode, CatName) - No Primary Key

Insert Into ProductNew (DW_Prod_Id, ProdId, ProdName, CC, CName)

Select Dw_Prod_Id.Nextval, Id, Name, CCode,
    (Select CatName
     From Category cc, Product p
     Where  cc.CCode IN p.CatCode
     Group By CatName ) 
From Product;

SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

I am getting the above error Because my SubQuery returns more than one row. I would like to Match the CatCode of each row from Product table to the Category Table so that I can obtain the CatName and then Insert rows into my New Table :)

Raphaël Althaus · Accepted Answer · 2012-05-24 15:33:00Z

1

if product can have only one category :

INSERT INTO ProdcutNew (DW_Prod_Id, ProdId, ProdName, CC, CName)

(SELECT Dw_Prod_Id.Nextval, p.Id, p.Name, cc.CCode, cc.CName
FROM Product p
INNER JOIN Category cc on p.CatCode = cc.CCode)

And you can correct your table name

ProdcutNew

to ProductNew ;)

EDIT :

But if, as @Gordon Linoff pointed, you have duplicates CCode, this won't work.

If you don't want a primary key on Category table, add at least a unique constraint (you'll have to clean your datas first)

ALTER TABLE Category ADD CONSTRAINT Unique_code UNIQUE(CCode);

EDIT 2 :

But the proper way would be :

Add an Id in Category as PK, and use it as Category_ID FK in Product (if CCode can change) With the unique constraint on CCode.

edited May 24, 2012 at 15:33

answered May 24, 2012 at 15:26

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1279970 Over a year ago

Yup changed my table name and let me try this out ;)

Gordon Linoff · Accepted Answer · 2012-05-24 15:28:23Z

0

You appear to have dulicates in your category table; otherwise a simple join would suffice:

select p.*, c.ccode
from Category c join
     Product p
     on c.ccode = p.catcode

To choose one category arbitrarily, do something like:

select p.*, c.ccode
from (select c.*
      from (select c.*, row_number() over (partition by c.ccode order by c.ccode) as seqnum
            from Category c
           ) c
       where seqnum = 1
      ) c join
     Product p
     on c.ccode = p.catcode

answered May 24, 2012 at 15:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

user1279970 Over a year ago

If I want to put Where rowid Not In (Select Source_RowId From A3_Error_Event Where Filter_Id = 1 ); I get an error - SQL Error: ORA-00918: column ambiguously defined

Collectives™ on Stack Overflow

SubQuery returning Multiple rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related