Web Development·12,052 discussions

I need a help with sql nested sub query please.

Locked

Hi,
I can create this view;

CREATE VIEW view1 AS
SELECT p.bedno, p.refno, p.surename
FROM patient p
WHERE p.bedno =
(SELECT bedno
FROM bed
WHERE bedstatus = ‘USED’);

But when I “SELECT * FROM view1″, I get this error message it says ” ERROR at line 2:
ORA-01427: single-row subquery returns more than one row”.
I need count “bedstatus”, help me please.
Thank you

Topic

Clarifications

In reply to I need a help with sql nested sub query please.

Clarifications

Two options

In reply to I need a help with sql nested sub query please.

if you want to stick with the subquery
it’s
where p.bedno IN (select…)

You are getting the error because you have more than one used bed and the logic of teh command says your outer query has to used all of them

you could also change it to a correlated subquery.

SELECT p.bedno, p.refno, p.surename
FROM patient p
WHERE p.bedno =
(SELECT bedno
FROM bed
WHERE bedstatus = ‘USED’ and bed.bedno = p.bedno);

assuming of course bed no is unique

A join would be much better though

SELECT p.bedno, p.refno, p.surename
FROM patient p bed b
WHERE p.bedno = b.bedno
and bedstatus = ‘USED;

query

In reply to Two options

You could use either method Tony mentioned, or use an EXIST clause.

Try this

In reply to I need a help with sql nested sub query please.

CREATE VIEW view1 AS
SELECT p.bedno, p.refno, p.surename
FROM patient p
WHERE p.bedno in
(SELECT bedno
FROM bed
WHERE bedstatus = ‘USED’);

Also try running the query before you create the view.

use IN operator instead of ‘=’

In reply to I need a help with sql nested sub query please.

As ur subquery returns two rows,then ur checking those two rows with bedno column of patient table using ‘=’, which checks only one value at a time.so use IN operator instead of ‘=’. The IN operator will checks multiple values at a time.

[Author]

Replies