SQL Delete s

[Joseph Drozdz]

I want to write a short script that would delete all files from my problemlog table every 30 days. I want to retain only 30 days worth.
Here is part the script I used

select count(*) from problemlog;
19150
Delete from problemlog where DTG <'1-sep-2008 00:00:00';

[Steven Schweda]

And you chose a VMS forum for this question
because ...?

[Hoff]

I'm not sure there's a question there, though I get the impression you'd like somebody to write some sort of a tool for you here.

Various databases can have different syntax available for the SQL-level commands, and the use of a delta time might well be feasible here. (For how DCL deals with this, see the HELP library for some information on delta, combination and absolute times and timekeeping. For the SQL documentation, see whichever SQL manual is kicking around for the particular database.)

If the database can't deal with these date formats directly, then some DCL could create a file, write the SQL statements and some DCL into a temporary procedure, and then invoke it. But that can (does) require some details around the SQL database and the context and the run-time environment, and right now this environment and this requirement clear. Writing DCL from DCL is easy; open a file, and start writing commands.

As an alternative, it might be just as easy to run a daily batch job and nuke the last 30 (or 31) days, for instance.

And assuming this is OpenVMS (the host OS and version isn't identified), there's some basic DCL date processing here:

http://64.223.189.234/node/736

And as mentioned earlier, you might well be able to use the native SQL syntax for this date-related processing, too.

[Hein van den Heuvel]

This question has NOTHING to do OpenVMS and everything with the DATABASE you are using an its capabilities.

I recommend posting the query a appropriate database related forum IF a GOOGLE search and some documenation scanning does not provide a solution.

But since you have out attention anyway...
Where does the '1-sep-2008' come from?
Looks like a piece of text.
Should it not be a formula?

For example, using ORACLE

First Generate a test table:

SQL> create table aap as select level noot, sysdate - level mies from dual connect by level < 100;

Proof that worked:

SQL> select count(*) from aap;

99

SQL> select * from aap where rownum < 5;

NOOT MIES
---------------------- -------------------------
1 07-OCT-08
2 06-OCT-08
3 05-OCT-08
4 04-OCT-08

Now for an example purge

SQL> delete aap where mies < sysdate - 30;

70 rows deleted

hth,
Hein van den Heuvel

[Richard J Maher]

Hi Joseph,

The INTERVAL data type could also be worth a look depending on your DBMS.

Cheers Richard Maher

[Willem Grooters]

The timestamp (1-sep-2008 00:00:00) suggests a VMS box; the script part suggest a relational database; but it were better it was explicitly mentioned what VMS version and, in this case far more important, what database and version.

>> would delete all files from my problemlog table

is interpreted by my as table problemlog holding date and a filespec.

Either I have to delete the files mentioned in this database, or outside the procedure or program where the query resides.

If inside, you won't get away with Hein's solution, UNLESS all these files are in one directory where just these problem logfiles exist - and nothing but these:

$ DELETE/BEFORE=today-"30 00:00"

(Well, "today-"30 00:00"" may not work in this syntax, but you get the meaning)

If outside, the same DELETE stament - and the same restictions) apply.