Re: I have come across an interesting SQL issue

[Mark Murphy <jmarkmurphy@xxxxxxxxx>]

Hmm, I didn't try the double quotes. Maybe there was a bug that was fixed
some time between V7.2 and v7.4. I do know the column names are fine as the
only change I had to make to create the table was to change the table name.

And no to Implementer.

On Thu, Feb 8, 2024 at 10:10 AM Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>
wrote:

> On my 7.4 system, your SQL statement works fine, from both STRSQL and ACS'
> Run SQL Scripts.
>
> Whenever I run across an error like yours, where changing the name of
> something "fixes" the issue, I enclose the name in double quotes.  E.g.:
>
> create or replace table "usr_initial_program_override"
> FOR SYSTEM name INLPGM
> (prefix char(2) default ' ' primary key,
> library char(10) default ' ' not null,
> name char(10) default ' ' not null
> );
>
> If, by any chance, you are using PTC (MKS) Implementer change management
> to deploy this, I am aware that Implementer uses this double quote
> workaround.  (But I doubt that you are, because Implementer requires you to
> name the table on the CREATE statement with a short name = to the source
> member name, then RENAME it to a long name afterwards.)
>
> I'm also wondering whether any of the field names are causing an issue as
> well.  Again, double quotes can resolve these.
>
> - Dan
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Mark Murphy
> Sent: Thursday, February 8, 2024 9:37 AM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: I have come across an interesting SQL issue
>
> Maybe someone can explain this.
>
> create or replace table usr_initial_program_override
> FOR SYSTEM name INLPGM
> (prefix char(2) default ' ' primary key,
> library char(10) default ' ' not null,
> name char(10) default ' ' not null
> );
>
> The above fails with SQL Error [42710]: [SQL0601]
> USR_INITIAL_PROGRAM_OVERRIDE in JMMLIB type *FILE already exists.
>
> It doesn't exist, I *ALLOBJ on this box, and I can't find it using WRKOBJ
> or by querying SYSTABLES. In addition:
>
> SELECT * FROM jmmlib.usr_initial_program_override;
>
> Fails with SQL Error [42704]: [SQL0204] USR_INITIAL_PROGRAM_OVERRIDE in
> JMMLIB type *FILE not found.
>
> If I change the SQL file name, it works. I have 2 files this happened
> with, both ended with override, and when I changed the override part of the
> name to ovrd, they started working.
>
> ???
> *** CONFIDENTIALITY NOTICE: The information contained in this
> communication may be confidential, and is intended only for the use of the
> recipients named above. If the reader of this message is not the intended
> recipient, you are hereby notified that any dissemination, distribution, or
> copying of this communication, or any of its contents, is strictly
> prohibited. If you have received this communication in error, please return
> it to the sender immediately and delete the original message and any copy
> of it from your computer system. If you have any questions concerning this
> message, please contact the sender. ***
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.