MySQL noob with a table-joining question: I have two tables, "splits" and "dividends" that describe events in the stock market. The splits table has "day", "ratio", and "ticker". The dividends table has "day", "amount", and "ticker". I would like to get a resulting joined table that has both tables' information, AND sorted by date and...
Hi i have the following stored proc in SQL Server 2005: ALTER PROCEDURE [dbo].[slot_sp_EngineerTimeslots_Group] @PROPREF VARCHAR(50), @PRIORITYCODE VARCHAR(3), @JOBLENGTH INT = 0, @TIMESLOT VARCHAR(3) AS SET NOCOUNT ON DECLARE @TOTALDAYS INT DECLARE @TOTALDAYSTARGET INT DECLARE @COUNTER INT ...
As part of a data regression and quality assurance exercise, I need to look for diffs between two tables that should, ideally, be nearly identical. I have looked at a large number of commercial products and can't seem to find one that satisfies all of our requirements: Must be able to compare LARGE tables (10 million rows, by 200 colum...
I have the probably unenviable task of writing a data migration query to populate existing records with a value for a new column being added to a table on a production database. The table has somewhere in the order of 200,000 rows. The source of the value is in some XML that is stored in the database. I have some XPath that will pull ou...
Hi folks, I am trying to do multiple counts in a single sql statement. I have two people, Mark and Chris. I want to count how many times each takes the train on a certain date. Here is the code I am using. SELECT TO_DATE(TRAIN.DEPARTURE_DATE,'YYYYMM') , (select COUNT(DISTINCT DEPARTURE_DATE) FROM TRAIN WHERE PERSON_ID='...
want to be able to explore the schema, run stored procs and run SQL statements. ...
I'm looking for a tool, which would help creating complex SQL queries. Sometimes it's difficult to even verify, whether the results of a query are correct. It's especially easy to get queries joining several tables to return too little or too much data. The tool should enable at least creation of test tables, some kind of visualization ...
Hi folks, i'm trying to migrate some data from two tables in an OLD database, to a NEW database. The problem is that I wish to generate new Primary Key's in the new database, for the first table that is getting imported. That's simple. But the 2nd table in the old database has a foreign key dependency on the first table. So when I wan...
Here is the definition of the stored procedure: CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR) IS BEGIN DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM all_tables WHERE owner = schema AND table_name = tblToDrop; IF v_cnt > 0 THEN EXECUTE IMMEDIAT...
I have a set of tables that look like the following: Person - PersonID, FirstName, LastName, etc... Family - FamilyID, etc... Parent - PersonID, FamilyID, etc... Child - PersonID, FamilyID, etc... Contact- PersonID, etc... My object hierarchy is organized as follows (using c#): class Person class Family interface IFamilyMember class...
I want to run the following sql command: ALTER TABLE `my_table` ADD UNIQUE ( `ref_id` , `type` ); The problem is that some of the data in the table would make this invalid, therefore altering the table fails. Is there a clever way in MySQL to delete the duplicate rows? ...
I have this query: SELECT web.KONTO, LAGKART.VARENUMMER, LAGKART.GRUPPE, LAGPRIS.PRIS, LAGKART.VARENAVN1, LAGPRIS.LXBENUMMER, LAGPRIS.ENHED FROM LAGKART INNER JOIN LAGPRIS ON LAGKART.VARENUMMER = LAGPRIS.VARENUMMER INNER JOIN DEBWEBVARER web ON web.VARENUMMER = LAGPRIS.VARENUMMER WHERE LAGPRIS.ENHED = web...
Hi, I'm looking for a MS access SQL function similar to SUBSTRING but not quite as the length of the data varies. I have several values in a field we'll call field1 The value length varies from 2 to 5. In all cases I would want to select the values except the very last character. So for example: computer browser mouse game zip Be...
In SELECT a.NAME, a.NUMBER, a.STRING, a.RDB$DB_KEY FROM ADMIN a what does a stand for? Thanks. ...
Ok, In MS Access, I have some reports that use some queries, to show data, within a date range. The queries use aliases, if, sum, and avg functions, and join multiple tables to get its data. I'd like to know if i could use a UNION ALL, with a table that has all the needed fields, to display this new data from this table, along with th...
I’m uploading products via the Magento API and they are not showing up in the frontend. I have to go into the backend, open them up, change nothing, save the product and then it will appear. Any idea why? I assume the act of saving it in the back end, is saving some extra flags in the DB, I just don’t know what. ...
I have two tables that holds inforamtion for SKU. Log table has datetimefield 2008-10-26 06:21:59.820 the other table has datetimefield 2008-10-26 06:22:02.313 I want to include this datetime fields in join for these 2 tables. Is it possible to join to tables with datetime fields that has difference not more than 3 seconds? What is the ...
I'm working with an application that adds a new row to the database, based on the last row meeting a certain criteria. Is there a standard pattern for dealing with this type of problem, or do I simply need to lock the table? Here is an over-simplified visualization: A1 A2 A3 B1 B2 Using the visualization above, a web page loads up th...
I want to convert below SQL Server code to VBScript in classic ASP... DECLARE @idoc int DECLARE @xdoc nvarchar(4000) DECLARE @xmldoc xml Set @xmldoc = '<Root><Authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham"><Titles title="The Busy Executive's Database Guide"/></Authors><Authors au_id="648-92-1872" au_lname="Blotche...
Hi, I'm trying to create a SQL 2005 query to retrieve and combine records from 3 SCCM Data Views. The first view contains records of valid PC's; the second contains logon-information containing: PC-id, username, timestamp, etc; the third contains PC-id, IP-address. The 1stview only contains a single, nique record per PC; the 2nd view ca...