PLSQL: Query value and stick it in another query-CodePudding

I'm trying to select a value from a one row table and stick it in my bigger query like below:

select person.first_name,
(select active_year.year from active_year) as YEAR -- should be '2022'
from person where person.last_name = 'Smith'

The year select returns an error:

ORA-00907: missing right parenthesis

How can I return the year field?

CodePudding user response：

Looks like you're misinterpreting reality, because that query won't return what you reported.

Sample data:

SQL> select * from person;

FIRST_NAME           LAST_NAME
-------------------- --------------------
John                 Smith

SQL> select * from active_year;

      YEAR
----------
      2002

Your query, literally:

SQL> select person.first_name,
  2  (select active_year.year from active_year) as YEAR -- should be '2022'
  3  from person where person.last_name = 'Smith'
  4  ;

FIRST_NAME                 YEAR
-------------------- ----------
John                       2002

SQL>

Even if it were PL/SQL, it wouldn't work - but due to a different error:

SQL> begin
  2  select person.first_name,
  3  (select active_year.year from active_year) as YEAR -- should be '2022'
  4  from person where person.last_name = 'Smith'
  5  ;
  6  end;
  7  /
select person.first_name,
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement


SQL>

Therefore, it seems that you didn't post exactly what you did.

CodePudding user response：

With sample data like this

WITH
    person AS
        (
            Select 'John'   "FIRST_NAME", 'Doe'     "LAST_NAME" From Dual UNION ALL
            Select 'John'   "FIRST_NAME", 'Smith'   "LAST_NAME" From Dual UNION ALL
            Select 'Marry'  "FIRST_NAME", 'Smith'   "LAST_NAME" From Dual UNION ALL
            Select 'Alice'  "FIRST_NAME", 'Freeman' "LAST_NAME" From Dual UNION ALL
            Select 'Mike'   "FIRST_NAME", 'Freeman' "LAST_NAME" From Dual UNION ALL
            Select 'Robert' "FIRST_NAME", 'Harris'  "LAST_NAME" From Dual
        ),
    active_year AS
        (
            Select 2022 "ACT_YEAR" From Dual
        )

Your select works ok (tested on 11gR2)

SELECT 
    p.FIRST_NAME,
    (Select ACT_YEAR From active_year) "YEAR" 
FROM 
    person p
WHERE 
    p.LAST_NAME = 'Smith'
--
--  R e s u l t
--
--  FIRST_NAME       YEAR
--  ---------- ----------
--  John             2022 
--  Marry            2022

I don't know what is the problem,but there is another way to get the same result if active_year is a one row table. Try to put it this way - as a joined table to your bigger query. It will work.

SELECT 
    p.FIRST_NAME "FIRST_NAME",
    a.ACT_YEAR  "YEAR" 
FROM 
    person p
INNER JOIN
    active_year a ON(1 = 1)
WHERE 
    p.LAST_NAME = 'Smith'
--
--  R e s u l t
--
--  FIRST_NAME       YEAR
--  ---------- ----------
--  John             2022 
--  Marry            2022

All the above is SQL and if you have this select in PL/SQL it will work as a CURSOR definition, but inside the block you will have to insert an INTO clause to store the values in variables and then do whatever you want to do. In that case you should be sure not to select more than one row because an error will be raised...

Declare
    varName     VarChar2(50);
    varYear     Number(4);
Begin
    SELECT  p.FIRST_NAME "FIRST_NAME", a.ACT_YEAR  "YEAR" 
    INTO    varName, varYear
    FROM    person p
    INNER JOIN  active_year a ON(1 = 1)
    WHERE   p.LAST_NAME = 'Harris';
End;