Search Results

Search found 36773 results on 1471 pages for 'sql statement syntax'.

Page 663/1471 | < Previous Page | 659 660 661 662 663 664 665 666 667 668 669 670  | Next Page >

• Opinions on sensor / reading / alert database design

  - by Mark

  I've asked a few questions lately regarding database design, probably too many ;-) However I beleive I'm slowly getting to the heart of the matter with my design and am slowly boiling it down. I'm still wrestling with a couple of decisions regarding how "alerts" are stored in the database. In this system, an alert is an entity that must be acknowledged, acted upon, etc. Initially I related readings to alerts like this (very cut down) : - [Location] LocationId [Sensor] SensorId LocationId UpperLimitValue LowerLimitValue [SensorReading] SensorReadingId Value Status Timestamp [SensorAlert] SensorAlertId [SensorAlertReading] SensorAlertId SensorReadingId The last table is associating readings with the alert, because it is the reading that dictate that the sensor is in alert or not. The problem with this design is that it allows readings from many sensors to be associated with a single alert - whereas each alert is for a single sensor only and should only have readings for that sensor associated with it (should I be bothered that the DB allows this though?). I thought to simplify things, why even bother with the SensorAlertReading table? Instead I could do this: [Location] LocationId [Sensor] SensorId LocationId [SensorReading] SensorReadingId SensorId Value Status Timestamp [SensorAlert] SensorAlertId SensorId Timestamp [SensorAlertEnd] SensorAlertId Timestamp Basically I'm not associating readings with the alert now - instead I just know that an alert was active between a start and end time for a particular sensor, and if I want to look up the readings for that alert I can do. Obviously the downside is I no longer have any constraint stopping me deleting readings that occurred during the alert, but I'm not sure that the constraint is neccessary. Now looking in from the outside as a developer / DBA, would that make you want to be sick or does it seem reasonable? Is there perhaps another way of doing this that I may be missing? Thanks. EDIT: Here's another idea - it works in a different way. It stores each sensor state change, going from normal to alert in a table, and then readings are simply associated with a particular state. This seems to solve all the problems - what d'ya think? (the only thing I'm not sure about is calling the table "SensorState", I can't help think there's a better name (maybe SensorReadingGroup?) : - [Location] LocationId [Sensor] SensorId LocationId [SensorState] SensorStateId SensorId Timestamp Status IsInAlert [SensorReading] SensorReadingId SensorStateId Value Timestamp There must be an elegant solution to this!

  Read the article

• Sorting Anonymous Types

  - by strobaek

  I have a question of how to sort an anonymous type. Using Linq2SQL I have the following query, which returns a list of submissions: var submissions = EventSubmissions .Where(s => s.EventId.Equals(eventId)); Consider the following interface (somewhat simplyfied): public interface IQuerySorter { IOrderedQueryable Sort(IQueryable query); IOrderedQueryable<T> Sort<T, U>(IQueryable<T> query, Expression<Func<T,U>> selector); ... } Using this interface allows me to implement a number of 'sorters', e.g. on Date, Rating or whether or not a submission has been nominated (for voting). sortedQuery = sorter.Sort(submissions) So far so good. A submission can be made "votable". I get the number of votes a nominated submission may have using the following query: var withVoteCount = submissions .Select(s => new {NumberOfVotes = s.Votes.Count(), Submission = s}); I would like to sort this new query by NumberOfVotes using my "general" sorter class, but run into the problem that the anonymous type/member does not seem to live outside the repository-method, hence I am unable to sort on it. Any input would be greatly appreciated.

  Read the article

