Sitemap

Amicuk Programming Answers

SQL statements:help

2015-10-11   Views:1

Advertisement

here is a sample code of my application.... int initialDelay = 10000; // start after 10 second int period = 10000*60*60*24*30; // repeat every month Timer timer = new Timer(); TimerTask task = new TimerTask() { public void run() { try { MyDBConnectio

here is a sample code of my application....
int initialDelay = 10000; // start after 10 second
int period = 10000*60*60*24*30; // repeat every month
Timer timer = new Timer();
TimerTask task = new TimerTask() {
public void run() {
try {
MyDBConnection jdbc = new MyDBConnection();
jdbc.init();
Connection conn = jdbc.getMyConnection();
Statement stmt = conn.createStatement();
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
String[] tbl = {"emticket","emattachment","emhistory","emtracker"};
progTxt.setText("");
Logs l = new Logs();
l.append("Auto Archive");
date.getTime();
//Archive data
progTxt.append("\n\n");
progTxt.append(" Archiving Data...\n ");
stmt.execute("select * into outfile 'c:/emticket " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate()");
stmt.execute("select * into outfile 'c:/emattachment " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emattachment where EmID in (select EmID from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate())");
stmt.execute("select * into outfile 'c:/emhistory " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emhistory where TicketID in (select TicketID from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate())");
stmt.execute("select * into outfile 'c:/emtracker " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emtracker where TicketID in (select TicketID from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate())");
progTxt.append("Done.\n\n");
//Delete data
progTxt.append(" Deleting Data... ");
stmt.execute("delete emticket, emattachment, emhistory, emtracker from emticket, emattachment, emhistory, emtracker where emattachment.EmID=emticket.EmID and emhistory.TicketID=emticket.TicketID and emtracker.TicketID=emtracker.TicketID and where RcvDateTime
"_______"DATE_SUB(CURDATE(),INTERVAL 90 DAY) and CURDATE())");
System.out.println(stmt.getUpdateCount());
progTxt.append("Done.\n\n");
Here's what i want to do....I want to backup file for the current date back to 3 mos...e.g 2007-05-23 down to 2007-02-23...then delete the remaining records...before"02-23-2007 to 05-23-2007"....this is where the query part i that doesn't work....
Delete data
progTxt.append(" Deleting Data... ");
stmt.execute("delete emticket, emattachment, emhistory, emtracker from emticket, emattachment, emhistory, emtracker where emattachment.EmID=emticket.EmID and emhistory.TicketID=emticket.TicketID and emtracker.TicketID=emtracker.TicketID and where RcvDateTime
"_______"DATE_SUB(CURDATE(),INTERVAL 90 DAY) and CURDATE())");
System.out.println(stmt.getUpdateCount());
progTxt.append("Done.\n\n")
I tried putting <>=which means not equal in the "_____"but it fails to work..can somebody help me to work out with this query.

The replay answer
Advertisement
I don't know MySQL, but shouldn't you just use <= ? I also don't understand what the final part 'and CURDATE()' is for...

Go to See the other 2 answers

Logical Operations in SQL decode function ?

Category:DefaultRelease time:2015-10-11Views:130

Hi, Is it possible to do Logical Operations in SQL decode function like '>' '<' '>=' '<=' '<>' not in in not null is null eg... select col1 ,order_by,decode ( col1 , > 10 , 0 , 1) from tab; select col1 ,order_by,decode ( col1 , <&g[More]

Unable to capture the parameter values from a PL/SQL procedure

Category:DefaultRelease time:2015-10-11Views:130

hi. i'm trying to capture the parameter values of a PL/SQL procedure by calling inside a anonymous block but i'm getting a "reference to uninitialized collection error" ORA-06531. Please help me regarding. i'm using following block for calling t[More]

Unable to capture SQL Server Schema

Category:DefaultRelease time:2015-10-11Views:130

I am trying to capture SQL Server 2005 demo schema (AdventureWorks). It does shows the numbers of Tables/Indexes captured..but finishes with this error.. Error ocurred during capture: Exhausted Resultset Column not found. Skipping index detail on ind[More]

NoClassDefFoundError: com/sap/sql/log/OpenSQLException

Category:DefaultRelease time:2015-10-11Views:130

I have developed a Java project. It consists of 2 SQLJ files and then a Java class. When I try to execute the Java program, I get a Java error: java.lang.NoClassDefFoundError: com/sap/sql/log/OpenSQLException      at com.mbb.crrs.business.report.dao.[More]

AND / OR  truth table in SQL

Category:DefaultRelease time:2015-10-11Views:130

why null is introduced in the AND and OR truth table?when will a statement/part of a statement return a null value?? |AND               |True          |False             |NULL??        | |True               |True          |False             |Null    [More]

If statement in sql

Category:DefaultRelease time:2015-10-11Views:130

regarding the following sql: SELECT a.lname, a.fname, a.user_id, c.address, c.city, c.zip, c.addresstypeid, d.descriptor as state FROM users a, address c, maintstatetype d WHERE a.user_id = c.user_id(+) AND c.statetypeid = d.statetypeid(+) AND c.addr[More]

If statement in reqular sql statement

Category:DefaultRelease time:2015-10-11Views:130

Hi everyone; I am trying to alter some trigger if I am in a certain environment. here what I am trying to do in pseudo-code if ((select dbenv from environments) = 'DEV') THEN ALTER TRIGGER XXX disable; end if; this sql script will be run from sqlplus[More]

IF statement in SQL*Plus - how to do it

Category:DefaultRelease time:2015-10-11Views:130

Hi, In SQL*Plus script, I would like to keep conditional checking (IF statement) and proceed. For example, whatever is done in PL/SQL block below, want to do the same in SQL*Plus script, I know partly it can be done using VARIABLE keyword, conditiona[More]

Partner Application written in other language than PL/SQL and Java

Category:DefaultRelease time:2015-10-11Views:130

I have an application written in another language than PL/SQL or Java. I want to integrate this application as an Partner apps where I use the same user repository as Portal. Can I integrate the application by calling a stored PL/SQL-procedure based[More]

NULL and dynamic SQL

Category:DefaultRelease time:2015-10-11Views:130

If table testrh2 has the following columns and data col1 --> NULL col2 --> 2 and table testrh has the following columsn and data col1 --> NULL How could I write a dynamic SQL statement to join on the nulls? I've written the following block as a s[More]

How do I connect to SQL Server with Muse?

Category:DefaultRelease time:2015-10-11Views:130

I want to query items from database and load it back to front end. Is there a way Muse can connect to sql server database?You cannot connect to databases via Muse at the moment. Please refer: http://forums.adobe.com/message/5090145#5090145 Cheers, Vi[More]

Hot
I have a previous post int the Newbie Corner that went unanswered, so I figured I'd try again here now that I have some new information. The post is here... https://bbs.archlinux.org/viewtopic.php?id=197031 And here's a copy of that post so you don't [More]
Hi, I am a java / web developer with no reports experience. We wish do the following from a java based web application: On a web page we have two buttons 1) Print 2) Create file. We want these two buttons to perform the following tasks: 1) The print [More]
I have a replacement phone and have saved a backup but ideally before or, if need be, directly after I restore from backup I would love to clean up my apps.  I have over 2000 apps which I clearly don't use all at once so like to keep most of them sit [More]
After updating my ipad to IOS 8 my ipad wont download anything like apps, music,ibooks,app updates,etc. I do have a strong wifi connection, but when i click download, the item gets stuck preparing to download, and doesnt even get to asking for my app [More]
Hi all. I'm having problems all of a sudden with my webfont not showing/ preveiwing in design mode. eg. the menu currently looks like this (design mode): when it should like this: The font i'm using is Babas Neue. It shows perfectly in preveiw mode a [More]
Is there any easy way to mimic ORACLE 11g nth_value() function with IGNORE NULLS clause in 10g or earlier Oracle release?SQL> select  ename,   2          sal,   3          nth_value(sal,5) over() fifth_min_sal   4    from  emp   5    order by sal   6 [More]
Hi, I have created an interface for an Adobe Form . And i'm using this interface to create an Adobe Form. When i click on Layout in Adobe Form , the system is not allowing me to desing the layout , but It is throwing an error message(Informative mess [More]
Several people have posted variations on this question but I haven't been able to find a coherent explanation of, or fix for, the problem. I have downloaded all my CDs into iTunes and synched them to my iPod. Everything downloaded from CDs plays fine [More]
I have purchase elements and premier elements 12 will my backup files on 11 work in 12 or do I lose all the work. Do I complete the work in 11 then remove 11 and install 12 When I remove 11 can I sell it on?Pleaseeeeeeeeeeeeeeeeeee advise me as soon [More]
hi using :forms 10g we have a requirement in forms where we need to populate the menu items from the database.first we used hiearchial tree where it was possible.but since the requirement changed i am not sure that whether the menu items can be popul [More]