I have a table with about 10 fields to store gps info for customers. Over time as we have added more customers that table has grown to about 14 million rows. As the gps data comes in a service constantly inserts a row into the table. 90% of the data is not revelent i.e. the customer does not care where the vehicle was 3 months ago, but t...
On oracle 10gr2, i have several sql queries that i am comparing performance, but after first run, the v$sql table has the execution plan stored for caching, so for one of the queries i go from 28 seconds on first run to .5 seconds after. I've tried ALTER SYSTEM FLUSH BUFFER_CACHE; -- after running this, the query consistently runs at ...
Okay, I have a SQL 2005 database with a stored PDF. The column uses the image type. So when the PDF gets stores it is a byte[] array. So here is the hard part. I can get it to store the data but I am not sure how to go about getting the byte[] array back into PDF format and display it on the web page. I am using a 3rd part tool to gen...
What is the maximum number of tables that can be created in sql and what is the maximum number of columns for a single table? ...
As part of our ANT based deploys we run some sql against an Oracle database. The deploys are set to fail if any sql errors occur. (We do this using WHENEVER SQLERROR EXIT FAILURE ROLLBACK in our SQL files and using the ant exec command with failonerror=true to run the sql). We would like to add to our deploy sql files some sql that ...
I have two columns in my Items table 'Category', 'Category2', the two columns contain, essentially the same information. If I had designed the database I would have created a separate table for categories and added items to categories based based off of that table, unfortunately I didn't create the database and I can't change it now but...
Given these two tables: CREATE TABLE TEST1 (TEST VARCHAR2(1 BYTE)) CREATE TABLE TEST2 (TEST VARCHAR2(1 BYTE)) Where TEST1 has two rows both with the value 'A' and TEST2 has one row with the value 'B'. When I run this command: SELECT TEST FROM TEST1 MINUS SELECT TEST FROM TEST2 I get the output: Test ----- A It appears that MI...
In MySQL, you can use the keyword USING in a join when you join on columns from different tables with the same name. For example, these queries yield the same result: SELECT * FROM user INNER JOIN perm USING (uid) SELECT * FROM user INNER JOIN perm ON user.uid = perm.uid Is there an equivalent shortcut in SQL Server? ...
I'm confused how to import a SQL dump file. I can't seem to import the database without creating the database first in MySQL. This is the error displayed when database_name has not yet been created: username = username of someone with access to the database on the original server. database_name = name of database from the original ser...
Hey, I Have a cursor in stored procedure under SQL Server 2000 (not possible to update right now) that updates all of table but it usually takes few minutes to complete. I need to make it faster. Here's example table filtered by an arbitrary product id; Whereas GDEPO:Entry depot, CDEPO:Exit depot,Adet: quantity,E_CIKAN quantity that's u...
I have a Python application. It has an SQLite database, full of data about things that happen, retrieved by a Web scraper from the Web. This data includes time-date groups, as Unix timestamps, in a column reserved for them. I want to retrieve the names of organisations that did things and count how often they did them, but to do this for...
Attempt to generalize my questions... I want to execute a stored procedure for each result returned by a SELECT statement. Mentally I want to try something like EXEC myStoredProc (SELECT id FROM sometable WHERE cond = @param) More details related to my specific case... I have a SaaS application. I would like to delete a tenant from th...
Assume active is a "boolean field" (tiny int, with 0 or 1) # Find all active users select * from users where active # Find all inactive users select * from users where NOT active In words, can the "NOT" operator be applied directly on the boolean field? ...
Group, I need to remove the '-' from any negative numbers in a column for certain record numbers. However the Sum needs to * by .01 to get the correct format. I tried using replace but it is getting thrown off by the *.01 Below is my syntax. CASE WHEN SUM(ExtPrice) *.01 < 0 AND RecordNum BETWEEN 4000 AND 5999 THEN REPLACE(...
I am trying to create a timesheet and want to have the following SQL Query done: I have the fields datebeginning and dateending and jobid and I want to only add a new one if no open one exists for a given job id and if not I want the query to update the fild dateending for the given id. Is there a way to do that without using PHP to fir...
I need to run a couple of relatively simple SQL update statements to update a single column in an Oracle table with 14.4 million rows. One statement runs a function written in Java and the JVM runs out of memory as I’m doing the update on all 14.4 million rows. Have you written a kind of batch PL/SQL routine that can break this simple u...
Hello everyone... I have a project that consist of a system that helps to manage a bus transportations company. I have to store in my database the daily routes of all bus. How do I do that? And what can I use to help user to design routes? I'm wondering use Google Maps or something like that, to help users "design" all routes. I ca...
Disclaimer: I just started work on a new contract, which forced me to switch from MSSQL to Oracle. So I am a complete newcomer to Oracle and probably bring many bad MSSQL practices with me. Okay, on to the question... According to an answer in this thread, semicolons are bad and should be avoided. I realized this firsthand after spendin...
User SQLServer 2005 Here is an example of string I'm stuck with: {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial Rounded MT Bold;}{\f1\fnil\fcharset0 Arial;}} \viewkind4\uc1\pard\f0\fs54 1000\f1\fs20\par } I want to replace any font name with 'Times New Roman' I can get the first one with (textlong1 is ...
I have some extended classes (utilizing both HMAC and cryptographic tech), and I'm hoping to utilize them for a new product's database from said database's stored procedures (inclusive of time logging, user-event logging, etc.) under SQL Server. Can anyone recommend a resource detailing the best ways to achieve this or optionally advise...