Nov 222013
 

A quiz of a slightly different kind this time. The following SQL raises an ORA-01722 (invalid number) exception:

select *
from   this_simple_join tsj, 
       does_not_work dnw
where  tsj.column_1 = dnw.column_1
and    tsj.column_2 = dnw.column_2;

ORA-01722: invalid number 

We can fix this so no error is returned without changing the SQL statement itself. How is this possible?

Put your answers in the comments! I’ll post my full solution on Monday.

Get SQLfail sent to your inbox

  10 Responses to “SQL Quiz: How can we fix this query (without changing it)?”

  1. It’s related to implicit datatype conversion:

    SQL:
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

    CREATE TABLE this_simple_join (column_1, column_2) AS SELECT 1.1, 1.1 FROM DUAL;

    CREATE TABLE does_not_work (column_1, column_2) AS SELECT CAST (column_1 AS VARCHAR2 (10 CHAR)), CAST (column_2 AS VARCHAR2 (10 CHAR)) FROM this_simple_join;

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

    SELECT *
    FROM this_simple_join tsj,
    does_not_work dnw
    WHERE tsj.column_1 = dnw.column_1
    AND tsj.column_2 = dnw.column_2;

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

    SELECT *
    FROM this_simple_join tsj,
    does_not_work dnw
    WHERE tsj.column_1 = dnw.column_1
    AND tsj.column_2 = dnw.column_2;

    DROP TABLE this_simple_join;

    DROP TABLE does_not_work;

    Result:
    Session altered.
    Table created.
    Table created.
    Session altered.
    SELECT *
    FROM this_simple_join tsj,
    does_not_work dnw
    WHERE tsj.column_1 = dnw.column_1
    AND tsj.column_2 = dnw.column_2
    *
    Error at line 4
    ORA-01722: invalid number

    Session altered.

    COLUMN_1 COLUMN_2 COLUMN_1_1 COLUMN_2_1
    ---------- ---------- ---------- ----------
    1.1 1.1 1.1 1.1
    1 row selected.
    Table dropped.
    Table dropped.

    • You’re correct Salek, it’s to do with implicit conversions.

      I have a slightly different setup and therefore solution, but this works well :)

      Can anyone see how else we can solve this?


  2. We can fix this so no error is returned without changing the SQL statement itself. How is this possible?

    trivial:

    setup like Salek Talangi:

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

    CREATE TABLE this_simple_join (column_1, column_2) AS SELECT 1.1, 1.1 FROM DUAL;

    CREATE TABLE does_not_work (column_1, column_2) AS SELECT CAST (column_1 AS VARCHAR2 (10 CHAR)), CAST (column_2 AS VARCHAR2 (10 CHAR)) FROM this_simple_join;

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

    SELECT *
    FROM this_simple_join tsj,
    does_not_work dnw
    WHERE tsj.column_1 = dnw.column_1
    AND tsj.column_2 = dnw.column_2;

    gives


    WHERE tsj.column_1 = dnw.column_1
    *
    ERROR at line 4:
    ORA-01722: invalid number

    And here is my fix:


    sokrates@11.2 > delete does_not_work;

    1 row deleted.

    sokrates@11.2 > SELECT *
    2 FROM this_simple_join tsj,
    3 does_not_work dnw
    4 WHERE tsj.column_1 = dnw.column_1
    5 AND tsj.column_2 = dnw.column_2;

    no rows selected

    no data, no error >:P

    • haha :)

      Perhaps I should have been more explicit – the query actually has to return some data! ;)

      • Even more explicitly, I don’t change any of the data in the tables.

        • ok, second try.

          This time this setup


          ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

          DROP TABLE this_simple_join;
          DROP TABLE does_not_work;

          create TABLE this_simple_join ( column_1 varchar2(10), column_2 number );
          create TABLE does_not_work ( column_1 number, column_2 varchar2(10) );

          insert into this_simple_join values('1.1', 1.1);
          insert into does_not_work values(1.1, '1.1');

          SELECT *
          FROM this_simple_join tsj,
          does_not_work dnw
          WHERE tsj.column_1 = dnw.column_1
          AND tsj.column_2 = dnw.column_2;

          WHERE tsj.column_1 = dnw.column_1
          *
          ERROR at line 4:
          ORA-01722: invalid number

          we’re not allowed to change the query nor the data.

          So we change the table:


          sokrates@11.2 > alter table does_not_work rename column column_1 to tmp;

          Table altered.

          sokrates@11.2 > alter table does_not_work rename column column_2 to column_1;

          Table altered.

          sokrates@11.2 > alter table does_not_work rename column tmp to column_2;

          Table altered.

          sokrates@11.2 > SELECT *
          2 FROM this_simple_join tsj,
          3 does_not_work dnw
          4 WHERE tsj.column_1 = dnw.column_1
          5 AND tsj.column_2 = dnw.column_2;

          COLUMN_1 COLUMN_2 COLUMN_2 COLUMN_1
          ---------- ---------- ---------- ----------
          1.1 1,1 1,1 1.1

          • Nice, that works but it still isn’t what I was thinking of ;)

            I’ve got something much more straightforward in mind. It doesn’t require changing any NLS parameters or anything “unusual” like renaming columns.

          • ok, third try.

            Well, if we are not to allowed the query, nor the data, nor the tables, nor nls-params, then we have to change the plan.

            Here is my complete solution.

            alter session set optimizer_mode = all_rows;

            create table this_simple_join( column_1 number, column_2 number );
            create table does_not_work( column_1 number, column_2 varchar2(10) );

            insert into this_simple_join values( 1, 1);
            insert into this_simple_join values( 2, 2);

            insert into does_not_work values(1, '1');
            insert into does_not_work values(3, 'a');

            SELECT *
            FROM this_simple_join tsj,
            does_not_work dnw
            WHERE tsj.column_1 = dnw.column_1
            AND tsj.column_2 = dnw.column_2;

            alter table does_not_work add primary key( column_1 );

            SELECT *
            FROM this_simple_join tsj,
            does_not_work dnw
            WHERE tsj.column_1 = dnw.column_1
            AND tsj.column_2 = dnw.column_2;

            gives ( for me ) the following output


            sokrates@11.2 > alter session set optimizer_mode = all_rows;

            Session altered.

            sokrates@11.2 >
            sokrates@11.2 > create table this_simple_join( column_1 number, column_2 number );

            Table created.

            sokrates@11.2 > create table does_not_work( column_1 number, column_2 varchar2(10) );

            Table created.

            sokrates@11.2 >
            sokrates@11.2 > insert into this_simple_join values( 1, 1);

            1 row created.

            sokrates@11.2 > insert into this_simple_join values( 2, 2);

            1 row created.

            sokrates@11.2 >
            sokrates@11.2 > insert into does_not_work values(1, '1');

            1 row created.

            sokrates@11.2 > insert into does_not_work values(3, 'a');

            1 row created.

            sokrates@11.2 >
            sokrates@11.2 > SELECT *
            2 FROM this_simple_join tsj,
            3 does_not_work dnw
            4 WHERE tsj.column_1 = dnw.column_1
            5 AND tsj.column_2 = dnw.column_2;
            ERROR:
            ORA-01722: invalid number

            no rows selected

            sokrates@11.2 >
            sokrates@11.2 > alter table does_not_work add primary key( column_1 );

            Table altered.

            sokrates@11.2 >
            sokrates@11.2 >
            sokrates@11.2 > SELECT *
            2 FROM this_simple_join tsj,
            3 does_not_work dnw
            4 WHERE tsj.column_1 = dnw.column_1
            5 AND tsj.column_2 = dnw.column_2;

            COLUMN_1 COLUMN_2 COLUMN_1 COLUMN_2
            ---------- ---------- ---------- ----------
            1 1 1 1

            sokrates@11.2 >

          • You got it :)

            My solution was slightly different (I just created an index instead of a primary key), but it’s the same principle. I’m posting my complete solution on Monday.

  3. […]   SQL Quiz: How can we fix this query (without changing it)? […]

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>