• SubSonic missing stored procedures in StoredProcedures.cs when generated with SubCommander sonic.exe

  - by Mark

  We have been using SubSonic to generate our DAL with a lot of success on VS2005 and SubSonic Tools 2.0.3. SubSonic Tools do not work on VS2008 (as far as we can work out) so we have tried to use SubCommander\sonic.exe and are now hitting some problems. When we regenerate the project using SubCommander\sonic.exe and try to compile we get some errors reporting missing members (which should have been automatically generated based on the stored procedures we have). On closer inspection it looks like my StoredProcedures.cs file is missing some (not all) automatically generated methods for my classes. As an example, I have 2 procs: [dbo]._ClassA_Func1 [dbo]._ClassA_Func2 Only one of these is being generated in the StoredProcedures.cs file. These methods generate fine using the SubSonic Tools plugin. We have tried now with versions 2.1 and 2.2 of SubSonic with the same issue. We are still on .NET 2.0 so cannot use SubSonic 3.0. I have checked the permissions of both procs using fn_my_permissions and they seem identical. Does anyone have any ideas on what I can check? Thanks -- Mark

  Read the article

• ASP.NET MVC pagination problem????

  - by MD_Oppenheimer

  OK, This is starting to get mildly irritating. I tried to implement Twitter style paging using ASP.NET MVC and JQuery my problem is that when not using Request.IsAjaxRequest() (for users with javascript turned off) it works fine, obviously posting back the whole page. when I run the code for Request.IsAjaxRequest(), it skips entries, and does not return result in order. this is the code I have: public ActionResult Index(int? startRow) { StatusUpdatesRepository statusUpdatesRepository = new StatusUpdatesRepository(); if (!startRow.HasValue) startRow = Globals.Settings.StatusUpdatesSection.StatusUpdateCount;//5 Default starting row //Retrieve the first page with a page size of entryCount int totalItems; if (Request.IsAjaxRequest()) { IEnumerable<StatusUpdate> PagedEntries = statusUpdatesRepository.GetLastStatusUpdates(startRow.Value,Globals.Settings.StatusUpdatesSection.StatusUpdateCount, out totalItems); if (startRow < totalItems) AddMoreUrlToViewData(startRow.Value); return View("StatusUpdates", PagedEntries); } //Retrieve the first page with a page size of global setting // First run skip 0 take 5 IEnumerable<StatusUpdate> entries = statusUpdatesRepository.GetLastStatusUpdates(0,startRow.Value, out totalItems); if (startRow < totalItems) AddMoreUrlToViewData(startRow.Value); return View(entries); } private void AddMoreUrlToViewData(int entryCount) { ViewData["moreUrl"] = Url.Action("Index", "Home", new { startRow = entryCount + Globals.Settings.StatusUpdatesSection.StatusUpdateCount }); } My GetLastStatusUpdates function: public IQueryable GetLastStatusUpdates(int startRowIndex, int maximumRows,out int statusUpdatesCount ) { statusUpdatesCount = db.StatusUpdates.Count(); return db.StatusUpdates .Skip(startRowIndex) .Take(maximumRows) .OrderByDescending(s = s.AddedDate); } Really fresh out out of ideas as to why this is not working properly when responding to a Request.IsAjaxRequest(), ie when I turn of javascript in the browser, the code works perfectly, except I don't want to repost the whole page????

  Read the article

• Use a stored procedure parameter for unit parameter of DATE_SUB

  - by Mike

  I would like to know if it's possible to pass a parameter to a mysql stored procedure and use this parameter as the unit parameter of the DATE_SUB function. It seems that the unit parameter is a reserved word so I don't know if there's a type for unit. Exemple of what I'm trying to do : DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`%` PROCEDURE `test`(param1 unit) BEGIN select DATE_SUB(now(), INTERVAL 1 param1); END $$ DELIMITER ;

  Read the article

• LEFT OUTER JOIN SUM doubles problem

  - by Michael

  Hi I've got two tables: Table: Shopping shop_id shop_name shop_time 1 Brian 40 2 Brian 31 3 Tom 20 4 Brian 30 Table:bananas banana_id banana_amount banana_person 1 1 Brian 2 1 Brian I now want it to print: Name: Tom | Time: 20 | Bananas: 0 Name: Brian | Time: 101 | Bananas: 2 I used this code: $result = dbquery("SELECT tz.*, tt.*, SUM(shop_time) as shoptime, count(banana_amount) as bananas FROM shopping tt LEFT OUTER JOIN bananas tz ON tt.shop_name=tz.banana_person GROUP by banana_person LIMIT 40 "); while ($data5 = dbarray($result)) { echo 'Name: '.$data5["shop_name"].' | Time: '.$data5["shoptime"].' | Bananas: '.$data5["bananas"].'<br>'; } The problem is that I get this instead: Name: Tom | Time: 20 | Bananas: 0 Name: Brian | Time: 202 | Bananas: 6 I just don't know how to get around this.

  Read the article

• Fill WinForms DataGridView From Anonymous Linq Query

  - by mdvaldosta

  // From my form BindingSource bs = new BindingSource(); private void fillStudentGrid() { bs.DataSource = Admin.GetStudents(); dgViewStudents.DataSource = bs; } // From the Admin class public static List<Student> GetStudents() { DojoDBDataContext conn = new DojoDBDataContext(); var query = (from s in conn.Students select new Student { ID = s.ID, FirstName = s.FirstName, LastName = s.LastName, Belt = s.Belt }).ToList(); return query; } I'm trying to fill a datagridview control in Winforms, and I only want a few of the values. The code compiles, but throws a runtime error: Explicit construction of entity type 'DojoManagement.Student' in query is not allowed. Is there a way to get it working in this manner?

  Read the article

• Usage of oracle function sys connect by path

  - by Dave

  We have a problem that it seens not supported in oracle 10g what could be the cause?

  Read the article

• Solving Slow Query

  - by Chris

  We are installing a new forum (yaf) for our site. One of the stored procedures is extremely slow - in fact it always times out in the browser. If I run it in MSSMS it takes nearly 10 minutes to complete. Is there a way to find out what part of this query if taking so long? The Query: DECLARE @BoardID int DECLARE @UserID int DECLARE @CategoryID int = null DECLARE @ParentID int = null SET @BoardID = 1 SET @UserID = 2 select a.CategoryID, Category = a.Name, ForumID = b.ForumID, Forum = b.Name, Description, Topics = [dbo].[yaf_forum_topics](b.ForumID), Posts = [dbo].[yaf_forum_posts](b.ForumID), Subforums = [dbo].[yaf_forum_subforums](b.ForumID, @UserID), LastPosted = t.LastPosted, LastMessageID = t.LastMessageID, LastUserID = t.LastUserID, LastUser = IsNull(t.LastUserName,(select Name from [dbo].[yaf_User] x where x.UserID=t.LastUserID)), LastTopicID = t.TopicID, LastTopicName = t.Topic, b.Flags, Viewing = (select count(1) from [dbo].[yaf_Active] x JOIN [dbo].[yaf_User] usr ON x.UserID = usr.UserID where x.ForumID=b.ForumID AND usr.IsActiveExcluded = 0), b.RemoteURL, x.ReadAccess from [dbo].[yaf_Category] a join [dbo].[yaf_Forum] b on b.CategoryID=a.CategoryID join [dbo].[yaf_vaccess] x on x.ForumID=b.ForumID left outer join [dbo].[yaf_Topic] t ON t.TopicID = [dbo].[yaf_forum_lasttopic](b.ForumID,@UserID,b.LastTopicID,b.LastPosted) where a.BoardID = @BoardID and ((b.Flags & 2)=0 or x.ReadAccess<>0) and (@CategoryID is null or a.CategoryID=@CategoryID) and ((@ParentID is null and b.ParentID is null) or b.ParentID=@ParentID) and x.UserID = @UserID order by a.SortOrder, b.SortOrder IO Statistics: Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_Active'. Scan count 14, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_User'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_Topic'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_Category'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_Forum'. Scan count 0, logical reads 488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_UserGroup'. Scan count 231, logical reads 693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_ForumAccess'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_AccessMask'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'yaf_UserForum'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Client Statistics: Client Execution Time 11:54:01 Query Profile Statistics Number of INSERT, DELETE and UPDATE statements 0 0.0000 Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000 Number of SELECT statements 8 8.0000 Rows returned by SELECT statements 19 19.0000 Number of transactions 0 0.0000 Network Statistics Number of server roundtrips 3 3.0000 TDS packets sent from client 3 3.0000 TDS packets received from server 34 34.0000 Bytes sent from client 3166 3166.0000 Bytes received from server 128802 128802.0000 Time Statistics Client processing time 156478 156478.0000 Total execution time 572009 572009.0000 Wait time on server replies 415531 415531.0000 Execution Plan

  Read the article

• using securestring for a sql connection

  - by Rick

  Hi, I want to use a SecureString to hold a connection string for a database. But as soon as I set the SqlConnection object's ConnectionString property to the value of the securestring surely it will become visible to any other application that is able to read my application's memory? I have made the following assumptions: a) I am not able to instantiate a SqlConnection object outside of managed memory b) any string within managed memory can be read by an application such as Hawkeye

  Read the article

• Query that ignore the spaces.

  - by xRobot

  What's the best way to run a query so that spaces in the fields are ignored? For example the following queries.... SELECT * FROM mytable WHERE username = "JohnBobJones" SELECT * FROM mytable WHERE username = "John Bob Jones" . would find the following entries: John Bob Jones JohnBob Jones JohnBobJones . I am using php or python but I think this doesn't matter.

  Read the article

• Prevent full table scan for query with multiple where clauses

  - by Dave Jarvis

  A while ago I posted a message about optimizing a query in MySQL. I have since ported the data and query to PostgreSQL, but now PostgreSQL has the same problem. The solution in MySQL was to force the optimizer to not optimize using STRAIGHT_JOIN. PostgreSQL offers no such option. Here is the explain: Here is the query: SELECT avg(d.amount) AS amount, y.year FROM station s, station_district sd, year_ref y, month_ref m, daily d LEFT JOIN city c ON c.id = 10663 WHERE -- Find all the stations within a specific unit radius ... -- 6371.009 * SQRT( POW(RADIANS(c.latitude_decimal - s.latitude_decimal), 2) + (COS(RADIANS(c.latitude_decimal + s.latitude_decimal) / 2) * POW(RADIANS(c.longitude_decimal - s.longitude_decimal), 2)) ) <= 50 AND -- Ignore stations outside the given elevations -- s.elevation BETWEEN 0 AND 2000 AND sd.id = s.station_district_id AND -- Gather all known years for that station ... -- y.station_district_id = sd.id AND -- The data before 1900 is shaky; insufficient after 2009. -- y.year BETWEEN 1980 AND 2000 AND -- Filtered by all known months ... -- m.year_ref_id = y.id AND m.month = 12 AND -- Whittled down by category ... -- m.category_id = '001' AND -- Into the valid daily climate data. -- m.id = d.month_ref_id AND d.daily_flag_id <> 'M' GROUP BY y.year It appears as though PostgreSQL is looking at the DAILY table first, which is simply not the right way to go about this query as there are nearly 300 million rows. How do I force PostgreSQL to start at the CITY table? Thank you!

  Read the article

