Getting ORA-01427 trying to select all from view I've created

Question

I created a view from tables using these queries

CREATE VIEW AVAIL_TOOLS AS
SELECT T.TOOL_NAME AS "Name", R.RETAILER_NAME AS "Retailer", T.RETAILER_NUM AS "Number", I.TI_STATUS AS "Status",
(SELECT 
  CASE 
     WHEN DETAIL_RETURNDATE IS NULL THEN TO_CHAR(DETAIL_DUEDATE,'Day, Month, DD, YYYY') 
     ELSE 'Now' END FROM DETAILRENTAL) AS "Expected Avaliablity"  
FROM TOOL T
INNER JOIN RETAILER R
ON T.RETAILER_NUM = R.RETAILER_NUM
INNER JOIN TOOLINSTANCE I
ON T.TOOL_NUM = I.TOOL_NUM
WHERE (I.TI_STATUS = 'Rented') OR (I.TI_STATUS = 'Available')
ORDER BY T.TOOL_NAME;

now when I try to run SELECT * FROM AVAIL_TOOLS I receive the error ORA-01427: single-row subquery returns more than one row. The case I am running in the query obviously seems to be the problem. Now is there anyway I could change the queries around. I am trying to create a view where if the return date is null it will be available when it is returned other wise it should be now.

I'm at a loss on how I should change about to get the resulted needed.

Kacper Kacper · Accepted Answer · 2016-11-01T19:09:17

Query below should do what you want when you fill proper join condition.

CREATE VIEW AVAIL_TOOLS AS
SELECT T.TOOL_NAME AS "Name", R.RETAILER_NAME AS "Retailer", T.RETAILER_NUM AS "Number", I.TI_STATUS AS "Status", 
  CASE 
     WHEN DETAIL_RETURNDATE IS NULL THEN TO_CHAR(DETAIL_DUEDATE,'Day, Month, DD, YYYY') 
     ELSE 'Now' END "Expected Avaliablity"  
FROM TOOL T
INNER JOIN RETAILER R
ON T.RETAILER_NUM = R.RETAILER_NUM
INNER JOIN TOOLINSTANCE I
ON T.TOOL_NUM = I.TOOL_NUM
LEFT OUTER JOIN DETAILRENTAL DR
ON (I.ID? = DR.TOOL_INSTANCE_ID?) --HERE fill join
WHERE (I.TI_STATUS = 'Rented') OR (I.TI_STATUS = 'Available')
ORDER BY T.TOOL_NAME;

Second option is adding to subquery with case where clause based on the same condition as join:

where I.INSTANCE_ID = DETAILRENTAL.INSTANCE_ID

Getting ORA-01427 trying to select all from view I've created

2 Answers