Table has surrogate primary key generated from sequence. Unfortunately, this sequence is used for generating keys for some other tables (I did not designed it and I cannot change it). What is the fastest way to select last n inserted records in Oracle, ordered by id in descending order (last inserted on top)? n is some relatively sma...
I've been googling around for a bit but I can't seem to find an answer to this. I'm trying to use the Database Mail in SQL Server 2005 in a stored procedure where the idea is to send separate mail for each row in a query, each mail depending on an address residing in the rows. Imagine for example 15 - 50 rows of product orders with the ...
I have the following tables in an SQL DB Vehicles, RepairCharges, TowCharges, There will always be only 1 record for Vehicle, but multiple records for the other tables. My current LEFT OUTER Join works however if there are multiple entries in the joined tables then its returning just as many rows. My question is, I need to create a SQ...
I created a simple databse using Oracle that has several tables and a few constraints, i am using Access 2007 to interact with the database. My problem is that I have a table that is called "Customer" which holds several fields, most notably a Primary Key called CUSTID. I have another table called "Order" which uses the Primary Key in ...
Sometimes when i'm writing moderately complex SELECT statements with a few JOINs, wrong key columns are sometimes used in the JOIN statement that still return valid-looking results. Because the auto numbering values (especially early in development) all tend to fall in similar ranges (sub 100s or so) the SELECT sill produces some resul...
I have 3 columns in my db table, I need to multiply column, itm_count & itm_price and output the total for a given id(itm_id). SELECT sum(itm_price * itm_count) as total FROM ods_temporder WHERE itm_id='11' I tried to do this using the above sql query, but the result was null. What seems to be the issue here? ...
Excel has NETWORKDAYS() function that find the number of business days between two dates. Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn't have to deal with holidays. ...
Doing a simple query I'm finding two very different results, and I'm not sure how I'm coming to this conclusion. Below is the example, please let me know if its something I'm just over looking.. SELECT b.fkid as t, b.timestamp, count(b.fkid) as hits, count(distinct(b.fkid)) as num, DATE_FORMAT( b.timesta...
Group, I am still learning SQL, and I am running into a new problem. I have a couple stored procedures that are slow on their initial execution/connection. For example, I have a stored procedure (sp) that I use in an application to look up prices for a product. The first time I run the sp in the morning it may take 20-40 seconds to ex...
Is there a way to write a query equivalent to select * from log_table where dt >= 'nov-27-2009' and dt < 'nov-28-2009'; but where you could specify only 1 date and say you want the results for that entire day until the next one. I'm just making this up, but something of the form: select * from log_table where dt = 'nov-27-2009':+1;...
I have the following setup... Table: properties p_id [pk] p_propname o_id_owners [fk] Table: owners o_id [pk] o_extcode o_fname o_lname Table: selectedfeatures s_id [pk] d_id_features [fk] p_id_properties [fk] Table: features d_id [pk] d_code d_featurename g_id_featuregroup [fk] Table: featuregroup g_id [pk] g_featuregroupname I...
I am using a subquery in an UPDATE: UPDATE tableA SET x,y,z = ( (SELECT x, y, z FROM tableB b WHERE tableA.id = b.id AND (tableA.x != b.x OR tableA.y != b.y OR tableA.z != b.z))) ); My question is, what happens if the subquery returns no rows?...
I have a table containing log entries for various servers. I need to create a view with the most recent (by time) log entry for each idServer. mysql> describe serverLog; +----------+-----------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------...
Having this SQL tables for a tagging system: CREATE TABLE tags ( id SERIAL PRIMARY KEY, name VARCHAR(100) ); CREATE INDEX tags_name_idx ON tags(name); CREATE TABLE tagged_items ( tag_id INT, item_id INT ); CREATE INDEX tagged_items_tag_id_idx ON tagged_items(tag_id); CREATE INDEX tagged_items_item_id_idx ON tagged_items...
It's been a while since I touched COM so be nice ;) This is under WindowsCE 5.0 with SQLServerCE 2.0. After calling this to load SQLServerCE 2.0 : - IDBInitialize *pIDBInitialize = NULL; CoCreateInstance(CLSID_SQLSERVERCE_2_0, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void**)&pIDBInitialize); Module load occurs for SSCE20.dll w...
I have stored proc that will tell us if there is any new checks to be printed. Now I have to manipulate the data in database table in order to get at least one check to be printed. I didn't write this query. I tried but I didn't get at least one check to be printed. Can anyone help me. HERE IS THE STORED PROC CREATE PROCEDURE [proc_1250...
What is the position of SQLCE 3.5 when running on Windows Mobile 6.0 in relation to FIPS compliance. I have read lots of differing reports on this and need a definitive answer, one which comes from a reliable source (please provide links) I have read this "The encryption modes in SQL Server Compact 3.5 are FIPS complaint if FIPS mode i...
Is there a way to see what connection string was used to connect to SQL Server? Or rather what string was used in a failed login attempt. Many times I deal with complex systems in which I see failures caused by some service failing to log in to SQL. I am guessing the connection string might be wrong, but since I can't see what it was I ...
Hello, Microsoft Access wants to be helpful and change my queries when underlying object (either form or query) does not exist. For example, if I had a query that did the following... SELECT FirstName FROM persons If the persons table does not exist yet, and I import the query, Access will place an "expr: " in front of FirstName si...
I have input data coming from a flat file which has english, japanese, chinese characters in one column. I am loading these values in a staging table column whose schema definition is VARCHAR2(250 CHAR), the main table column has definition VARCHAR2(250) WHICH i can not change. So, i am doing a SUBSTR on this column. After loading the t...