So, I have a subscriptions table: id - int(11) (With Primary Key) user_id - int(11) group_id - int(11) role - int(11) pending - tinyint(1) created_at - datetime updated_at - datetime I'm often doing queries to see if users have access rights similar to this: SELECT * FROM `subscriptions` WHERE (group_id = 1 AND user_id = 2 AND pendin...
I was thinking of using AUTONOMOUS_TRANSACTION Pragma for some logging in a batch process. Does anyone have any experience with this ? If so any pros and cons would be appreciated. ...
I have a database table (sqlite) containing items that form a tree hierarchy. Each item has an id field (for itself) and a parentId for its parent. Now given an item, I must retrieve the whole chain from the root to the item. Basically the algorithm in pseudocode looks like: cursor is item retrieve parentItem for cursor by parentId if...
I have the following SQL code: select val.PersonNo, val.event_time, clg.number_dialed from vicidial_agent_log val join call_log clg on date_add('1970-01-01 02:00:00', interval clg.uniqueid second) = val.event_time order by val.event_time desc limit 100; which executes and returns rows in les...
The basic table schema looks something like this (I'm using MySQL BTW): integer unsigned vector-id integer unsigned fk-attribute-id float attribute-value primary key (vector-id,fk-attribute-id) The vector is represented as multiple records in the table with the same vector-id I need to build a separate table with the dot product (al...
I am attempting to more fully document our database packages as an API. What we would like is something like JavaDocs for PL/SQL. I have seen a couple tools out there (pldoc, plsqldoc) but would like to know from people who use them how they compare. ...
I have a basic tree structure of entities. The tree can be a maximum of 5 nodes deep, but may be N nodes wide. I have mapped this relationship in table similar to what is shown below: myID | myDescription | myParentID I am starting out with a known object, which could translate to having a starting "myID". Now I want to get all th...
I know SQL Server 2008 can do this, but essentially I need a way to log all the changes made to a database. I don't need to log selects, and I don't need to log the user, the only important data is what has been added or changed, both with regard to data and structural changes like columns, tables, and indices. What are my options? ...
I tried to delete duplicate records in my DB. The only difference is the PrimaryKey which is a uniqueidentifier. I have about 1500 entries that have been duped so in all I'm looking at around 3000 entries. So I sectioned off about 60 entries (based on the receivedOn date) and executed my code to pare them down to 30 and OH CRAP the 30...
I am looking for a better means of error trapping my sql connections on connection.open, so that I may log the error better and determine where in my application it is faulting. I am using try catch and logging the exception caught but what I really want is to know which connection is failing. My application is iterating through 70 se...
SQL query for a carriage return in a string and ultimately removing carriage return I have some data in a table and there are some carriage returns in places where I don't want them. I am trying to write a query to get all of the strings that contain carriage returns. I tried this select * from Parameters where Name LIKE '%"\n" %' A...
We use 4-4-5 Accounting Periods. In case you aren't familiar with this, you can find info at Wikipedia. To accomplish this, I created a date table with all the days for the next 10 years. I used the script (albeit modified) found here. The creation script for my table is: USE [RedFridayDates]; GO SET ANSI_NULLS ON; GO SET QUOTED_...
Short version: How can I map two columns from table A and B if they both have a common identifier which in turn may have two values in column C Lets say: A --- 1 , 2 B --- ? , 3 C ----- 45, 2 45, 3 Using table C I know that id 2 and 3 belong to the same item ( 45 ) and thus "?" in table B should be 1. What query could do ...
I have a table, let's call is [MYTABLE], with an FOR INSERT, UPDATE trigger. The trigger needs to execute a stored procedure, which will do some work based on the changes made to [MYTABLE]. I can't move the stored procedure's code into the trigger. So far, so good... since triggers execute after the changes are made, the stored procedu...
I have a set of data, with columns x and y. This set contains rows where, for any 2 given values, A and B, there is a row with A and B in columns x and y respectivly and there will be a second row with B and A in columns x and y respectivly. E.g **Column X** **Column Y** Row 1 A B Row 2 ...
Hello, I have this query to fetch the total OrderStatus that have values 1 and 5. How do I Sum only distinct OD.OrderStatus=2 as there can be multiple records in Orderdetails table with OrderStatus as 2. Please help SELECT O.OrderDate, Sum(Case When OD.OrderStatus = 2 Then 1 Else 0 End) AS OrdersOffered, Sum(Case When OD.OrderStatus =...
I manage to identify duplicate records from two different databases: select * from taskperformance a, taskperformance@dm_prod b where a.activityin = b.activityin and a.completiondate = b.completiondate How can I delete duplicated records from b? I tried: delete taskperformance@dm_prod where exist ( select * from ...
Whats the general consensus on using LINQ to perform quieries and manipulation inline and how does this differ to embedding SQL statements into your code which is considered a no no. ...
I have two arrays of values like X,Y,Z and 1,2 There is a table A with two columns.I want to validate that in table A records with all the combination exists irrespective of duplicates. e.g. X 1 Y 1 Z 1 X 2 Y 2 Z 2 Thanks in advance! ...
I have two tables, one for invoices and one for incoming payments. An incoming payment can be joined to an invoice by a foreign key like so: from invoices t1 inner join incoming_payments t2 on t1.receiptnum = t2.docnum The question: I want to return all invoices which have more than one payment posted against them. For each invoice...