CODEWITHSUNDEEP
sqloracle-databasesubquery
TDL
TDL

Reputation: 126

Solving "single-row subquery returns more than one row" error in Oracle SQL

Given with the query below

Select COLUMN_ID,  (Select 
CASE COLUMN_ID
WHEN 4 THEN 'WEIGHT'
WHEN 6 THEN 'CARGO_LENGTH'
WHEN 7 THEN 'WIDTH'
WHEN 8 THEN 'HEIGHT'
END 
GROOVE
FROM ALL_TAB_COLS where TABLE_NAME = 'TBL_CARGO')
FROM ALL_TAB_COLS where COLUMN_ID IN(4,6,7,8)

I like to get only non blank columns. I'm expecting the output 4 6 7 8 displayed per field. How do I do that?

Upvotes: 2

Views: 203

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

Try this

Select COLUMN_ID, 
CASE COLUMN_ID
WHEN 4 THEN 'WEIGHT'
WHEN 6 THEN 'CARGO_LENGTH'
WHEN 7 THEN 'WIDTH'
WHEN 8 THEN 'HEIGHT'
END 
GROOVE
FROM ALL_TAB_COLS where TABLE_NAME = 'TBL_CARGO'
and COLUMN_ID IN(4,6,7,8)

Upvotes: 0

jarlh
jarlh

Reputation: 44766

No need for a sub-select, just add the CASE expression. Something like this perhaps?

Select COLUMN_ID,  
       CASE COLUMN_ID WHEN 4 THEN 'WEIGHT'
                      WHEN 6 THEN 'CARGO_LENGTH'
                      WHEN 7 THEN 'WIDTH'
                      WHEN 8 THEN 'HEIGHT'
       END  GROOVE
FROM ALL_TAB_COLS
where COLUMN_ID IN(4,6,7,8)

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

All that sub-query and in-line view could be done in single query:

Using CASE expression(verbose and easy to understand):

SELECT COLUMN_ID,
  CASE COLUMN_ID
    WHEN 4
    THEN 'WEIGHT'
    WHEN 6
    THEN 'CARGO_LENGTH'
    WHEN 7
    THEN 'WIDTH'
    WHEN 8
    THEN 'HEIGHT'
  END GROOVE
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'TBL_CARGO'
AND COLUMN_ID   IN(4,6,7,8);

Using DECODE(looks short):

SELECT COLUMN_ID,
  DECODE(COLUMN_ID, 4, 'WEIGHT', 6, 'CARGO_LENGTH', 7, 'WIDTH', 8, 'HEIGHT')
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'TBL_CARGO'
AND COLUMN_ID   IN(4,6,7,8);

Upvotes: 2

Related Questions