sql

Creating batch updates from within Java

I want to update every row on a specific column in a mySql database. Currently I am using a java.sql.PreparedStatement for each row and iterating in a for loop. I was wondering if there were any other alternatives in terms of Java programming to make this less time and resource consuming (something like executing the prepared statements ...

Value should be 0 between the two dates?

Using SQL Server 2000 I want to compare the table2.date between table1.from, table1.todate, if exist then value should be 0 (zero) Table1 ID FromDate ToDate 001 20090801 20090815 002 20090817 20090820 …, Table2 Id Date Value 001 20090730 100 001 20090731 200 001 20090801 300 001 20090802 400 … 001 20090815 0 001 20090816 250 … ...

Can I include the table name in the result of an SQL query?

If I have two tables: Actor: ID | Name 1 : Patrick 3 : Emma 7 : Vanessa Singer: ID | Name 4 : Will 5 : Madonna 13 : Emma Can I generate the following table from an SQL query, which contains the table name each record came from? ID | Career | Name 1 : Actor : Patrick 3 : Actor : Emma 4 : Singer : Will 5 : Singer : Madonna ...

% in the beginning of like clause

I heard that its not advised to use % in the beginning of LIKE clause in SQL Server due to performance reasons.Why is this is so? Some more details on this will help me in understanding the impact of this issue. ...

ExecuteNonQuery not executing - vb.net

Hi I'm trying to execute a SQL command, but I just can't find out why this is not working. This is how I defined the "execute" function in my class named "clsSQL": ''#... Private m_sConnectionString As String = String.Empty ''#... Friend WithEvents m_objConnection As SqlConnection Friend WithEvents m_objCommand As S...

generate table DDL via query mysql mssql

Hi, Is there an easy way to extract table DDL information, via a query, using either Ms or My SQL server? (preferably both?) For example, using MySQL Administrator / Navicat for MySql, there is a "DDL" function, which generates the "create table foo (....)" script. Is there any way to get this information from a query itself, such as...

SQL query with count and case statement

i need to find how many people have filed (fil_dt) their tax return within 60 days, withing 120 days, within 180 days, over 180 days of their filing period end date (fpe) for 3 different years (2006, 2007, 2008) the statement below will give me ALL years i need a count for each year and for each possibility.. anyway i can do this withou...

SELECT SUM as field

Suppose i have this table table (a,b,c,d). Datatypes are not important. I want to do this select a as a1,b as b1,c as c1, (select sum(d) from table where a=a1 and b=b1) as total from table group by a,b,c ...but I can't find a way (sqldeveloper keeps complaining with "from clause not found".) Is there a way? Is it possible? ...

Format an SQL Query using a function

I have a SELECT query that returns multiple lines for the same record due to a group by on a field that has many different results e.g. ID Name x 1 test a 1 test b 1 test c How do I create a function that takes the current record id in the select statement and returns all the values of x in one field? ...

SQL Date and Flash Date problem

I am using the local database feature in AIR 1.5. I have a table which is created by: "CREATE TABLE IF NOT EXISTS employees (timeAdded Date, name STRING)" Now lets say that I want every employee added between date1:Date and date2:Date, I would do that by using: "SELECT * FROM employees WHERE " + date1 + "<timeAdded AND timeAdded<" + ...

SQL Server, views and datetime conversions...

So I have a base table that looks something like this. SELECT [BILL_MONTH] ,[BILL_YEAR] ,[BILLED] FROM bill_Detail Everything is varchar. I created a view based on that table that converts the bill year and bill month to a datetime field. Of course, the bill_month and bill_year fields have some crap data that doesn't co...

SQL - Trying to get Customers who ordered a product for the first time during a month and...

I need to get a list of customer who ordered a particular product for the first time in a particular month and then sum how much of that items they have ordered since then. I started working on this but feel like I am going in the wrong direction - SQL is not my strong point. So sample result data would be something like: ...

Oracle reverse index and query

If I create a reverse index in Oracle on a VARCHAR2 column do I also need to reverse the value used in the WHERE clause? CREATE INDEX myindex ON myTable(name) REVERSE; SELECT name FROM myTable WHERE name='Peter'; or SELECT name FROM myTable WHERE name='reteP'; ...

MySQL call a stored procedure every x minutes

Is there a way in MySQL to call a stored procedure from within SQL every x minutes? I want to use this in a session-environment, where the database keeps track of all the sessions and automatically deletes sessions older than x minutes. ...

PostgreSQL 8.2: require specific column to be present in UPDATE statement

I would like to force user to specify origin of update to some table (sometbl), eg. to specify 'local' or 'remote' (for col2) - but checking of that requirement should occur at DB level when UPDATE statement is executed so: UPDATE sometbl SET col1 = 'abc'; should throw error (exception), but: UPDATE sometbl SET col1 = 'abc', col2 = '...

How to save a single result set to a temp table from a SQL stored procedure returning multiple sets?

I need to store the result set of a stored procedure in a temporary table (using SQL Server 2000). From what I've read, this (poorly constructed example) should work: create table #tempTable (TempId int primary key, Column1 varchar(100), Column2 varchar(100), DateCreated datetime) insert into #tempTable (TempId, Column1, Column2, Dat...

using aggregate function with more than two columns

I have two tables, stats and stat_log. The stat log is like this: user_id,stat_id,value,registered and It logs the value of the stats for the given times. I need every last value from every stat for a given user. So i want something like this, but with values: select stat,max(registered) from stat_log where uid = 1 group by stat; ...

LINQ to SQL: Lazy loading an association

How can I lazy load an association (EntitySet) in LINQ to SQL? You can't set Delay Loaded on Associations in the designer, and I couldn't find a DBML attribute for it either. I looked at DataLoadOptions to see if there was a way to lazy load them that way, but DataLoadOptions really just provides a way to mold the SQL that is generated f...

MySQL select that returns a dummy column?

Is there a way i can do a SELECT and tell it to return 3 columns and a dummy empty column ? I need 4 columns and the 3rd must be '' until the table is somewhere in the database. I am not allowed to add any columns to any tables. ...

Transactions within loop within stored procedure

I'm working on a procedure that will update a large number of items on a remote server, using records from a local database. Here's the pseudocode. CREATE PROCEDURE UpdateRemoteServer pre-processing get cursor with ID's of records to be updated while on cursor process the item No matter how much we optimize it, th...