13 Replies Latest reply: May 12, 2009 1:40 PM by 627047 RSS

    Uppercase lower case issue..

    627047
      I am fiinding this throughout all the oracle stuff.
      If I create a view or stored procedure, it always returns the column names in uppercase...
      This is an issue, becuase some of my code uses keys in grids that are case sensitive.
      And my app has to work with both MS SQL and Oracle..
      So the view listed below, returns the column names in uppercase instead of the case I have specified..
      Is there any way around this?

      SELECT View_AssetCurrent.AssetID,
      View_AssetCurrent.ItemVar1 ,
      View_AssetCurrent.ItemVar2 ,
      View_AssetCurrent.ItemVar3 ,
      View_AssetCurrent.ItemVar4 ,
      View_AssetCurrent.ItemVar5 ,
      View_AssetCurrent.ItemVar6 ,
      View_AssetCurrent.ItemVar7 ,
      View_AssetCurrent.ItemVar8 ,
      View_AssetCurrent.ItemVar9 , View_AssetCurrent.ItemVar10 ,
      View_AssetCurrent.ItemVar11 ,
      View_AssetCurrent.ItemVar12 ,
      View_AssetCurrent.ItemVar13 ,
      View_AssetCurrent.ItemVar14 ,
      View_AssetCurrent.ItemVar15 ,
      View_AssetCurrent.ItemVar16 ,
      View_AssetCurrent.ItemVar17 ,
      View_AssetCurrent.ItemVar18 ,
      View_AssetCurrent.ItemVar19 ,
      View_AssetCurrent.ItemVar20 ,
      View_AssetCurrent.AssetDate , View_AssetCurrent.ProfileID , View_AssetCurrent.Carrier ,
      View_AssetCurrent.CurrentID ,
      View_AssetCurrent.DestinationID ,
      View_AssetCurrent.OwnerID ,
      View_AssetCurrent.IntraUserName ,
      View_AssetCurrent.ClientName ,
      View_AssetCurrent.HDate ,
      View_AssetCurrent.HStatus ,
      View_AssetCurrent.HParent ,
      View_AssetCurrent.HVar1 ,
      View_AssetCurrent.HVar2 ,
      View_AssetCurrent.HVar3 , View_AssetCurrent.HVar4 ,
      View_AssetCurrent.HVar5 ,
      View_AssetCurrent.HVar6 ,
      View_AssetCurrent.HVar7 ,
      View_AssetCurrent.HVar8 ,
      View_AssetCurrent.HVar9 ,
      View_AssetCurrent.HVar10 ,
      View_AssetCurrent.SignatureID ,
      View_AssetCurrent.ImageID ,
      View_AssetCurrent.UpdatedByParent ,
      View_AssetCurrent.ExplicitOwnerID ,
      View_AssetCurrent.ExplicitDestID ,
      View_RecipAndLoc_All.EmpID ,
      View_RecipAndLoc_All.FullName ,
      View_RecipAndLoc_All.RecipVar1 ,
      View_RecipAndLoc_All.RecipVar2 ,
      View_RecipAndLoc_All.RecipVar3 ,
      View_RecipAndLoc_All.RecipVar4 ,
      View_RecipAndLoc_All.RecipVar5 ,
      View_RecipAndLoc_All.RecipVar6 ,
      View_RecipAndLoc_All.RecipVar7 ,
      View_RecipAndLoc_All.RecipVar8 ,
      View_RecipAndLoc_All.RecipVar9 ,
      View_RecipAndLoc_All.RecipVar10 ,
      View_RecipAndLoc_All.DYNAMIC ,
      View_RecipAndLoc_All.Download ,
      View_RecipAndLoc_All.STATUS ,
      View_RecipAndLoc_All.LastModified ,
      View_RecipAndLoc_All.EmailAddress ,
      ClientList.Client_Type ,
      RTRIM(f_getRecipName(View_AssetCurrent.CurrentID, View_AssetCurrent.ProfileID)) CurrentName,
      RTRIM(f_getRecipName(View_AssetCurrent.DestinationID, View_AssetCurrent.ProfileID)) DestinationName ,
      RTRIM(f_getRecipName(View_AssetCurrent.OwnerID, View_AssetCurrent.ProfileID)) OwnerName ,
      Signatures.Sig_Data "Sig_Data"
      FROM View_AssetCurrent
      LEFT JOIN Signatures
      ON View_AssetCurrent.SignatureID = Signatures.Sig_ID
      LEFT JOIN ClientList
      ON View_AssetCurrent.ClientName = ClientList.Client_Name
      LEFT JOIN View_RecipAndLoc_All
      ON View_AssetCurrent.ProfileID = View_RecipAndLoc_All.ProfileID
      AND View_AssetCurrent.DestinationID = View_RecipAndLoc_All.EmpID
        • 1. Re: Uppercase lower case issue..
          Tubby
          Enclose your column name in "
          {code}
          SQL?Select 1 as "MyColumn" from dual;

          MyColumn
          ------------------
          1

          1 row selected.

          Elapsed: 00:00:00.01
          {code}

          Edited by: Tubby on May 12, 2009 9:24 AM
          • 2. Re: Uppercase lower case issue..
            627047
            thanks, 1 question..
            If I do a select from that view does that mean my column names in the select have to be encased in double quotes?
            Like

            SELECT * FROM VIEW_ASSETCURRENT_DESTID WHERE ROWNUM < 5000 and 1=1 AND "HDate" >= to_date('5/11/2009 12:08:51 PM','MM/DD/YYYY HH:MI:SS AM') AND "HDate" <= to_date('5/12/2009 12:08:51 PM','MM/DD/YYYY HH:MI:SS AM')
            • 3. Re: Uppercase lower case issue..
              Warren Tolentino
              are you referring to the colum name itself or the data on the columns from a view?
              • 4. Re: Uppercase lower case issue..
                Frank Kulash
                Hi,

                Unquoted parts of SQL commands are converted to uppercase before Oracle compiles the statement.

                Why can't you specify uppercase letters for the table- and column names? It seems like that's the best bet for database independence, and also the easiest to use in Oracle. (That is, every time you refer to the name in Oracle, you'll have to use double-quotes.)

                If you really, really want to use lowercase letters in a table- or column name, enclose the name in double-quotes:
                SELECT  "View_AssetCurrent"."AssetID",
                        "View_AssetCurrent"."ItemVar1",
                ...
                I don't know how SQL Server handles double-quotes.
                • 5. Re: Uppercase lower case issue..
                  Tubby
                  Easy enough to test out....
                  ME_XE?create or replace view test_it as select 1 as "CaseSensitive" from dual;
                  
                  View created.
                  
                  Elapsed: 00:00:00.18
                  ME_XE?select * from test_it where casesensitive = 1;
                  select * from test_it where casesensitive = 1
                                              *
                  ERROR at line 1:
                  ORA-00904: "CASESENSITIVE": invalid identifier
                  
                  
                  Elapsed: 00:00:00.04
                  ME_XE?select * from test_it where "casesensitive" = 1;
                  select * from test_it where "casesensitive" = 1
                                              *
                  ERROR at line 1:
                  ORA-00904: "casesensitive": invalid identifier
                  
                  
                  Elapsed: 00:00:00.00
                  ME_XE?select * from test_it where "CaseSensitive" = 1;
                  
                       CaseSensitive
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  • 6. Re: Uppercase lower case issue..
                    627047
                    If I specified uppercase for the code I would have to completely redo the MS SQL tables, views, stored procs etc...
                    Since the application already works with MS SQL and we have few Oracle customers.. the extra work has to go into the Oracle side of things...
                    • 7. Re: Uppercase lower case issue..
                      627047
                      the resulting column name that is output by the view
                      • 8. Re: Uppercase lower case issue..
                        Warren Tolentino
                        to better understand try to post a sample single row output from your query.
                        • 9. Re: Uppercase lower case issue..
                          627047
                          With modifications:
                          SELECT AssetID "AssetID",IntraUserName "IntraUserName",ItemVar1,ItemVar2 FROM VIEW_ASSETCURRENT_DESTID WHERE ROWNUM < 5000 and 1=1

                          returns a table(ref curosr)(dataset)
                          AssetID - IntraUserName - ITEMVAR1- ITEMVAR2

                          so the first two field are in the case/format i need. the next two are displayed in uppercase...

                          Is that clear?
                          • 10. Re: Uppercase lower case issue..
                            Warren Tolentino
                            close but what you posted was the column names. please also post the result in data (1 single row).
                            • 11. Re: Uppercase lower case issue..
                              627047
                              actually the result data is unimportant.
                              It will return whatever I have entered into it in whatever case it is..

                              The problem is the column/field names are returned in uppercase..

                              Column Names AssetID - IntraUserName - ITEMVAR1 - ITEMVAR2
                              Data 12345-1 admin null null
                              12345-2 admin city state
                              • 12. Re: Uppercase lower case issue..
                                Warren Tolentino
                                does the case sensitive has to be applied in all columns on the select clause including on the where clause?
                                • 13. Re: Uppercase lower case issue..
                                  627047
                                  no...
                                  It just needs to return the case formatted names..
                                  But, in order to do that oyu need to alias the column name.. ie: AssetID "AssetID"

                                  but in views, if oyu do that then if you do a select from a view you must use the quoted identifier.


                                  so what I have decided to do is Uppercase everything in my sql database. then it won't matter anymore... :)