Highlighted

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

LEGEND ,
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.1K

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

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

LEGEND ,
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.1K

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
Sep 28, 2016 0
Advocate ,
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
Reply
Loading...
Sep 29, 2016 0
LEGEND ,
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
Reply
Loading...
Sep 29, 2016 0
Adobe Community Professional ,
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
Reply
Loading...
Oct 01, 2016 0
LEGEND ,
Oct 02, 2016

Copy link to clipboard

Copied

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
Reply
Loading...
Oct 02, 2016 0