RE: [EXTERNAL] Create view or index with a constraint

It depends on what he really needs.
If he wants an index with a condition, for example to consume it with native
I/O or to let the optimizer use this index, then Dexter's Solution is the
right way.
If he wants an index and make sure that all values in ACCT# are greater than
5000000, then he needs an Index without where condition and an separate
Check Constraint on the Column.

A check constraint can be added with the ALTER TABLE Statement (as Vern
already described).

But there are Wizards in ACS for all of these things

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan
Campin
Sent: Montag, 12. Oktober 2020 23:57
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Create view or index with a constraint

The best way to create any SQL object is to use ACS and select Schemas.

Include the library where you want to create the object.

Click on the object type you want to create. In this case, an index.

Right click and new index.

You will get a graphic screen. Follow it through and create the object.

Then right mouse on the object and say generate SQL. You can select include
CREATE OR REPLACE.

It will generate the SQL code for you to create the objects.

You can clean up and paste into a source member.

Does all the work and generates all the code that is needed and no wondering
about syntax.

I agree with Vernon though. Not clear what you are asking for.

Like a select/omit where only only certain records are included in the index
or a constraint that prevents a value from being entered in a table?

Also, if you want the SQL equivalent.of a Logical Select/Omit, you can goto
schemas and select an logical with select/omit criteria and then say
generate SQL and it will generate the SQL equivalent.


On Mon, Oct 12, 2020 at 2:18 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

This doesn't work if Tim really means a constraint - if he means
selectivity through the index, then this is cool! But they aren't the
same thing, conceptually.

Vern

On 10/12/2020 4:11 PM, Dexter Thompson wrote:

CREATE INDEX indexxx
ON tablexxx
( NewKey ASC )
WHERE Acct# > 5000000

;

On Mon, Oct 12, 2020 at 5:04 PM Tim Feldmeier
<Tim.Feldmeier@xxxxxxxx>
wrote:

Hello, I wanted to create an Index of a table with a different key
as

well

as have a constraint to account# > 500000 .
Does anyone have an example for this.




Thank You

Tim Felmeier
Programmer Analyst For Coyote System Roadrunner Transportation
Systems
1431 Opus Place, Suite 530
Downers Grove, IL 60515
Phone# (331)-2337921


The information transmitted is intended only for the person or
entity to which it is addressed and may contain proprietary,
business-confidential and/or privileged material. If you are not
the intended recipient of

this

message you are hereby notified that any use, review,
retransmission, dissemination, distribution, reproduction or any
action taken in

reliance

upon this message is prohibited. If you received this in error,
please contact the sender and delete the material from any computer.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com