Hi all, In a SQL database I got storage information for each user, for each customer. I need to write a stored procedure that sums the disc usage (MB) for the customers users. When I got the total sum of all users for a single customer (totalDiscUsage), I need to perform a calculation (simple example): x = numberOfUsers * 200 y = (tot...
Lets say I have a table with columns such as: ID Name City State ZIP I need to write a query that will return only one row. This row will include City, State, and ZIP, but I only want a field to have a value if all values in the results set are the same, otherwise I want the field to be null. For example, if every record has the sam...
I'm maintaining an in-house ORM written in C#, and it currently does not have any eager-loading mechanism. To improve performance, we decided that it would require eager loading so we need to write our own code to support that. (My colleagues and I do not have any experience with any ORM tools, and furthermore, due to some legacy reasons...
Hi all, I have a couple of tables which look like this Table 1 user_id | name ------------------------- x111 | Smith, James x112 | Smith, Jane etc.. Table 2 id | code | date | incident_code | user_id ----------------------------------------------------------------- 1 | 102008 | 10/2...
All our applications rely on a certain amount of client data i.e. Lookups for comboboxes, users, roles, user-roles... We currently use a spreadsheet to map the data and generate insert scripts that are then imported into SQL server. The has seemed to work for us but it is very difficult to update and maintain when there are a lot of ch...
I have the following table of TIME datatypes clarification: I am representing hours/mins/seconds of videos and want to figure out how long the videos will be. runtime ---------------- 01:27:19.0000000 01:26:09.0000000 01:00:56.0000000 01:09:59.0000000 01:25:43.0000000 01:16:01.0000000 01:27:12.0000000 01:22:00.0000000 01:17:47.0000000 ...
I've developed an application that populates a treeview from hierachical data in a database. I've designed it to use lazy-loading so it only gets the child nodes when a node is expanded. My problem is that obviously I don't know if a node has children unless I make a call to the database and look. Currently I have implemented a dummy c...
I need to select by date in a SQL query, for example SELECT * FROM foo WHERE date = '2009-09-09' That query works in my Hypersonic test database, but not Oracle, which seems to requires: SELECT * FROM foo WHERE date = TO_DATE('2009-09-09', 'yyyy-mm-dd') Is there a way to select by date uniformly across these two databases? ...
I'm an MSSQL guy, but I'm working on a DB2 query that needs to create a temp table, insert into it, and do stuff with it. As a much-shortened test, I'm using the following query, which is providing the same result.. declare global temporary table tt_testingSyntax (id int); insert into session.tt_testingSyntax (id) values (1); insert i...
Hi, I need to convert an existing db table to a new db table which adds a new column. I have the following sql code which set the default value of this column to be -1. // Insert new column for holding appWidgetIds db.execSQL("ALTER TABLE favorites " + "ADD COLUMN appWidgetId INTEGER NOT NULL DEFAULT -1;"); But how can i set the ...
Specifically, in relational database management systems, why do we need to know the data type of a column (more likely, the attribute of an object) at creation time? To me, data types feel like an optimization, because one data point can be implemented in any number of ways. Wouldn't it be better to assign semantic roles and constraints...
I have a table which has a column labeled 'sortorder' which is used to allow customer to manually change the order of each item. The table also has a column labeled 'CategoryId'. I was curious, if I was bulk importing a set of data in which I knew all data, including CategoryId, how I could specify the incrimenting value for 'SortOr...
I’m trying to import a table into a secured sql database using vb.net but it seems to timeout (or so I think) and not import properly (null value), I truncate the table to save the field types and then call the shell() function to import the file and 'Clean out the existing data in the validation import table cmd.CommandText = "TRUNCAT...
Say I have a table that has items and attributes listed like, frog green cat furry frog nice cat 4 legs frog 4 legs From the items column I want to select unique objects that have both the green and 4 legs attribute. I would expect to get back just the frog object in this case. What is the most efficient query to do t...
Forgive my naivety, but I am new to using Delphi with databases (which may seem odd to some). I have setup a connection to my database (MSSQL) using a TADOConnection. I am using TADOStoredProc to access my stored procedure. My stored procedure returns 2 columns, a column full of server names, and a 2nd column full of users on the serve...
How can I detect if a certain table exists in a given SQL database in Java? ...
I am migrating a MSSQL script to Oracle SQL and I can't figure out what one line in the script is doing. I am very new to SQL. CREATE TABLE HA_BACKUP_PROCESSES ( ID numeric (10, 0) NOT NULL , PROCESS_ID numeric (10, 0) NOT NULL , BACKUP_PROCESS_ID numeric (10, 0) NOT NULL , CONSTRAINT HA_BCK_PROC_PK PRIMARY KEY (ID)...
Table A Col1 Col2 101 102 101 103 102 104 104 105 Table B Col1 101 102 103 104 105 I want to take data from Table A and insert it into Table B as a Distinct value in one query so INSERT INTO TableB (Col1) (SELECT ...) Any ideas? ...
Can someone give me the skeleton body of an Instead of Insert Trigger for MSSQL. I am trying to test the largest value in an Purchase Order Column (which is an integer) and on insert it grabs the largest value increments that by one and inserts that as the Purchase Order ID. The column was not setup with an Auto Increment option so I am ...
I took an existing MySQL database, and set up a copy on a new host. The file size for some tables on the new host are 1-3% smaller than their counterpart files on the old host. I am curious why that is. My guess is, the old host's files have grown over time, and within the b-tree structure for that file, there is more fragmentation. ...