This discussion is archived
14 Replies Latest reply: Sep 24, 2013 10:35 PM by davidp 2 RSS

Not able to query table with column name as number

TanmoyMoulik Newbie
Currently Being Moderated

Hi ,I need a small help.I have a table created by HPSM application with one of the column name number.So as its reserve keyword I am not able run select number from tablename.Is there any workaround so that I can fetch value of column name number?

 

Thanks,

Tanmoy

  • 1. Re: Not able to query table with column name as number
    Paul M. Oracle ACE
    Currently Being Moderated
    I have a table created by HPSM application with one of the column name number.So as its reserve keyword I am not able run select number from tablename.Is there any workaround so that I can fetch value of column name number?

     

     

    Do you mean this ?

    SQL> desc test

    Name                                      Null?    Type

    ----------------------------------------- -------- ----------------------------

    number                                             NUMBER

    If so, then see this example :

     

    SQL> insert into test values(123);

     

    1 row created.

     

    SQL> select number from test;

    select number from test

           *

    ERROR at line 1:

    ORA-00936: missing expression

     

    SQL> select "number" from test;

     

    number

    ----------

          123

     

    SQL>

  • 2. Re: Not able to query table with column name as number
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Paul showed you the best way to cope with the problem, but curing it would be better.  You should change the application so that it creates a reasonable column name in the first place.  If that's not possible, then change the column name as soon as you can.  Names that require double-quotes are nothing but trouble.

  • 3. Re: Not able to query table with column name as number
    TanmoyMoulik Newbie
    Currently Being Moderated

    Hi PaulM,

     

    Thanks for reply.Howerver the problem is that I can't chnage the column name as its COTS tool product,so changing the column name is not possible.So the structure is like this

     

    tablename

    (number varchar2(60),

    ----

    );

     

    So tried to run the query as select "number" from tablename.Still getting the same error.

     

    Thanks,

    Tanmoy

  • 4. Re: Not able to query table with column name as number
    Paul M. Oracle ACE
    Currently Being Moderated
    So tried to run the query as select "number" from tablename.Still getting the same error.

     

    As you can see from my post, it should work. Please post any possible details of what you've done.

  • 5. Re: Not able to query table with column name as number
    Pablolee Journeyer
    Currently Being Moderated

    Anyone find it vaguely amusing that not only is the choice of name very poor from a keyword/reserved word perspective, but purely from a semantic perspective (a column called number, defined as a varchar2).

    Just sayin'

  • 6. Re: Not able to query table with column name as number
    davidp 2 Pro
    Currently Being Moderated

    Quoted identifiers are case sensitive, and you haven't given us a true table definition. Try:

    select "NUMBER" from tablename

  • 7. Re: Not able to query table with column name as number
    Etbin Guru
    Currently Being Moderated

    From within Oracle everything seems to work in Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

     

    create table not_possible (number varchar2(60))

     

    ORA-00904: : invalid identifier

     

    create table tst ("NUMBER" varchar2(60),"number" varchar2(60),"Number" varchar2(60))

    insert into tst values('UPPER','lower','Initcap')

    insert into tst values('UPPER1','lower1','Initcap1')

    insert into tst values('UPPER1','lower2','Initcap3')

    insert into tst values('upper1','lower2','initcap3')

    insert into tst values('upper1','lower1','initcap1')

     

    select "NUMBER","number","Number",

           case when "NUMBER" = upper("NUMBER")

                 and "number" = lower("number")

                 and "Number" = initcap("Number")

                then 'consistent'

           end case_to_name,

           case when regexp_replace("NUMBER",'\D+') = regexp_replace("number",'\D+')

                 and regexp_replace("number",'\D+') = regexp_replace("Number",'\D+')

                 and regexp_replace("Number",'\D+') = regexp_replace("NUMBER",'\D+')

                then 'consistent'

           end numeric_value      

      from tst

     

    NUMBERnumberNumberCASE_TO_NAMENUMERIC_VALUE
    UPPERlowerInitcapconsistent-
    UPPER1lower1Initcap1consistentconsistent
    UPPER1lower2Initcap3consistent-
    upper1lower2initcap3--
    upper1lower1initcap1-consistent

     

    Check the dictionary to find out how the application managed to create the table

     

    select *

      from user_tab_columns

    where table_name = 'TST'


    So tried to run the query as select "number" from tablename.Still getting the same error.


    Did you try select "NUMBER" from tablename ?

     

    Regards

     

    Etbin

  • 8. Re: Not able to query table with column name as number
    TanmoyMoulik Newbie
    Currently Being Moderated

    Hi Etbin,

     

    I cannot chnage the column type as this table is created the COTS product (HP service manager) and on changing the column structure the whole product shall behave behaviour.DO we have any workaround to fetch the data without chnaging the column name?

     

    Thanks,

    Tanmoy

  • 9. Re: Not able to query table with column name as number
    davidp 2 Pro
    Currently Being Moderated

    Etbin just showed how to retrieve columns called "NUMBER","number" and "Number" - you put the correctly capitalised version of the column name inside double quotes.

    If you need more, post the table name and the output of a describe of the table.

  • 10. Re: Not able to query table with column name as number
    EdStevens Guru
    Currently Being Moderated

    TanmoyMoulik wrote:

     

    Hi Etbin,

     

    I cannot chnage the column type as this table is created the COTS product (HP service manager) and on changing the column structure the whole product shall behave behaviour.DO we have any workaround to fetch the data without chnaging the column name?

     

    Thanks,

    Tanmoy

     

    I don't what is more absurd/sad/laughable ..

     

    A column whose name is a reserved word

    A column whose reserved word name is a direct lie about the data type of said column

    That this absurdity is created by a commercial software provider

    That the commercial software provider that created this absurdity is not some minor startup run by 3 Java junkies with no understanding of databases, but by a company with the resources of HP.

  • 11. Re: Not able to query table with column name as number
    marcusafs Journeyer
    Currently Being Moderated

    This is not unusual in COTS that are ported from other databases.  The easiest solution is to use table aliases.  I once had a column named current_date and the select worked but it was always the same date and that date was today.  Looked like the application was doing an unconditional update to the current_date field.  Using an alias returned the correct date.

     

    select tab.number from t;able1 tab;
    

     

    Marcus Bacon

  • 12. Re: Not able to query table with column name as number
    EdStevens Guru
    Currently Being Moderated

    MarcusAFS wrote:

     

    This is not unusually in COTS that are ported from other databases.  The easiest solution is to use table aliases.  I once had a column named current_date and the select worked but it was always the same date and that date was today.  Looked like the application was doing an unconditional update to the current_date field.  Using an alias returned the correct date.

     

    1. select tab.number from t;able1 tab; 

     

    Marcus Bacon

     

    This is not unusually in COTS that are ported from other databases.

     

    Not unusual but still inexcusable.  I can understand in-house developers having the narrow vision of their own shop, but people developing software for sale to others should know better.  I'm not saying that the actual developers at a software house are or should be any smarter than those working in-house, but the organization should have standards and procedures that account for the broad spectrum of their customer base and the platforms they are developing for.  All the more so when said organization is one with the stature, size, and resources of an HP.

     

    I still remember the software sales team that told me (the evaluation team I was on) that while their product would run on Oracle they recommended SQL Server instead, "because Oracle won't perform will with more than five concurrent user connections."

  • 13. Re: Not able to query table with column name as number
    TanmoyMoulik Newbie
    Currently Being Moderated

    Hi Marchus,

     

    Thanks for the reply.However still not able to run the query.

    I tried this query is sqldeveloper

     

    select tab.number from probsummarym1 tab;

     

    Getting this error.

    ORA-01747: invalid user.table.column, table.column, or column specification

    01747. 00000 -  "invalid user.table.column, table.column, or column specification"

    *Cause:   

    *Action:

    Error at Line: 6 Column: 12

     

     

    Please some one suggest me any workaround for resolve this issue.

     

    Thanks,

    Tanmoy

  • 14. Re: Not able to query table with column name as number
    davidp 2 Pro
    Currently Being Moderated

    Etbin showed you how to do this. https://forums.oracle.com/message/11182214#11182214

    If you want more help, show us the results from "describe probsummarym1" in SQLDeveloper.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points