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

LEGEND ,
Sep 28, 2016 Sep 28, 2016

Copy link to clipboard

Copied

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,

^_^

TOPICS
Database access

Views

4.2K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Sep 29, 2016 Sep 29, 2016

Copy link to clipboard

Copied

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

SELECT TRIM(thisID) AS thisID FROM (

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 29, 2016 Sep 29, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Oct 01, 2016 Oct 01, 2016

Copy link to clipboard

Copied

SELECT TRIM(thisID) thisID

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

SELECT TRIM(thisID) into thisID

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 02, 2016 Oct 02, 2016

Copy link to clipboard

Copied

LATEST

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines