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
My HP Touchsmart was sent back in to HP for repairs under warranty. I was advised to make a copy of my harddrive because they may erase my hard drive, which they did, if the problem had to do with a faulty motherboard, which it was. They replaced my [More]
Recently I copied my iTunes music library on to my Time Capsule using the instructions here (http://support.apple.com/kb/HT1449) because I'm running out of space on my internal hard drive. (I haven't deleted the original files yet, since, as you'll s [More]
When I'm on Skype I use my external monitor, and my 15" retina MBP is in clamshell mode. This is why I need a microphone to use Skype. I heard that you can't use USB-powered microphones on a Mac. Is this true? What microphone can you recommend me? I' [More]
I made a movie on my digital camera and wanted to get in on my computer to email to my kids.. Turns out to be 200mg.. I try to use Movie Maker, as that is the only program I know that can email a movie this size or download it onto a CD. BUT Movie ma [More]
What's with Apple and the latest updates?  They are really messed up!  with ios 8.02 and 8.10 updating via itunes, (itunes 12.0.1.26) has screwed my iphone.  The update fails.  The restore won't go since it wants a password, which on a defaulted ipho [More]
I had recieved my new macbook today and if I press the power button the screen is not coming up nor I could hear any beep But a white LED was glowing in the front, dvd drive makes some noise and fans are running. Battery indicator shows full. Further [More]
Hi, I'm using PostgreSQL as my DB and the pg73jdbc3.jar driver to connect to my DB. I'm trying to display the pound sterling currency symbol (�) in my JTable but it will not display, it puts in a square instead. However, it will display the U.S. doll [More]
Hi all, Im trying to set up extension mobility without using a CUE module. I have the configuration set up correctly, but im having all sorts of issues with the url services. Can anyone offer any insight / requests for information that might help wor [More]
Hi all, following my other topic to remove the shutdown/reboot/logout power, I now need to make a script to execute the given command at specific time. What I am trying to do (see script below) is to get the time into a variable, then check if the ti [More]
Dear Gurus, We have an Internal Order and we want to change the Responsible Cost Center and the Profit Center fileds in the Basic Data. But we can only change the Responsible Cost Center field only... how  can I change Profit Center field too... Than [More]