Generic File Name Search - *OUTFILE

[jabccs]

Hi all: I need to perform a generic file name search on a library and create an OUTFILE with the file names found. I tried to use DSPOBJD, which allows a generic file name search with *OUTFILE BUT.... I need to search for the last 5 characters of the file name, ignoring the characters before that. DSPOBJD allows me to search generically for the first characters but not the last. Is there an equivalent command that allows for a generic file name search on the last characters of the name, with *OUTFILE?

[J.Pluta]

The short answer is "no". The quickest way would be to execute the DSPOBJD to an outfile and then use SQL to either select the records you want to process or delete the ones you don't. The easiest would be to write a simple one-line SQL statement to delete the records and then run it using RUNSQLSTM.

[jabccs]

Thanks for the reply Pluta. I'll investigate the RUNSQLSTM command. I experimented with it many moons ago. The SQL code goes in a source file I think and the RUNSQLSTM command refers to the source member the sql code is in. If I execute the DSPOBJD command and create a physical file of all files in a library, what would the SQL code look like to identify all the object names whose "last" 5 characters equal 12345? I'm sure this is simple but my SQL skills are minimal.

[B.Robins]

The statement would depend upon what "the last 5 characters" really means. Does that mean positions 6 through 10 of the name or the last 5 non-blank characters of the name: A12345, ABC12345, etc. Select odobnm from dspobjdf where odobnm like '_____12345' or Select odobnm from dspobjdf where right(rtrim(odobnm),5)='12345' Bill

[J.Pluta]

As Bill notes, it depends on what you mean. He has code that will work for either of the cases. I would probably change the first case to: WHERE SUBSTR(ODOBNM,6,5) = '12345' Remember, though, this only checks the last five characters of the name field, not necessarily the name. Any name shorter than 10 characters would not pass, since at least one character in the substring would be blank. Joe

[jabccs]

Thanks Bill. You're second example would work because the length of the file name in odobnm is unknown in advance. I thought of another potential barrier though. If I have this sql code in a source member and run it using the RUNSQLSTM command from a CL program, is there a way to pass the "last 5 characters" as a variable to the sql statement? The user will be entering the characters at run time because they represent a unique processing run number.

[brianmay]

I don't know much about RUNSQLSTM, but if it cannot accept a parameter, there is another option. You could write an RPG program with embedded SQL. Then you could accept your parameter in the RPG and build your SQL statement dynamically in your code and run it.

[jabccs]

I agree Brian that I can do this work fairly easily in an RPG program. It just seems like a simple task and I was hoping to just be able to handle it in CL. Maybe not!