Oracle Select Subquery Return Error

Question

I have a database with following definition:

--------------------------------------------------------
--  DDL for Table MAKAN
--------------------------------------------------------

CREATE TABLE "MAMAT"."MAKAN" 
(   "ID" NVARCHAR2(50), 
    "JUDUL_BUKU" NVARCHAR2(45), 
    "NO_ISBN" NVARCHAR2(30), 
    "DT_TERBIT" DATE, 
    "JENIS" NUMBER(1,0), 
    "TEBAL" NUMBER(6,0), 
    "PENGARANG" NVARCHAR2(50)
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into MAMAT.MAKAN
SET DEFINE OFF;
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('3F26E5EA2A474778855B28479E01D13C','Di Bawah Lindungan Kabah','3232emasdsdsa',to_date('20/05/2016','DD/MM/RRRR'),1,234,'Mohammad Fajar');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('D0BC61589AB9465AA7D77A2EAE3A9765','JANGAN kau bunuh Pacarmu','dadsadsaddssada',to_date('20/05/2016','DD/MM/RRRR'),1,234,'Mohammad Fajar');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('CE5C935BEB5A4F3CA7627E7C60E5B60F','Lempar Batu Sembunyi Tangan','099999ddddddd',to_date('20/05/2016','DD/MM/RRRR'),2,22,'Mohammad Fajar');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('9347A16BF25D4A85B12629856A9B64C1','Kejar Daku Kau kutangkap','dsdsd',to_date('20/05/2016','DD/MM/RRRR'),1,22,'Mohammad Fajar');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('DD06FD15C06343358B213A4D6EB95D77','Lempar Batu Sembunyi Tangan','099999ddddddd',to_date('20/05/2016','DD/MM/RRRR'),2,22,'Rivai');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('C6199285FDFC4A2FAD93E7E9EC3A339A','Kejar Daku Kau kutangkap','dsdsd',to_date('20/05/2016','DD/MM/RRRR'),2,22,'Rivai');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('C53D8A80726F4AD9B91D43CD60F68332','Kejar Daku Kau kutangkap','dsdsd',to_date('20/05/2016','DD/MM/RRRR'),2,22,'Rivai');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('BE7072DA0BCD4D3FA8A96CAFF38EC0CF','Kejar Daku Kau kutangkap','dsdsd1',to_date('20/05/2016','DD/MM/RRRR'),2,22,'Rivai');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('C81A724896F146F78159BF14F1F1E72B','Kejar Daku Kau kutangkap','mami1',to_date('20/05/2016','DD/MM/RRRR'),3,26,'Jubaidah');
Insert into MAMAT.MAKAN (ID,JUDUL_BUKU,NO_ISBN,DT_TERBIT,JENIS,TEBAL,PENGARANG) values ('0A61A74D4F4941DEBACB134CB637B608','Kejar Daku Kau kutangkap','mami1',to_date('20/05/2016','DD/MM/RRRR'),3,26,'Jubaidah');

And I run following subquery to retrieve some data:

Select  Ab.Jenis Jn , Ab.Pengarang 
,
(
Select Tebal  From Mamat.Makan Mt Where Mt.Pengarang = Ab.Pengarang 
) 
gg 
From Mamat.Makan Ab
where Ab.Pengarang = 'Mohammad Fajar'   or Ab.Pengarang = 'Rivai' ;

And getting error:

ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

Sorry I dont know detail implementation of select sub-query. And I have try to search over internet to get the explanation. And I become frustrate and stuck too long to solve this problem. Anyone can help me to solve this problem, I will appreciate.

please rephrase my question if not well phrased, I'am not native english speaker. — Mohammad Fajar
– Mohammad Fajar, Commented May 20, 2016 at 11:07

SSD · Accepted Answer · 2016-05-20 15:39:02Z

1

This error happens when your subquery returns more than one row. The subquery itself acts as a column value to main query. what you should do is making sure that subquery returns just one row by adding or modifying conditions

answered May 20, 2016 at 15:39

SSD

3691 gold badge3 silver badges15 bronze badges

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

Comments

collapsar · Accepted Answer · 2016-05-20 11:22:16Z

Your subquery does not make sense: The information that you retrieve can already be made available through the main query:

SELECT Ab.Jenis     Jn
     , Ab.Pengarang 
     , Ab.Tebal     gg
  FROM Mamat.Makan Ab
 WHERE Ab.Pengarang IN ( 'Mohammad Fajar', 'Rivai' )
     ;

However, if you want to present all possible values of Tebal for each distinct Pengarang realization, you may use the LISTAGG function ( note that Jenis needs to be subjected to the list aggregation,too, since its values are not unique within a group):

  SELECT LISTAGG ( Ab.Jenis, ', ' ) WITHIN GROUP ( ORDER BY Ab.Jenis )  Jn
       , Ab.Pengarang 
       , LISTAGG ( Ab.Tebal, ', ' ) WITHIN GROUP ( ORDER BY Ab.Jenis )  gg
    FROM Mamat.Makan Ab
   WHERE Ab.Pengarang IN ( 'Mohammad Fajar', 'Rivai' )
GROUP BY Ab.Pengarang
     ;

Collectives™ on Stack Overflow

Oracle Select Subquery Return Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related