• DNN 5.2.3 Stored Procedures executing numerous times during page loads

  - by David Neale

  After tracing the DB activity from a DNN 5.2.3 site I noticed that there are numerous identical calls to the database whilst loading the home page for the first time (afterwards the caching works successfully). //Procedure : Number of executions exec dbo.aspnet_Membership_GetUserByName @ApplicationName=N'DotNetNuke',@UserName=N'MYDOMAIN\ME',@UpdateLastActivity=0,@CurrentTimeUtc='2010-03-24 10:04:15:223' : 22 exec dbo.GetPortalAliasByPortalID @PortalID=0 : 15 exec dbo.GetUserProfile @UserID=8 : 11 exec dbo.GetUser @PortalID=0,@UserID=8 : 10 exec dbo.GetDatabaseVersion : 2 exec dbo.GetUserCountByPortal @PortalId=0: 2 exec dbo.GetDesktopModules : 2 exec dbo.KB_XMod_Forms_List @PortalId=0 : 2 exec dbo.KB_XMod_Templates_List @PortalId=0,@TemplateType=-1 : 2 Why so many duplicates?

  Read the article

• Gridview buttonfield works LinkButton doesn't

  - by Karsten

  I've been fighting this problem for many hours now and could really use some help :-) This is the grid <asp:GridView ID="annonceView" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="dataSourceAnnoncer"> <Columns> <asp:BoundField DataField="Productname" HeaderText="Productname" /> <asp:buttonfield buttontype="Link" commandname="Delete" text="Delete"/> <asp:TemplateField HeaderText="Administration"> <ItemTemplate> <asp:LinkButton ID="lnkBtnDelete" runat="server" Text="Delete" CausesValidation="False" CommandName="Delete" OnClientClick="return confirm('Delete?')" /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:LinqDataSource ID="dataSourceAnnoncer" runat="server" ContextTypeName="Namespcae.TheContext" EnableDelete="True" TableName="Annoncer"> </asp:LinqDataSource> Clicking the buttonfield deletes the record just fine. Clicking the LinkButton doesn't work. I get a postback and the grid is shown as empty and no record is deleted. Seems like an empty databinding. I have tried to create a custom OnClick, OnCommand event for the LinkButton, but neither are fired. The OnRowCommand isn't fired either. I don't manually DataBind in the codebehind.

  Read the article

