CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle-sqldeveloper
user1279970
user1279970

Reputation:

SubQuery returning Multiple rows

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 :)

Upvotes: 0

Views: 5841

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 0

Related Questions