Exact Fetch

[kamk]

I know the message "exact fetch returns more than requested number of rows" typically means that I should only be getting one record back but it is returning more.

When I run the following code through my proc I get the exact fetch message, but when I run just the select it returns only one record. Any help as to what is on the go would be appreciated.

SELECT DISTINCT A.ADJ_SHELTER_AMT
INTO V_ADJ_SHELTER_AMT
FROM accommod a, res_address r, "CASE" c
WHERE c.RES_ADDRESS_ID = r.RES_ADDRESS_ID
AND r.RES_ADDRESS_ID = a.RES_ADDRESS_ID
AND a.DATE_END IS NULL
AND C.CASE_ID = 56282

 

[Thargy]

kamk,

the word CASE is an oracle reserved word.
This will be confusing things, as SQL will be expecting a syntactically correct CASE statement to follow.

Check in v$reserved_words for future reference.

I'd recommend changing the name of the table called "CASE" to something else.

Regards

Tharg

Grinding away at things Oracular

 

[Dagon]

I don't think that is the cause of the problem, as kamk has included it in double quotes, so it should work.

Try changing it to a cursor and looping through it to see how many rows you get back.

 

[carp]

Is it possible that there is at least one row in the table where ADJ_SHELTER_AMT is NULL, but the row fits all of the WHERE criteria? This could display an "invisible" row when you run the query in SQL, but would still cause the PL/SQL error.
Assuming ADJ_SHELTER_AMT is a number, try this in SQL:
SELECT DISTINCT NVL(A.ADJ_SHELTER_AMT,-757575)
FROM accommod a, res_address r, "CASE" c
WHERE c.RES_ADDRESS_ID = r.RES_ADDRESS_ID
AND r.RES_ADDRESS_ID = a.RES_ADDRESS_ID
AND a.DATE_END IS NULL
AND C.CASE_ID = 56282