Re: Field names in SQL-generated files

Bill

I believe it's in the "Run an SQL script" option. When you have selected a system under Databases in the environment list on the left, open it to see the schemas (libraries). Open a library to see all the SQL objects, including tables. Right-click Tables and see the New... option - this opens a CREATE TABLE wizard.

And I see that there is an option there to assign a system (short) name, instead of the generated one. I was looking at V5R4 of Navigator for this function. Not sure when it came in - maybe forever. But Generate SQL ignores this short name - that's not cool, as Rob pointed out. Seems a bug to me.

Also interesting - since I've never looked at this before - the record format name is the generated name - I created a table named THISISALONGNAME, short name LONGNAME. Format was named THISI00001, which was the system name when the table was created, then I have to assume a RENAME TABLE was run - didn't do a monitor, which would tell us everything done.

There are similar wizards for all the SQL objects types.

For SELECT, UPDATE, INSERT, DELETE, there's an SQL Assist in the Run an SQL script task. Once you've selected a system under databases, there's a link to this on the Databases tasks on the lower-right. SQL Assist is under the Edit menu option. There's also a drop-down of some templates that get copied into the statement space.

HTH
Vern

On 8/23/2012 7:15 AM, Erhardt, Bill wrote:

Where does one find the "Table Create Wizard" in Ops Nav?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of alan0307d@xxxxxxxxx
Sent: Wednesday, August 22, 2012 9:31 PM
To: Midrange Systems Technical Discussion
Subject: Re: Field names in SQL-generated files

Do you have Ops Nav installed. It has a table create wizard that will generate the sql automatially and you can enter short and long names. I always give short and long names. I could also send you a script with both but use Ops Nav if you can.

Sent from my Verizon Wireless 4GLTE smartphone

----- Reply message -----
From: "James Lampert" <jamesl@xxxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Subject: Field names in SQL-generated files
Date: Wed, Aug 22, 2012 6:33 pm


We've got an SQL jock with little or no AS/400 experience developing an entirely SQL-generated database for us (and in fact, that script that was giving us trouble recently was part of it -- I passed along the answers, and he was able to get the script working; thanks!).

At any rate, we're getting (not unexpectedly) SQL field names coming up as column headings, looking at the file in QuestView. and generated
AS/400 native field names appearing wherever the SQL field names exceed
10 characters.

Is there a way to specify both the AS/400-native and SQL-native field names when creating the file? And what about specifying an AS/400-native filename when the SQL-native filename exceeds 10 characters?

I know about "LABEL ON" for column headings; I've used it in my own SQL scripts

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.