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

[WolfShade]

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,

^_^

[EddieLotter]

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

SELECT TRIM(thisID) AS thisID FROM (

Cheers

Eddie

[WolfShade]

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]

SELECT TRIM(thisID) thisID

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

SELECT TRIM(thisID) into thisID

[WolfShade]

thisID isn't a variable, though.  I could try your original suggestion of including AS thisID to see if Oracle is getting confused by some ambiguous anomaly that I'm not seeing. 

Thanks,

^_^