PL/SQL oracle function with ora-01744 INTO

oracle, plsql, sql

Solution

The `INTO` clause must be in the outer-most `SELECT` not the inner one. And you don't need two levels of nesting, one derived table and a `rownum <= 1` is enough.

So it should be something like this:

SELECT full_name
  INTO info
FROM (
    SELECT (NAME || ' ' || SURNAME) as full_name
    FROM TB_CUSTOMER 
    WHERE FLAT_ID = flatId 
      AND TYPE = typeId 
    ORDER BY OWN_DATE DESC
) Q2 
WHERE ROWNUM <= 1;

Note that it might still fail with a "no rows found" error if the inner select does not return anything.

Problem

I have set of functions in myqsl that I need to write oracle versions. Which is going well until I face limit 1 issue. I think my query for limit is the problem but couldn't figure out what's wrong. Can you give me a hand? MySQL version ``` SELECT concat(fld_name, ' ', fld_surname) INTO info FROM tbl_customer WHERE fld_flat_id = flatId and fld_type = typeId order by fld_own_date desc limit 1; ``` Oracle version (causing the problem) ``` SELECT Q1.* FROM ( SELECT ROWNUM AS RWNR2, Q2.* FROM ( SELECT (NAME || ' ' || SURNAME) INTO info FROM TB_CUSTOMER WHERE FLAT_ID = flatId AND TYPE = typeId ORDER BY OWN_DATE DESC ) Q2 WHERE ROWNUM <= 1 ) Q1 WHERE Q1.RWNR2 > 0; ``` And by the way I know table names and fields are different, values are correct in this preview.

Original source