table udf - Page 312 - Developer IT

Cannot disable index during PL/SQL procedure

- by nw

I've written a PL/SQL procedure that would benefit if indexes were first disabled, then rebuilt upon completion. An existing thread suggests this approach: alter session set skip_unusable_indexes = true; alter index your_index unusable; [do import] alter index your_index rebuild; However, I get the following error on the first alter index statement: SQL Error: ORA-14048: a partition maintenance operation may not be combined with other operations ORA-06512: [...] 14048. 00000 - "a partition maintenance operation may not be combined with other operations" *Cause: ALTER TABLE or ALTER INDEX statement attempted to combine a partition maintenance operation (e.g. MOVE PARTITION) with some other operation (e.g. ADD PARTITION or PCTFREE which is illegal *Action: Ensure that a partition maintenance operation is the sole operation specified in ALTER TABLE or ALTER INDEX statement; operations other than those dealing with partitions, default attributes of partitioned tables/indices or specifying that a table be renamed (ALTER TABLE RENAME) may be combined at will The problem index is defined so: CREATE INDEX A11_IX1 ON STREETS ("SHAPE") INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=890,8010,72090 ST_SRID=2'); This is a custom index type from a 3rd-party vendor, and it causes chronic performance degradation during high-volume update/insert/delete operations. Any suggestions on how to work around this error? By the way, this error only occurs within a PL/SQL block.

Read the article

Performance of stored proc when updating columns selectively based on parameters?

- by kprobst

I'm trying to figure out if this is relatively well-performing T-SQL (this is SQL Server 2008). I need to create a stored procedure that updates a table. The proc accepts as many parameters as there are columns in the table, and with the exception of the PK column, they all default to NULL. The body of the procedure looks like this: CREATE PROCEDURE proc_repo_update @object_id bigint ,@object_name varchar(50) = NULL ,@object_type char(2) = NULL ,@object_weight int = NULL ,@owner_id int = NULL -- ...etc AS BEGIN update object_repo set object_name = ISNULL(@object_name, object_name) ,object_type = ISNULL(@object_type, object_type) ,object_weight = ISNULL(@object_weight, object_weight) ,owner_id = ISNULL(@owner_id, owner_id) -- ...etc where object_id = @object_id return @@ROWCOUNT END So basically: Update a column only if its corresponding parameter was provided, and leave the rest alone. This works well enough, but as the ISNULL call will return the value of the column if the received parameter was null, will SQL Server optimize this somehow? This might be a performance bottleneck on the application where the table might be updated heavily (insertion will be uncommon so the performance there is not a problem). So I'm trying to figure out what's the best way to do this. Is there a way to condition the column expressions with something like CASE WHEN or something? The table will be indexed up the wazoo as well for read performance. Is this the best approach? My alternative at this point is to create the UPDATE expression in code (e.g. inline SQL) and execute it against the server. This would solve my doubts about performance, but I'd rather leave this in a stored proc if possible.

Read the article

Having to insert a record, then update the same record warrants 1:1 relationship design?

- by dianovich

Let's say an Order has many Line items and we're storing the total cost of an order (based on the sum of prices on order lines) in the orders table. -------------- orders -------------- id ref total_cost -------------- -------------- lines -------------- id order_id price -------------- In a simple application, the order and line are created during the same step of the checkout process. So this means INSERT INTO orders .... -- Get ID of inserted order record INSERT into lines VALUES(null, order_id, ...), ... where we get the order ID after creating the order record. The problem I'm having is trying to figure out the best way to store the total cost of an order. I don't want to have to create an order create lines on an order calculate cost on order based on lines then update record created in 1. in orders table This would mean a nullable total_cost field on orders for starters... My solution thus far is to have an order_totals table with a 1:1 relationship to the orders table. But I think it's redundant. Ideally, since everything required to calculate total costs (lines on an order) is in the database, I would work out the value every time I need it, but this is very expensive. What are your thoughts?

Read the article

Populate JOIN into a list in one database query

- by axio

I am trying to get the records from the 'many' table of a one-to-many relationship and add them as a list to the relevant record from the 'one' table. I am also trying to do this in a single database request. Code derived from http://stackoverflow.com/questions/1580199/linq-to-sql-populate-join-result-into-a-list almost achieves the intended result, but makes one database request per entry in the 'one' table which is unacceptable. That failing code is here: var res = from variable in _dc.GetTable<VARIABLE>() select new { x = variable, y = variable.VARIABLE_VALUEs }; However if I do a similar query but loop through all the results, then only a single database request is made. This code achieves all goals: var res = from variable in _dc.GetTable<VARIABLE>() select variable; List<GDO.Variable> output = new List<GDO.Variable>(); foreach (var v2 in res) { List<GDO.VariableValue> values = new List<GDO.VariableValue>(); foreach (var vv in v2.VARIABLE_VALUEs) { values.Add(VariableValue.EntityToGDO(vv)); } output.Add(EntityToGDO(v2)); output[output.Count - 1].VariableValues = values; } However the latter code is ugly as hell, and it really feels like something that should be do-able in a single linq query. So, how can this be done in a single linq query that makes only a single database query? In both cases the table is set to preload using the following code: _dc = _db.CreateLinqDataContext(); var loadOptions = new DataLoadOptions(); loadOptions.LoadWith<VARIABLE>(v => v.VARIABLE_VALUEs); _dc.LoadOptions = loadOptions; I am using .NET 3.5, and the database back-end was generated using SqlMetal.

Read the article

Using an empty column as a divider in a JTable.

- by Catalina Island

I'm trying to use an empty column as a divider between pairs of columns in a JTable. Here's a picture and code for what I have so far. I know I can change the look using a custom TableCellRenderer. Before I go down that road, is there a better way to do this? Any ideas appreciated. import javax.swing.*; import javax.swing.table.*; public class TablePanel extends JPanel { public static void main(String[] args) { SwingUtilities.invokeLater(new Runnable() { @Override public void run() { JFrame f = new JFrame("TablePanel"); f.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE); f.add(new TablePanel()); f.pack(); f.setVisible(true); } }); } public TablePanel() { TableModel dataModel = new MyModel(); JTable table = new JTable(dataModel); table.getColumnModel().getColumn(MyModel.DIVIDER).setMaxWidth(0); JScrollPane jsp = new JScrollPane(table); jsp.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS); this.add(jsp); } private static class MyModel extends AbstractTableModel { private static final int DIVIDER = 2; private final String[] names = { "A1", "A2", "", "B1", "B2" }; @Override public int getRowCount() { return 32; } @Override public int getColumnCount() { return names.length; } @Override public String getColumnName(int col) { if (col == DIVIDER) return ""; return names[col]; } @Override public Object getValueAt(int row, int col) { if (col == DIVIDER) return ""; return (row + 1) / 10.0; } @Override public Class<?> getColumnClass(int col) { if (col == DIVIDER) return String.class; return Number.class; } } }

Read the article

t-sql help with recursive sort of query

- by stackoverflowuser

Hi Based on the following table ID Path --------------------------------------- 1 \\Root 2 \\Root\Node0 3 \\Root\Node0\Node1 4 \\Root\Node0\Node2 5 \\Root\Node3 6 \\Root\Node3\Node4 7 \\Root\Node5 ... N \\Root\Node5\Node6\Node7\Node8\Node9\Node10 so on... There are around 1000 rows in this table. I want to display individual node in seperate columns. Maximum columns to be displayed 5 (i.e. node till 5 level deep). So the output will look as below ID Path Level 0 Level 1 Level 2 Level 3 Level 4 Level 5 ---------------------------------------------------------------------------------------- 1 \\Root Root Null Null Null Null Null 2 \\Root\Node0 Root Node 0 Null Null Null Null 3 \\Root\Node0\Node1 Root Node 0 Node 1 Null Null Null 4 \\Root\Node0\Node2 Root Node 0 Node 2 Null Null Null 5 \\Root\Node3 Root Node 3 Null Null Null Null 6 \\Root\Node3\Node4 Root Node 3 Node 4 Null Null Null 7 \\Root\Node5 Root Node 5 Null Null Null Null ... N (see in above table) Root Node 5 Node 6 Node 7 Node 8 Node 9 The only way i can think of is to open a cursor, loop through each row and perform string split, just fetch the first 5 nodes and then insert into a temp table. Pls. suggest. Thanks

Read the article

Custom ADO.NET provider to intercept and modify sql queries.

- by Faisal

Our client has an application that stores blobs in database which has now grown enough to impact the performance of SQL Server. To overcome this issue, we are planning to offload all blobs to file system and leave the path of file in a new column in user table. Like if user has a table docs with columns id, name and content (blob); we would ask him to add a new column 'filepath' in this table. Our client is willing to make this change in this database. But when it comes to changing the sql queries to read and write into this table, they are not ready to accep this. Actually, they don't want any change that results in recompilation and deployment. Now we are planning to write a custom ADO.NET provider that will intercept the select queries add a column 'filepath' at the end of the select statement retieve the result set and modify the 'content' column value based on 'filepath' value Is there any use case that you think will certainly fail with this approach? I know this sounds dirty but do we have a better way?

Read the article

Why trigger query fails in the sqlite with Qt?

- by dexterous_stranger

I am a beginner in the SQL. I am using SQLite, Qt - on embedded systems. I want to put a trigger here. The trigger is that whenever the primary key Id is greater than 32145, then channelNum=101 should be set. I want to set the attrib name - text also, but I got the compilation issue. I believe that the setting of trigger is the part of DDL - Data definition language. Please let me know that if I am wrong here. Here is my create db code. I get the sql query error. Also please do suggest how to set the text attrib = "COmedy". /** associate db with query **/ QSqlQuery query ( m_demo_db ); /** Foreign keys are disabled by default in sqlite **/ /** Here is the pragma to turn them on first **/ query.exec("PRAGMA foreign_keys = ON;"); if ( false == query.exec()) { qDebug()<<"Pragma failed"; } /** Create Table for storing user preference LCN for DTT **/ qDebug()<<"Create Table postcode.db"; query.prepare(" CREATE TABLE dttServiceList (Id INTEGER PRIMARY KEY, attrib varchar(20), channelNum integer )" ); if ( false == query.exec()) { qDebug()<<"Create dttServiceList table failed"; } /** Try placing trigger here **/ triggerQuery = "CREATE TRIGGER upd_check BEFORE INSERT ON dttServiceList \ FOR EACH ROW \ BEGIN \ IF Id > 32145 THEN SET channelNum=101; \ END IF; \ END; "; query.prepare(triggerQuery); if ( false == query.exec()) { qDebug()<<"Trigger failed !!"; qDebug() << query.lastError(); } Also, how to set the text name in the trigger - I want to SET attrib = "Comedy".

Read the article

Javascript access object variables from functions

- by Parhs

Read the article

Sorting by some field and fetching whole tree from DB

- by Niaxon

Hello everyone, I am trying to do file browser in a tree form and have a problem to sort it somehow. I use PHP and MySQL for that. I've created mixed (nested set + adjacency) table 'element' with the following fields: element_id, left_key, right_key, level, parent_id, element_name, element_type (enum: 'folder','file'), element_size. Let's not discuss right now that it is better to move information about element (name, type, size) into other table. Function to scan specified directory and fill table work correctly. Noteworthy, i am adding elements to tree in specific order: folders first and only files. After that i can easily fetch and display whole table on the page using simple query: SELECT * FROM element WHERE 1=1 ORDER BY left_key With the result of that query and another function i can generate correct html code (<ul><li>... and so on). Now back to the question (finally, huh?). I am struggling to add sorting functionality. For example i want to order my result by size. Here i need to keep in my mind whole hierarchy of tree and rule: folders first, files later. I believe i can do that by generating in PHP recursive query: SELECT * FROM element WHERE parent_id = {$parentId} ORDER BY element_type (so folders would be first), size (or name for example) After that for each result which is folder i will send another query to get it's content. Also it's possible to fetch whole tree by left_key and after that sort it in PHP as array but i guess that would be worse :) I wonder if there is better and more efficient way to do such thing?

Read the article

Laravel4: Checking many-to-many relationship attribute even when it does not exist

- by Simo A.

This is my first time with Laravel and also Stackoverflow... I am developing a simple course management system. The main objects are User and Course, with a many-to-many relationship between them. The pivot table has an additional attribute, participant_role, which defines whether the user is a student or an instructor within that particular course. class Course extends Eloquent { public function users() { return $this->belongsToMany('User')->withPivot('participant_role')->withTimestamps(); } } However, there is an additional role, system admin, which can be defined on the User object. And this brings me mucho headache. In my blade view I have the following: @if ($course->pivot->participant_role == 'instructor') { // do something here... } This works fine when the user has been assigned to that particular $course and there is an entry for this user-course combination in the pivot table. However, the problem is the system administrator, who also should be able to access course details. The if-clause above gives Trying to get property of non-object error, apparently because there is no entry in the pivot table for system administrator (as the role is defined within the User object). I could probably solve the problem by using some off-the-shelf bundle for handling role-based permissions. Or I could (but don't want to) do something like this with two internal if-clauses: if (!empty($course->pivot)) { if ($course->pivot->participant_role == 'instructor') { // do something... } } Other options (suggested in partially similar entries in Stackoverflow) would include 1) reading all the pivot table entries to an array, and use in_array to check if a role exists, or even 2) SQL left join to do this on database level. However, ultimately I am looking for a compact one-line solution? Can I do anything similar to this, which unfortunately does not seem to work? if (! empty ($course->pivot->participant_role == 'instructor') ) { // do something... } The shorter the answer, the better :-). Thanks a lot!

Read the article

ASP.NET DynamicData: Whats happening during an update?

- by Jens A.

I am using ASP.NET DynamicData (based on LINQ to SQL) on my site for basic scaffolding. On one table I have added additional properties, that are not stored in the table, but are retrieved from somewhere else. (Profile information for a user account, in this case). They are displayed just fine, but when editing these values and pressing "Update", they are not changed. Here's what the properties look like, the table is the standard aspnet_Users table: public String Address { get { UserProfile profile = UserProfile.GetUserProfile(UserName); return profile.Address; } set { UserProfile profile = UserProfile.GetUserProfile(UserName); profile.Address = value; profile.Save(); } } When I fired up the debugger, I've noticed that for each update the set accessor is called three times. Once with the new value, but on a newly created instance of user, then once with the old value, again on an new instance, and finally with the old value on the existing instance. Wondering a bit, I checked with the properties created by the designer, and they, too, are called three times in (almost) the same fashion. The only difference is, that the last call contains the new value for the property. I am a bit stumped here. Why three times, and why are my new properties behaving differently? I'd be grateful for any help on that matter! =)

Read the article

Checking if user owns file before deleting it

- by Martin Hoe

I'm building an API for my site that allows users to delete the files they upload. Obviously, I want to check if the user owns that file before they delete it through the API. I have a files table and a users table, here's the schema: f_id, s_id, u_id, name, size, uploaded u_id, username, password, email, activated, activation_code u_id is a foreign key. The u_id field in the files table points to the u_id in the users table. Given the users username, I want to find the users u_id, and then check if they own the file through the file ID (f_id). I wrote this SQL: $sql = 'SELECT u.username FROM `users` u JOIN `files` f ON u.u_id = f.u_id WHERE f_id = ? AND u.u_id = ? LIMIT 1'; I'm assuming that'd work if I was given the users u_id in the API request, but alas I'm given only their username. How can I modify that SQL to find their user ID and use that? Thanks. Edit: Alright I've got this query but it's always returning an empty result set even though both the file ID and username exist. SELECT u.username FROM `users` u JOIN `files` f ON u.u_id = f.u_id WHERE f.f_id = ? AND u.username = ? LIMIT 1

Read the article

Help converting subquery to query with joins

- by Tim

I'm stuck on a query with a join. The client's site is running mysql4, so a subquery isn't an option. My attempts to rewrite using a join aren't going too well. I need to select all of the contractors listed in the contractors table who are not in the contractors2label table with a given label ID & county ID. Yet, they might be listed in contractors2label with other label and county IDs. Table: contractors cID (primary, autonumber) company (varchar) ...etc... Table: contractors2label cID labelID countyID psID This query with a subquery works: SELECT company, contractors.cID FROM contractors WHERE contractors.complete = 1 AND contractors.archived = 0 AND contractors.cID NOT IN ( SELECT contractors2label.cID FROM contractors2label WHERE labelID <> 1 AND countyID <> 1 ) I thought this query with a join would be the equivalent, but it returns no results. A manual scan of the data shows I should get 34 rows, which is what the subquery above returns. SELECT company, contractors.cID FROM contractors LEFT OUTER JOIN contractors2label ON contractors.cID = contractors2label.cID WHERE contractors.complete = 1 AND contractors.archived = 0 AND contractors2label.labelID <> 1 AND contractors2label.countyID <> 1 AND contractors2label.cID IS NULL

Read the article

Risky Business with LINQ to SQL and OR Designer?

- by Toadmyster

I have two tables with a one to many relationship in SQL 2008. The first table (BBD) PK | BBDataID | int | Floor_Qty | tinyint | Construct_Year | char(4) | etc, etc describes the data common to all buildings and the second (BBDCerts) PK | BBDCertsID | int | BBDataID | int | Certification_Type | varchar(20) | etc, etc is a collection of certifications for a particular building. Thus, the primary key in BBD (BBDataID) is mapped to the corresponding field in BBDCerts via an FK relationship, but BBDCertsID is the second table's primary key and BBDataID is not because it will not be unique. My problem is that I want to be able to use the OR generated data context to get at the list of certs when I access a particular record in the BBD table. For instance: Dim vals = (From q in db.BBD Where q.BBDataID = x Select q.Floor_Qty, q.Construct_Year, q.BBDCerts).SingleOrDefault and later be able to access a particular certification like this: vals.BBDCerts.Certification_Type.First Now, the automatic associations created when the SQL tables are dropped on the design surface don't generate the EntityRef associations that are needed to access the other table using the dot notation. So, I have to use the OR designer to make the BBDCerts BBDataID a primary key (this doesn't affect the actual database), and then manually change the association properties to the appropriate OneToMany settings. There might be a better way to approach this solution but my question is, is the way I've done it safe? I've done a barrage of tests and the correct cert is referenced or updated every time. Frankly, the whole thing makes me nervous.

Read the article

DIV inside TD to make it appear correctly

- by Daniel Svensson

Hi, I have a <table> generated from code-behind and now facing a problem. In one of the TD i need to have a DIV that is setup with JQuery so that when i click a link the DIV slideToggles. Now i need the TD belonging to that TR not to expand the TR. To solve this i have used an old trick that is to place the JQuery DIV inside another surrounding DIV with height 1px and make the TR not expanding with the heigth of the DIV that slides out. In IE the sliding DIV is partially under the table and in Firefox the DIV appears over the table but it's trasparent, the text from the data in the table shows thru. I have tried to alter the Z-index in various ways but it's no good. Anyone that has an idea or alternatively solution that has worked for them. HtmlGenericControl containerDiv = new HtmlGenericControl("div"); containerDiv.ID = "containerDiv"; containerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.Width, "100%"); containerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.Height, "1px"); containerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.ZIndex, "999"); HtmlGenericControl innerDiv = new HtmlGenericControl("div"); innerDiv.ID = System.Guid.NewGuid().ToString() + "_annualDiv"; inner.Style.Add(System.Web.UI.HtmlTextWriterStyle.Width, "100%"); innerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.Height, "300px"); innerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.ZIndex, "1000"); innerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.BorderStyle, "solid"); innerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.BorderColor, "Black"); innerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.BorderWidth, "1px"); innerDiv.Style.Add(System.Web.UI.HtmlTextWriterStyle.BackgroundColor, "white"); innerDiv.InnerHtml = "Here is a list of links coming later"; conDiv.Controls.Add(innerDiv);

Read the article

sqlite database help

- by mamila

Hi all i have some questions regarding database in android. 1. in my project, I would like to create a database with single table. i would also like to insert data on this table from word or excel document. is it possible? if so please how can i do it? some code snippets would be of great help. 2. i have four different activities which i would like them to interact with this single table. the first is the main activity and its purpose is just to launch the other three activities according to user choice. so it has no interaction with the db. but the other three activities will read from the table whenever called. can you please tell me how to call the dbhelper on these activities. am kind of unable to do this.and am currently creating one db per each activity which is not the optimal way. any help is really appreciated and would be better if very soon tahnks

Read the article

SQL Scenario of allocating ids to user

- by Enjoy coding

Hi, I have an sql scenario as follows which I have been trying to improve. There is a table 'Returns' which is having ids of the returned goods against a shop for an item. Its structure is as below. Returns ------------------------- Return ID | Shop | Item ------------------------- 1 Shop1 Item1 2 Shop1 Item1 3 Shop1 Item1 4 Shop1 Item1 5 Shop1 Item1 There is one more table Supplier with Shop, supplier and Item as shown below. Supplier --------------------------------- Supplier | Shop | Item | Volume --------------------------------- supp1 Shop1 Item1 20% supp2 Shop1 Item1 80% Now as you see supp1 is supplying 20 % of total item1 volume and supp2 is supplying 80% of Item1 to shop1. And there were 5 return of items against the same Item1 for same Shop1. Now I need to allocate any four return IDs to Supp1 and remaining one return Id to supp2. This allocation of numbers is based on the ratio of the supplied volume percentage of the supplier. This allocation varies depending on the ratio of volume of supplied items. Now I have tried a method of using RANKs as shown below by use of temp tables. temp table 1 will have Shop, Return Id, Item, Total count of return IDs and Rank of the return id. temp table 2 will have shop, Supplier, Item and his proportion and rank of proportion. Now I am facing the difficulty in allocating top return ids to top supplier as illustrated above. As SQL doesnt have loops how can I achieve this. I have been tying several ways of doing this. Please advice. My environment is Teradata (ANSI SQL is enough). Thanks in advance.

Read the article

Creating the same event for 2 different elements in jQuery (not for each one, for both!)

- by danfromisrael

Hey guys, I'd like to create a toggle event for 2 different TD's in my table row. the event should show / hide the next table row. <table> <tr> <td>1</td> <td>2</td> <td>3</td> <td>4</td> <td>5</td> <td class="clickable1">6</td> <td class="clickable2">7</td> </tr> <tr><td>this row should be toggled between show/hide once one of the clickable TDs were clicked</td></tr> </table> here's the code i tried to apply but it has applied each one of the classes the event: $('.clickable1,.clickable2').toggle(function() { $(this).parent() .next('tr') .show(); }, function() { $(this).parent() .next('tr') .hide(); }); One more thing: i'm applying on each TR a css hover psuedo class. how can i make the two TRs to be highlighted (like hover effect on two of them) ? Thanks in advanced! :-Dan

Read the article

HABTM selection seemingly ignores joinTable

- by TheCapn

I'm attempting to do a HABTM relationship between a Users table and Groups table. The problem is, that I when I issue this call: $this->User->Group->find('list'); The query that is issued is: SELECT [Group].[id] AS [Group__id], [Group].[name] AS [Group__name] FROM [groups] AS [Group] WHERE 1 = 1 I can only assume at this point that I have defined my relationship wrong as I would expect behavior to use the groups_users table that is defined on the database as per convention. My relationships: class User extends AppModel { var $name = 'User'; //...snip... var $hasAndBelongsToMany = array( 'Group' => array( 'className' => 'Group', 'foreignKey' => 'user_id', 'associationForeignKey' => 'group_id', 'joinTable' => 'groups_users', 'unique' => true, ) ); //...snip... } class Group extends AppModel { var $name = 'Group'; var $hasAndBelongsToMany = array ( 'User' => array( 'className' => 'User', 'foreignKey' => 'group_id', 'associationForeignKey' => 'user_id', 'joinTable' => 'groups_users', 'unique' => true, )); } Is my understanding of HABTM wrong? How would I implement this Many to Many relationship where I can use CakePHP to query the groups_users table such that a list of groups the currently authenticated user is associated with is returned?

Read the article

Is there a set based solution for this problem?

- by NYSystemsAnalyst

We have a table set up as follows: |ID|EmployeeID|Date |Category |Hours| |1 |1 |1/1/2010 |Vacation Earned|2.0 | |2 |2 |2/12/2010|Vacation Earned|3.0 | |3 |1 |2/4/2010 |Vacation Used |1.0 | |4 |2 |5/18/2010|Vacation Earned|2.0 | |5 |2 |7/23/2010|Vacation Used |4.0 | The business rules are: Vacation balance is calculated by vacation earned minus vacation used. Vacation used is always applied against the oldest vacation earned amount first. We need to return the rows for Vacation Earned that have not been offset by vacation used. If vacation used has only offset part of a vacation earned record, we need to return that record showing the difference. For example, using the above table, the result set would look like: |ID|EmployeeID|Date |Category |Hours| |1 |1 |1/1/2010 |Vacation Earned|1.0 | |4 |2 |5/18/2010|Vacation Earned|1.0 | Note that record 2 was eliminated because it was completely offset by used time, but records 1 and 4 were only partially used, so they were calculated and returned as such. The only way we have thought of to do this is to get all of the vacation earned records in a temporary table. Then, get the total vacation used and loop through the temporary table, deleting the oldest record and subtracting that value from the total vacation used until the total vacation used is zero. We could clean it up for when the remaining vacation used is only part of the oldest vacation earned record. This would leave us with just the outstanding vacation earned records. This works, but it is very inefficient and performs poorly. Also, the performance will just degrade over time as more and more records are added. Are there any suggestions for a better solution, preferable set based? If not, we'll just have to go with this.

Read the article

remove row on click on specific td of that row by jquery

- by I Like PHP

i have an table <table class="oldService"> <thead> <th>name</th> <th>age</th> <th>action</th> </thead> <tbody> <?php foreach($array as $k=>$v){ ?> <tr> <td><?php echo $k[name] ?></td> <td><?php echo $k[age]?></td> <td id="<?php $k[id]" class="delme">X</td> </tr> <?php } ?> </tbody> <table> now i want to delete any row by clicking on X of each row except first and last row, and also need to confirm before deletion. i used below jquery <script type="text/javascript"> jQuery(document).ready(function(){ jQuery('table.oldService>tbody tr').not(':first').not(':last').click(function(){ if(confirm('want to delete!')){ jQuery(jQuery(this).addClass('del').fadeTo(400, 0, function() { jQuery(this).remove()})); jQuery.get('deleteService.php', {id:jQuery(this).attr('id')}); } else return false;}); }); </script> this is working perfect,but it execute by click on that row( means any td), i want that this event only occour when user click on X(third td) . please suggest me how to modify this jquery so that the event occur on click of X.

Read the article

Static selection and Ruby on Rails objects

- by Dave

