Mixed Case Fields and SQL

["Andy Leisk" <ALeisk@xxxxxxxxx>]

After having searched the archives and not finding what I wanted I have
decided to ask this illustrious group if any of you know how I might arrive
at an understanding of SQL View field naming on the iSeries.

What I would like to do is be able to create views over iSeries files
(tables) where the SQL column names are "mixed case". 
Lets make a very simple example, suppose I create a physical file using DDS
as follows:

     A          R ITEMREC              
     A            ITM_NUM        9S 0  
     A            ITM_DESC      30A    
     A          K ITM_NUM              

Notice that the field names are all in upper case.

Now I want to create an SQL View over that file, this view will only be used
by users accessing our system via an ODBC connection.


The source for this view is:

create view itemview  as        
select                          
  ITM_NUM  as Item_Number,      
  ITM_DESC as Item_Description  
  From ITEMMASTER     

I put the above statement in to a source member on the iSeries, and then I
use the RUNSQLSTM over that source member to create the view.

What I had hoped for was to have two fields in the view named:
Item_Number
Item_Description

When I access this view via ODBC from a product like Crystal Reports or
SQLThing, what I see are the following column names:
ITEM_NUMBER
ITEM_DESCRIPTION

So something has changed the field names from Mixed to UPPER case
characters.  Is there any way to prevent this case switching?  Is there any
way to keep the mixed case field names.  

I realize that i could run the "Label on" SQL statements to produce the
textual field descriptions on the iSeries that would be mixed case.  But the
ODBC products don't look at the textual field desciptions on the iSeries,
ODBC only looks at field names which have been converted to all upper case.

Any assistance which anyone in this group can provide in this matter would
be greatly appreciated.

Andy Leisk
Senior Programmer Analyst
Banta Global Turnkey Group
Menash WI, USA
920-751-4434
www.bgt.com <http://www.bgt.com>