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

Question

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?

Get rid off the in-line view. Do it in single query, no sub-query is required. Post your input and desired output. — Lalit Kumar B
– Lalit Kumar B, Commented Jul 3, 2015 at 11:12
Your subselect returns one row per column in the table TBL_CARGO. It's unclear to me what you're trying to accomplish - could you please add sample input (or the DDL statement for TBL_CARGO) and expected output? — Frank Schmitt
– Frank Schmitt, Commented Jul 3, 2015 at 11:13
@FrankSchmitt Lalit Kumar's solved my problem thanks for comments. — TDL
– TDL, Commented Jul 3, 2015 at 11:20

Lalit Kumar B · Accepted Answer · 2015-07-03 11:14:01Z

2

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

answered Jul 3, 2015 at 11:14

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

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

1 Comment

Lalit Kumar B Over a year ago

@TDL Glad it helped, please mark it as answered, would help others.

jarlh · Accepted Answer · 2015-07-03 11:14:36Z

0

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)

answered Jul 3, 2015 at 11:14

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

2 Comments

Lalit Kumar B Over a year ago

Why did you remove the filter on table_name?

jarlh Over a year ago

Good question, I couldn't find out where to move it from the sub-select... Easy to add if required.

Madhivanan · Accepted Answer · 2015-07-03 11:19:11Z

0

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)

answered Jul 3, 2015 at 11:19

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related