• Ado JobStore use!

  - by aleo

  well i'm new in Quartz i'm following this tutorial and i configured my scheduler instance and quartz to use this properties: properties["quartz.jobStore.lockHandler.type"] = "Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz"; properties["quartz.jobStore.driverDelegateType"] = "Quartz.Impl.AdoJobStore.SqlServerDelegate, Quartz"; properties["quartz.jobStore.dataSource"] = "default"; properties["quartz.dataSource.default.connectionString"] = "Server=loclahost;Initial Catalog=aleo;Persist Security Info=True;User ID=userid;Password=password"; properties["quartz.dataSource.default.provider"] = "SqlServer-20"; properties["quartz.jobStore.type"] = "Quartz.Impl.AdoJobStore.JobStoreTX, Quartz"; properties["quartz.jobStore.useProperties"] = "true"; properties["quartz.jobStore.tablePrefix"] = "QRTZ_"; ISchedulerFactory schedFact = new Quartz.Impl.StdSchedulerFactory(properties); IScheduler sched = schedFact.GetScheduler(); sched.Start(); but what's next? i am new on C# but if someone explain to can understand :) and my question are how i will add jobs and triggers and stuff to the database? i also created the tables given in the Database/tables folder that comes with Quartz API thanks.

  Read the article

• How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN

  - by jakenoble

  Hi I have three tables Post ID Name 1 'Something' 2 'Something else' 3 'One more' Comment ID PostId ProfileID Comment 1 1 1 'Hi my name is' 2 2 2 'I like cakes' 3 3 3 'I hate cakes' Profile ID Approved 1 1 2 0 3 1 I want to count the comments for a post where the profile for the comment is approved I can select the data from Post and then join a count from Comment fine. But this count should be dependent on if the Profile is approved or not. The results I am expecting is CommentCount PostId Count 1 1 2 0 3 1 Thanks for any help.

  Read the article

• Row Number for group by ?

  - by Damien Joe

  I have table structure like Category EmpName 1 Harry 1 John 1 Ford 2 James 2 Mark 2 Shane 3 Oliver 3 Ted I want results like Category EmpName RowNumber 1 Harry 1 1 John 2 1 Ford 3 2 James 1 2 Mark 2 2 Shane 3 3 Oliver 1 3 Ted 2 I am using db2 and row_number() is not working for different groups of records.

  Read the article