Hi all- I have a simple problem, but am having trouble wrapping my head around it. I have an video object that should have one or more "genres". This list of genres should be prepopulated and then the user should just select one or more using autocomplete or some such. Here is the question: Is it worth creating a table with genres for the static selection? Or should it just be included in the presentation layer? If there is a static table, how do we name it correctly. I envision something like this class Video < ActiveRecord::Base has_many :genres ... end class Genre < ... belongs_to :video ... end But then we get a table called genre, that basically maps all the selected genres to their parent videos. There would need to be some static table to reference the static genres. Is this the best way to do it? Sorry if this was rambl-y a little stream of conciousness. Thanks!

Read the article

SQL Server insert performance with and without primary key

- by Eric

Summary: I have a table populated via the following: insert into the_table (...) select ... from some_other_table Running the above query with no primary key on the_table is ~15x faster than running it with a primary key, and I don't understand why. The details: I think this is best explained through code examples. I have a table: create table the_table ( a int not null, b smallint not null, c tinyint not null ); If I add a primary key, this insert query is terribly slow: alter table the_table add constraint PK_the_table primary key(a, b); -- Inserting ~880,000 rows insert into the_table (a,b,c) select a,b,c from some_view; Without the primary key, the same insert query is about 15x faster. However, after populating the_table without a primary key, I can add the primary key constraint and that only takes a few seconds. This one really makes no sense to me. More info: The estimated execution plan shows 0% total query time spent on the clustered index insert SQL Server 2008 R2 Developer edition, 10.50.1600 Any ideas?

Read the article

Database PK-FK design for future-effective-date entries?

- by Scott Balmos

Ultimately I'm going to convert this into a Hibernate/JPA design. But I wanted to start out from purely a database perspective. We have various tables containing data that is future-effective-dated. Take an employee table with the following pseudo-definition: employee id INT AUTO_INCREMENT ... data fields ... effectiveFrom DATE effectiveTo DATE employee_reviews id INT AUTO_INCREMENT employee_id INT FK employee.id Very simplistic. But let's say Employee A has id = 1, effectiveFrom = 1/1/2011, effectiveTo = 1/1/2099. That employee is going to be changing jobs in the future, which would in theory create a new row, id = 2 with effectiveFrom = 7/1/2011, effectiveTo = 1/1/2099, and id = 1's effectiveTo updated to 6/30/2011. But now, my program would have to go through any table that has a FK relationship to employee every night, and update those FK to reference the newly-effective employee entry. I have seen various postings in both pure SQL and Hibernate forums that I should have a separate employee_versions table, which is where I would have all effective-dated data stored, resulting in the updated pseudo-definition below: employee id INT AUTO_INCREMENT employee_versions id INT AUTO_INCREMENT employee_id INT FK employee.id ... data fields ... effectiveFrom DATE effectiveTo DATE employee_reviews id INT AUTO_INCREMENT employee_id INT FK employee.id Then to get any actual data, one would have to actually select from employee_versions with the proper employee_id and date range. This feels rather unnatural to have this secondary "versions" table for each versioned entity. Anyone have any opinions, suggestions from your own prior work, etc? Like I said, I'm taking this purely from a general SQL design standpoint first before layering in Hibernate on top. Thanks!

Search Results

Search found 24784 results on 992 pages for 'table udf'.

Page 312/992 | < Previous Page | 308 309 310 311 312 313 314 315 316 317 318 319 | Next Page >

- by nw

- by kprobst

- by dianovich

- by axio

- by Catalina Island

- by stackoverflowuser

- by Faisal

- by dexterous_stranger

- by Parhs

- by Niaxon

- by Simo A.

- by Jens A.

- by Martin Hoe

- by Tim

- by Toadmyster

- by Daniel Svensson

- by mamila

- by Enjoy coding

- by danfromisrael

- by TheCapn

- by NYSystemsAnalyst

- by I Like PHP

- by Dave

- by Eric

- by Scott Balmos

< Previous Page | 308 309 310 311 312 313 314 315 316 317 318 319 | Next Page >