RE: INDEX VS Logical File -- RPG400-L

Monica,

just open ACS (Access Client Solutions for IBM i) Schemas --> Open your
schema (If you cannot see your schema, just add it) --> Click on Views -->
right click on your logical file --> Select Generate SQL --> Select Options
--> Select Create Index Instead of view ... and let the SQL Script to be
created.
If your logical file includes something that cannot be converted you should
see a comment in the SQL Script

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i

"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 Mónica
Anastácio
Sent: Donnerstag, 19. Januar 2023 10:04
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: INDEX VS Logical File

im so sorry
Logical File
A *$PMCCMSG I SST($PMCMSG1 1 7) *
A K *$PMCCMSG *
A K $PMCDTEV
A S $PMCSITU COMP(EQ 'P')
A $PMCDEST COMP(NE 'CG')

INDEX FILE CREATED
CREATE INDEX CONSOLE_MESSAGES_$P_4 FOR SYSTEM NAME $PMCONS4
ON CONSOLE_MESSAGES_$P (*$PMCCMSG *ASC, SEND_DATE ASC)
WHERE (STATUS = 'P' AND RECIPIENT <> 'CG')
RCDFMT RMCONS4;

And this was my tries

CREATE INDEX CONSOLE_MESSAGES_SENT_$P_4 FOR SYSTEM NAME $PMCONS4

ON CONSOLE_MESSAGES_SENT_$P (MESSAGE_FIRST_LEVEL(7), SEND_DATE ASC)

WHERE (STATUS = 'P' AND RECIPIENT <> 'CG')

RCDFMT RMCONS4;

CREATE INDEX CONSOLE_SENT_MESSAGES_$P_4 FOR SYSTEM NAME $PMCONS4

ON CONSOLE_SENT_MESSAGES_$P AS (MESSAGE_CODE FOR
SUBSTRING(MESSAGE_FIRST_LEVEL,1,7) ASC,
SEND_DATE ASC)

WHERE (STATUS = 'P' AND RECIPIENT <> 'CG')

RCDFMT RMCONS4;

CREATE INDEX CONSOLE_SENT_MESSAGES_$P_4 FOR SYSTEM NAME $PMCONS4

ON CONSOLE_SENT_MESSAGES_$P MESSAGE_CODE AS
SUBSTRING(MESSAGE_FIRST_LEVEL,1,7)
(MESSAGE_CODE ASC, SEND_DATE ASC)

WHERE (STATUS = 'P' AND RECIPIENT <> 'CG')

RCDFMT RMCONS4;

On Wed, Jan 18, 2023 at 6:51 PM Rob Berendt <robertowenberendt@xxxxxxxxx>
wrote:

Sorry for my bad advice from earlier.
I looked at the basic CREATE INDEX at
https://www.ibm.com/docs/en/i/7.5?topic=statements-create-index and
the very last example is
CREATE INDEX DEPT_TYPE
ON DEPARTMENT (SUBSTR(DEPTNO,2,2))

--
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.

--
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.