• Predicate problem in ToSelectList

  - by Stefanvds

  the ToSelectList method I have: public static IList<SelectListItem> ToSelectList<T>(this IEnumerable<T> itemsToMap, Func<T, string> textProperty, Func<T, string> valueProperty, Predicate<T> isSelected) { var result = new List<SelectListItem>(); foreach (var item in itemsToMap) { result.Add(new SelectListItem { Value = valueProperty(item), Text = textProperty(item), Selected = isSelected(item) }); } return result; } when I call this method here: public static List<SelectListItem> lesgeverList(int selectedID) { NASDataContext _db = new NASDataContext(); var lesg = (from l in _db.Lesgevers where l.LG_Naam != "leeg" orderby l.LG_Naam select l).ToSelectList(m => m.LG_Naam + " " + m.LG_Vnaam, m => m.LG_ID.ToString(), m => m.LG_ID == selectedID); return lesg.ToList(); } the selectlist I get has the selectedID as selected. now, when I want to have multiple selected items, I give a list of Lesgevers public static List<SelectListItem> lesgeverList(List<Lesgever> lg) { NASDataContext _db = new NASDataContext(); var test = (from l in _db.Lesgevers where l.LG_Naam != "leeg" && lg.Contains(l) orderby l.LG_Naam, l.LG_Vnaam select l).ToList(); var lesg = (from l in _db.Lesgevers where l.LG_Naam != "leeg" orderby l.LG_Naam, l.LG_Vnaam select l).ToSelectList(m => m.LG_Naam + " " + m.LG_Vnaam, m => m.LG_ID.ToString(), m => lg.Contains(m)); return lesg.ToList(); } the var test does return the Lesgevers that i have in the lg List, in my 'var lesg', there are no selectlistitem's selected at all. where is my mistake? :) how do I fix thix?

  Read the article

• Simple join gives unnecessary rows,How to get join properly with only matches in both tables

  - by LS

  Table 1 Field1 Field2 AA 20 AA 20 AB 12 AC 13 Table2 field3 field4 AA 20 AA 20 AC 13 AD 23 AW 21 output required: newfield field2 field4 AA 20 20 AA 20 20 AC 13 13 I used: select field1 as newfield, t1.field2,t2.field4 from table1 t1 join table2 t2 on t1.field1=t2.field3 This does not give the required output,Please let me know how to get the required output,Many thx in advance

  Read the article

• Foreign key reference to a two-column primary key

  - by Adam Ernst

  One of my tables has a two-column primary key: CREATE TABLE tournament ( state CHAR(2) NOT NULL, year INT NOT NULL, etc..., PRIMARY KEY(state, year) ); I want a reference to the tournament table from another table, but I want this reference to be nullable. Here's how I might do it, imagining that a winner doesn't necessarily have a tournament: CREATE TABLE winner ( name VARCHAR NOT NULL, state CHAR(2) NULL, year INT NULL ); If state is null but year is not, or vice-versa, the table would be inconsistent. I believe the following FOREIGN KEY constraint fixes it: ALTER TABLE winner ADD CONSTRAINT FOREIGN KEY fk (name, state) REFERENCES tournament (name, state); Is this the proper way of enforcing consistency? Is this schema properly normalized?

  Read the article

• How effective can this Tagging solution be?

  - by Pablo

  Im working on an image sharing site and want to implement tagging for the images. I've read Questions #20856 and #2504150 I have few concerns with the approach on the questions above. First of all it looks easy to link an image to a tag. However getting images by tag relation is not as easy. Not easy because you will have to get the image-to-tag relation from one table and then make a big query with a bunch of OR statements( one OR for every image). Before i even research the tagging topic i started testing the following method: This tables as examples: Table: Image Columns: ItemID, Title, Tags Table: Tag Columns: TagID, Name The Tags column in the Image table takes a string with multiple tagID from the Tag table enclosed by dashes(-). For example: -65-25-105- Links an image with the TagID 65,25 and 105. With this method i find it easier to get images by tag as i can get the TagID with one query and get all the images with another simple query like: SELECT * FROM Image WHERE Tags LIKE %-65-% So if i use this method for tagging, How effective this is? Is querying by LIKE %-65-% a slow process? What problems can i face in the future?

  Read the article

