14 Replies Latest reply: Sep 25, 2013 12:35 AM by davidp 2 RSS

    Not able to query table with column name as number

    TanmoyMoulik

      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.
          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

            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

              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.
                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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.