Home > IT Answers > AS/400 > I want to delete records from a PF outside a PGM
TIB
5 pts.
 I want to delete records from a PF outside a PGM
CPYF, PGM, SQL, SQL commands, SQL Server
I need to either setup a SQL that lets me pass a variable so that I can delete records based on a moving date. Or, I want to setup a CPYF command that will allow me to copy records that are *LT a variable (date). I'm not sure how to pass variable to either idea.

Software/Hardware used:
I-Series
ASKED: March 21, 2011  3:25 PM
UPDATED: March 22, 2011  8:47 PM
RELATED QUESTIONS
Answer Wiki:
With the CPYF, you just need a CL program that receives your data as a parm. Then use (assuming this is an externally defined file) you use the inputted parm in the INCREL option of the CPYF command. * With the SQL, it is the same thing. If you do not have a Command to run an SQL statement in a CL program, you can download one off the net. Or you can have an RPG program that does the SQL statement and receives the data as a parm, Or you can build the source for a RUNSQL command using the inputted date.
Last Wiki Answer Submitted:  March 21, 2011  3:33 pm  by  CharlieBrowne   32,855 pts.
All Answer Wiki Contributors:  CharlieBrowne   32,855 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Please show us the definition of the date column in the table. A DATE data type column will have different input requirements than a numeric or character column that only holds a value that represents a date.

Tom

TomLiotta
 108,055 pts.
 

The creation of a “QM query” is probably the way you should go.

Here are basic steps for a procedure that you can use for most similar needs in the future:

  • CRTSRCPF FILE( mylib/QQMQRYSRC )
  • STRSEU SRCFILE( mylib/QQMQRYSRC ) SRCMBR( DLTBYDATE ) TYPE( SQL ) OPTION( 2 )

The source file only needs to be created once. It can have any name, but QQMQRYSRC is the default name for this kind of procedure. The default record length will be 92, which is actually one position longer than can actually be used.

The STRSEU command will exist if your system has the source editor installed. If not, there are other ways of doing the next step. The command will open an edit session, and it creates a new source member and lets you type into it. What you type will be the SQL statement that the procedure is built around. The statement will look something like one of these two, depending on the definition of the date column and other possible elements: 

delete from mylib/myfile where DATECOL = &DATE

Or: 

delete from mylib/myfile where DATECOL = &q&DATE&q

The names of the library, the file and the date column will come from your existing file. The &DATE value will come from your “moving date” when you invoke the SQL statement.

The SQL is compiled with this step:

  • CRTQMQRY QMQRY( mylib/DLTBYDATE ) SRCFILE( mylib/QQMQRYSRC )

Once the QM query is created, you can execute it at any time with a command like this: 

STRQMQRY QMQRY( mylib/DLTBYDATE )
         SETVAR((DATE 032111))

The value ’032211′ would most likely really be a CL variable, but I’m just using a numeric constant. The idea is that each time you run the STRQMQRY command (in a CL program), you will pass a new date value in through the SETVAR() parameter. The new value will replace the corresponding replacement variable in the SQL that you compiled.

If you can provide some details, the actual steps could be provided.

Tom

TomLiotta
 108,055 pts.