• How to select from tableA sum of grouped numbers from tableB above their sums average in Oracle?

  - by Nazgulled

  I have data like this: tableA.ID --------- 1 2 3 tableB.ID tableB.NUM -------------------- 1 10 1 15 2 18 3 12 2 15 3 13 1 12 I need to select tableA IDs where the sum of their NUMs in tableB is above the average of all tableA IDs sums. In other words: SUM ID=1 -> 10+15+12 = 37 SUM ID=2 -> 18+12+15 = 45 SUM ID=3 -> 12+13 = 25 AVG ALL IDs -> (37+45+25)/3 = 35 The SELECT must only show ID 1 and 2 because 37 35, 45 35 but 25 < 35. This is my current query which is working fine: SELECT tableA.ID FROM tableA, tableB WHERE tableA.ID = tableB.ID HAVING SUM(tableB.NUM) > ( SELECT AVG(MY_SUM) FROM ( SELECT SUM(tableB.NUM) MY_SUM FROM tableA, tableB WHERE tableA.ID = tableB.ID GROUP BY tableA.ID ) ) GROUP BY tableA.ID But I have a feeling there might be a better way without all those nested SELECTs. Perhaps 2, but 3 feels like too much. I'm probably wrong though. For instance, why can't I do something simple like this: SELECT tableA.ID FROM tableA, tableB WHERE tableA.ID = tableB.ID HAVING SUM(tableB.NUM) > AVG(SUM(tableB.NUM)) GROUP BY tableA.ID Or this: SELECT tableA.ID, SUM(tableB.NUM) MY_SUM FROM tableA, tableB WHERE tableA.ID = tableB.ID HAVING MY_SUM > AVG(MY_SUM) GROUP BY tableA.ID

  Read the article

• Oracle custom sort

  - by Carter

  The query... select distinct name from myTable returns a bunch of values that start with the following character sequences... ADL* FG* FH* LAS* TWUP* Where '*' is the remainder of the string. I want to do an order by that sorts in the following manner... ADL* LAS* TWUP* FG* FH* But then I also want to sort within each name in the standard order by fashion. So, an example, if I have the following values LAS-21A TWUP-1 FG999 FH3 ADL99999 ADL88888 ADL77777 LAS2 I want it to be sorted like this... ADL77777 ADL88888 ADL99999 LAS2 TWUP-1 FG999 FH3 I initially thought I could accomplish this vias doing an order by decode(blah) with some like trickery inside of the decode but I've been unable to accomplish it. Any insights?

  Read the article

• How can parallelism affect number of results?

  - by spender

  I have a fairly complex query that looks something like this: create table Items(SomeOtherTableID int,SomeField int) create table SomeOtherTable(Id int,GroupID int) with cte1 as ( select SomeOtherTableID,COUNT(*) SubItemCount from Items t where t.SomeField is not null group by SomeOtherTableID ),cte2 as ( select tc.SomeOtherTableID,ROW_NUMBER() over (partition by a.GroupID order by tc.SubItemCount desc) SubItemRank from Items t inner join SomeOtherTable a on a.Id=t.SomeOtherTableID inner join cte1 tc on tc.SomeOtherTableID=t.SomeOtherTableID where t.SomeField is not null ),cte3 as ( select SomeOtherTableID from cte2 where SubItemRank=1 ) select * from cte3 t1 inner join cte3 t2 on t1.SomeOtherTableID<t2.SomeOtherTableID option (maxdop 1) The query is such that cte3 is filled with 6222 distinct results. In the final select, I am performing a cross join on cte3 with itself, (so that I can compare every value in the table with every other value in the table at a later point). Notice the final line : option (maxdop 1) Apparently, this switches off parallelism. So, with 6222 results rows in cte3, I would expect (6222*6221)/2, or 19353531 results in the subsequent cross joining select, and with the final maxdop line in place, that is indeed the case. However, when I remove the maxdop line, the number of results jumps to 19380454. I have 4 cores on my dev box. WTF? Can anyone explain why this is? Do I need to reconsider previous queries that cross join in this way?

  Read the article

• Is there any class generator for bltoolkit?

  - by Sami Beyoglu

  Is there any class generator for bltoolkit?

  Read the article

< Previous Page | 659 660 661 662 663 664 665 666 667 668 669 670  | Next Page >