[SOLVED] conditional where with subquery

conditional where with subquery

I am using conditional where as shown below to make query more dynamic when there are no condition are meet but I am getting below error while when I omit the (SELECT CP.COLUMN1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS') IS NULL part the query runs fine

SELECT P.* FROM TABLE1 P
WHERE (SELECT CP.COLUMN1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS') IS NULL 
        OR P.ID IN (SELECT CP.COLUMN1 FROM TABLE2 CP WHERE CP.CAT='PSTATUS')

ORA-01427: single-row subquery returns more than one row

Solution

It looks like you are trying to understand which items are in Table2 with a given status, or not at all, but I can't see how this query can work as column names aren't in alignment- but in general NULL is a single row comparison value - so you can't use it to compare against a result set - only single values. Try using EXISTS:


SELECT P.* FROM TABLE1 P
WHERE NOT EXISTS (SELECT CP.ID FROM TABLE2 CP WHERE CP.CAT='PSTATUS')
        OR P.ID IN (SELECT CP.ID FROM TABLE2 CP WHERE CP.CAT='PSTATUS')

See here for more details on Exists:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXISTS-Condition.html

and NULL checking : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Null-Conditions.html

I think the below may be what you are trying to achieve though - note the query at the end, this will return all items in TABLE1 that don't have a status, or have a status of PSTATUS of PSTATUS in TABLE2

-- INIT database
CREATE TABLE TABLE1 (
  ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  Name VARCHAR2(100)
);

CREATE TABLE TABLE2 (
  ID INT,
  CAT VARCHAR2(100)
);

INSERT INTO TABLE1(Name) VALUES ('Product 1');
INSERT INTO TABLE1(Name) VALUES ('Product 2');
INSERT INTO TABLE1(Name) VALUES ('Product 3');

INSERT INTO TABLE2(ID, CAT) VALUES (1, 'PSTATUS');
INSERT INTO TABLE2(ID, CAT) VALUES (2, 'FOO');

-- QUERY database
SELECT P.* FROM TABLE1 P
LEFT OUTER JOIN TABLE2 CP ON p.ID = CP.ID
WHERE COALESCE (CP.CAT, 'PSTATUS') = 'PSTATUS';

If this doesn't help, please provide more details of the data, with your desired result. HTH< NIck