Skip to main content
WolfShade
Legend
September 28, 2016
Question

Oracle: Check to see if a record exists - return something else if it does not

  • September 28, 2016
  • 1 reply
  • 5513 views

Hello, all,

I've been running into a brick wall for the last day or two, over this.

I'm trying to write a query that will return an ID if it exists in the database; if the ID does not exist, I want to return a 1 (numeric, not string).

Here is what I have, so far, that isn't working.  (It's throwing error messages in SQL Developer).

DECLARE

    v_count number(2) := 0 ;

    BEGIN

        SELECT COUNT(thisID) into v_count

        FROM tableA

        WHERE UPPER(trim(thisID)) = 'zzz'

             OR trim(thatID) = '987987987987' ;

        IF v_count > 0 THEN

            SELECT TRIM(thisID) thisID FROM (

                SELECT thisID

                FROM tableA

                WHERE UPPER(trim(thisID)) = 'zzz'

                     OR trim(thatID) = '987987987987'

                                           )

            WHERE ROWNUM = 1 ;

        ELSE

            SELECT 1 thisID FROM DUAL ;

        END IF ;

    END;

I keep getting error messages that SELECT INTO was expected on line 10; followed by a PL/SQL compilation error.  What am I doing incorrectly, here?

V/r,

^_^

This topic has been closed for replies.

1 reply

EddieLotter
Inspiring
September 29, 2016

It's been decades since I did PL/SQL, but shouldn't line 10 be:

SELECT TRIM(thisID) AS thisID FROM (

Cheers

Eddie

WolfShade
WolfShadeAuthor
Legend
September 29, 2016

In Oracle, "AS" isn't required; if a column name or column in a function like trim() is followed by a non-reserved word, it is assumed that the word is an alias.

V/r,

^_^

BKBK
Community Expert
Community Expert
October 1, 2016
SELECT TRIM(thisID) thisID

Couldn't you just repeat what you did earlier? That is:

SELECT TRIM(thisID) into thisID