Caracter order 1

[UAMI]

I have a subfile where the user can find register's by name. In the file (ordered by name) some names have accentuation. When the user pretend to find the names begining with AB i wnat to write in the subfile, first the:
ABA
and then the same but with the accentuation
ABB
and then the same but with the accentuation
ABC
and then the same but with the accentuation

and so on

I know that in sql it is possible to do something like this:
SELECT t0$tit,
T0NOME,translate(T0NOME, 'EEEEUUUUIIIIOOOOOAAAAAC',

'ÉÈÊËÚÙÛÜÍÌÎÏÓÒÔÕÖÁÀÂÃÄÇ' ) NOME FROM ACOCTLD/TITUL0



but then i have a problem, i have no pf with a key

any idea???

 

[MdnghtPgmr]

UAMI,

Have you considered using embedded SQL? This might be a way to get the best of both worlds, SQL and RPG. I can't give you any guidance along those lines as we are not allowed to use embedded SQL but there is a FAQ on this forum about embedded SQL.

HTH,
MdnghtPgmr

 

[UAMI]

The problem of the embended sql is that i have no key. The user will type some letters of the name, like, barba, and program must list all names beginning with barba. It has to apear barba
barbara


I could create a LF
R RECORD1 PFILE(PF1)
00020A CHAR1 I TRNTBL(LIB1/TBL1)

How do i define the translations table?

 

[Mercury2]

Hi,
Sorry but I do not clearly see your problem. Can you pls show us an excerpt of implied names in the file ?
Philippe --

 

[UAMI]

AGÊNCIA IMPRENSA NOVOSTI
AGGLOCORK-CORTIÇAS LDA
ALÍPIO FRANCISCO MORAIS
ALBANO FONSECA FRIAS
AMÍLCAR SOUSA COELHO ASCENSO
AMADEU ARAÚJO SILVA

This is the output of the actual subfile, but it should be:
AGÊNCIA IMPRENSA NOVOSTI
AGGLOCORK-CORTIÇAS LDA
ALBANO FONSECA FRIAS
ALÍPIO FRANCISCO MORAIS
AMADEU ARAÚJO SILVA
AMÍLCAR SOUSA COELHO ASCENSO

 

[Mercury2]

Well why don't you create a program with SQL embedded in this RPG ? AMHO it could perfectly do the job to order and help you to create the subfile in the desired order. I do not see the need of a translation table.
Therefore you could use the following steps:
1/ Prepare the SQL stm with LIKE "user_selected_name%" predicate and ORDER BY name.
2/ Open cursor from prepared stm.
3/ Fetch rows thru cursor
4/ Load SF with required data
5/ close cursor

It looks to be the best bet.
HTH
Philippe --

 

[flapeyre]

Put the following in your logical file DDS at the file level:

A                                      ALTSEQ(QSYS/table_name)

To determine what table_name should be, do a DSPFFD on the physical file and look at the CCSID. Then do a WRKTBL command, and scroll down until you see one that matches your CCSID. Use that in the logical file. You can also create your own custom table from here if you need to.

It looks like you need to select one of the tables that starts with the letter Q followed by your file's CCSID.

De mortuis nihil nisi bonum.

 

[Mercury2]

Hmm spoke to soon... (

I git it finally . Using SQL is actually an option but you need a translation table.

 

[iSeriesCodePoet]

SQL is a powerful, but beautiful thing. I am with TalkTurkey about using SQL.

You can either dynamically build the SQL statement, or you can build a fixed one. I know how to do both. If you need a program sample, let me know. I have a template I use to build all new programs. That should help.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[jmanj]

If you call your display program thru CL then you
can call OPNQRYF then sort the file before the display pgm.

 

[UAMI]

Well, i followed the advice from "flapeyre", and construct my own trntbl. wITH THE HELP OF A EBCDIC TABLE (code page 01148)

CRTTBL TBL(TEST/XXX_TBL) SRCFILE(*PROMPT)

For the caracter â (hex 42) i put (hex 81)= a
and for all caracter's with accent i put the code with no accent. In this way every caracter's have the same value. Then i crteated a LF, with a new field:
A X0NOME I RENAME(NOME)
A TRNTBL(FTBLGD04)
A
A K X0NOME


Thank you all, for your help and "iseriescodepoet", i might use the sql another time but i have some doubht's in using it. I apreciate the template anyway..

 

[iSeriesCodePoet]

There was a thead here a while back that I started about using SQL instead of reads. There was an article about how many people are switching to SQL over READ, CHAIN, etc.

If you have any doubts, maybe you should ask your questions. Start a new thread so all others can benifit. I know several of us here use SQL and are quite proficient.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

 

[UAMI]

Sorry, iseriescodepoet, i expressed myself wrong. When i said i had doubts, i ment, i can't use it very well. One time i made a experience and i had to do a strange thing to put the program function.

I mean, after the program run a time, an error message apeared, something like null values....
than i made the following:
READ_SQL BEGSR
*
/EXEC SQL
+ Fetch Next From Point Into
+ :GL$GRU,
+ :U3$RAM, :U3NAPO, :U3NORD, :U3$RIS, :U3OPSR, :U3NSEQ, :U3CAPT ,
+ :U3PREM, :AUDCRI:IND4,:AUDENT:IND5,:AUDSAI:IND6,
+ :AF$FRA:IND1,:AFFVAL:IND2,:AFFDIA:IND3, :A0ACTA
/END-EXEC
*
* = -1 É NULL
IF IND1=-1
EVAL AF$FRA=0
ENDIF
ENDSR

And so, no more null values.. do you know if that error was normal or the select was wrong???

 

[iSeriesCodePoet]

There is always a possibility of having a result of NULL when you do a join. What you have to do this is use COALESCE (usage: COALESCE(MYFILE,what I want instead of NULL). I have used this a couple times, and works great. Let me know if you have any other questions.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

 

[Mercury2]

iSeriesCodePoet,
You mean:
COALESCE(MYFIELD,what I want instead of NULL) or better yet use the more meaningful IFNULL predicate instead : IFNULL(MYFIELD,what I want instead of NULL)
Philippe -- )

 

[iSeriesCodePoet]

Yes...

..hey I didn't know about that!!! I will try to remeber that for next time!

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net