I want to delete records from a PF outside a PGM

5 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TIB
    [...] 4. Dream team CharlieBrowne and TomLiotta give aid to a member wanting to delete records from a physical file outside a program. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following