× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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>
          

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.