Search Results

Search found 19966 results on 799 pages for 'datetime query'.

Page 71/799 | < Previous Page | 67 68 69 70 71 72 73 74 75 76 77 78  | Next Page >

• SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)

  - by pinaldave

  Earlier this weekend I have presented at Bangalore User Group on the subject of SQL Server Tips and Tricks. During the presentation I have asked a question to attendees. It was very interesting to see that I have received various different answer to my question. Here is the same question for you and I would like to see what your answer to this question. Question: SELECT * gives error when executed alone but SELECT COUNT(*) does not. Why? Select * - resulting Error Select count * - NOT resulting Error Please leave your answer as comment over here. If you prefer you can blog post about this on your blog and put a link here. I will publish valid answer with due credit in future blog posts. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, Readers Question, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Enable PowerPivot Plugin in Excel

  - by pinaldave

  Recently I had interesting experience at one conference. My PowerPivot plugin got disabled and I had no clue how to enable the same. After while, I figured out how to enable the same. Once I got back from the event, I searched online and realize that many other people online are facing the same problem. Here is how I solved the problem. When I started Excel it did not load PowerPivot plugin. I found in option>> Add in the plug in to be disabled. I enabled the plugin and it worked very well. Let us see that with images. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: PowerPivot

  Read the article

• Speaking at SQLSaturday #44 in Huntington Beach, CA (Los Angeles Area)

  - by Ben Nevarez

    I'll be presenting a session at SQLSaturday #44 in Huntington Beach, the first SQLSaturday on Southern California. The event takes place on Saturday, April 24 at the Golden West College on 15744 Goldenwest St, Huntington Beach, CA 92647.. For more information visit the following link   http://sqlsaturday.com/44/eventhome.aspx   My session is “How the Query Optimizer Works”. I hope to see you there. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Did You Know? I'm doing 3 more online seminars with SSWUG!

  - by Kalen Delaney

  As I told you in April , I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops can be replayed for up to a week after the broadcast, and you can even buy a DVD of the workshop. You can get more details by clicking on the workshop name, below, or check out the announcement on the SSWUG site at http://www.sswug.org/editorials/default.aspx?id=1948...(read more)

  Read the article

• Did You Know? I'm doing 3 more online seminars with SSWUG!

  - by Kalen Delaney

  As I told you in April , I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops can be replayed for up to a week after the broadcast, and you can even buy a DVD of the workshop. You can get more details by clicking on the workshop name, below, or check out the announcement on the SSWUG site at http://www.sswug.org/editorials/default.aspx?id=1948...(read more)

  Read the article

• Using QTIP2 in COGNOS "content" customization

  - by Jonathan

  I'd like to know how to call to a query in COGNOS 8 using qtip2. Where do I plugin the required "content?" For instance: ajax was calling to a wiki server that pulled an image and content dynamically. So it goes with out saying that I need to plugin my content there but what exactly do I plugin? I know we have ASP.NET and ISS on our cognos server side but where can I just plugin a span for the data to appear dynamically in my scrollable qtip2?

  Read the article

• SQL SERVER – Merge Two Columns into a Single Column

  - by Pinal Dave

  Here is a question which I have received from user yesterday. Hi Pinal, I want to build queries in SQL server that merge two columns of the table If I have two columns like, Column1 | Column2 1                5 2                6 3                7 4                8 I want to output like, Column1 1 2 3 4 5 6 7 8 It is a good question. Here is how we can do achieve the task. I am making the assumption that both the columns have different data and there is no duplicate. USE TempDB GO CREATE TABLE TestTable (Col1 INT, Col2 INT) GO INSERT INTO TestTable (Col1, Col2) SELECT 1, 5 UNION ALL SELECT 2, 6 UNION ALL SELECT 3, 7 UNION ALL SELECT 4, 8 GO SELECT Col1 FROM TestTable UNION SELECT Col2 FROM TestTable GO DROP TABLE TestTable GO Here is the original table. Here is the result table. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

  Read the article

• T-SQL Tuesday: What kind of Bookmark are you using?

  - by Kalen Delaney

  I’m glad there is no minimum length requirement for T-SQL Tuesday blog posts , because this one will be short. I was in the classroom for almost 11 hours today, and I need to be back tomorrow morning at 7:30. Way long ago, back in SQL 2000 (or was it earlier?) when a query indicated that SQL Server was going to use a nonclustered index to get row pointers, and then look up those rows in the underlying table, the plan just had a very linear look to it. The operator that indicated going from the nonclustered...(read more)

  Read the article

• IronRuby System.DateTime NilClass

  - by Sergey Mirvoda

  Why comparing to null is so unstable? Just code. IronRuby 0.9.4.0 on .NET 2.0.50727.4927 Copyright (c) Microsoft Corporation. All rights reserved. >>> require 'System' => true >>> i = System::Int32.MinValue => -2147483648 >>> i==nil => false >>> d = System::DateTime.Now => 11.02.2010 14:15:02 >>> d==nil (ir):1: can't convert NilClass into System::DateTime (TypeError) >>> In 9.1 this code works as expected. EDIT: workaround: >>> i.nil? => false >>> d.nil? => false >>> nil => nil >>> nil.nil? => true >>>

  Read the article

• Object Reference with TimeSpan/DateTime

  - by user1732039

  When creating an appointment i want to send an email out to the patient with details like Time, Date etc. I know the email service i have created works (i have tested it by hardcoding strings into the method with the problem. The Problem is that i am getting Object reference issues with converting the Time and Date to a string. It does create the appointment data in the database correctly (time and date). User_Doctor thisDoc = user_DoctorComboBox.SelectedItem as User_Doctor; User_Patient thisPatient = appointment_Patient_autoComplete.SelectedItem as User_Patient; Appointment App = AppointmentSlots.SelectedItem as Appointment; DateTime date = (DateTime)datePickerAppointment.SelectedDate; TimeSpan timeslot = App.Time; //For Emailing Patients string fullname = thisPatient.PatientName + " " + thisPatient.PatientSurname; string mestime = timeslot.ToString("HH:mm"); string mesdate = date.ToString("MM/dd/yyyy"); string email = thisPatient.aspnet_Users.aspnet_Membership.LoweredEmail; EmailServiceClient em = new EmailServiceClient(); em.createMessageAsync(email, "Upcomming Appointment", fullname, mestime, mesdate, thisDoc.aspnet_Users.UserName, true); The problem occures with the strings mestime and mesdate, as well as with getting the email of the user from the database (again this exists in the db, as a nvar)

  Read the article

• SQL query duplicating results [on hold]

  - by Ben

  I have written a query that results in data being retrieved for the top 5 customers in my table per account manager. Here is the query: SELECT account_manager_id, mgap_ska_id, total FROM (SELECT account_manager_id, mgap_ska_id, mgap_growth + mgap_recovery AS total, @grp_rank := IF(@current_accmanid = account_manager_id, @grp_rank + 1, 1) AS grp_rank, @current_accmanid := account_manager_id FROM mgap_orders ORDER BY total DESC ) ranked WHERE grp_rank <= 5 and here is the result of the query: account_manager_id mgap_ska_id total 159840 5062352 61569.21 159840 5062352 61569.21 159840 5062352 61569.21 159840 5062352 61569.21 159840 5062352 61569.21 160023 5024546 52244.29 160023 5024546 52244.29 160023 5024546 52244.29 160023 5024546 52244.29 160023 5024546 52244.29 159669 5323292 50126.38 159669 5323292 50126.38 159669 5323292 50126.38 159669 5323292 50126.38 159669 5323292 50126.38 As you can see the query is partially working as needed, except Im getting duplicates for mgap_ska_id whereas it should be five individual mgap_ska_id numbers. and here is a sample of my data: mgap_ska_id account_manager_id mgap_growth mgap_recovery 5057810 64154 0 1160.78 5178114 24456 0 5773.42 5292421 160338 0 5146.04 5414091 24408 0 104.14 5057810 64154 0 1160.78 Can anyone see where Ive gone wrong in my query and how/where I might correct the error so I get the 5 top individual customers (mgap_ska_id) instead of the duplicated top single customer?

  Read the article

• Using Terminal Services 'query' in batch file

  - by dboarman-FissureStudios

  I have a batch file that checks several of our servers for a user. From the command: query user %userID% /server:ServerName I want to capture the output before it goes to the screen. Is there a way to redirect the output to a variable? The basic gist of what I want to accomplish is this - we iterate through our servers: query user %userID% /server:Server1 query user %userID% /server:Server2 query user %userID% /server:Server3 query user %userID% /server:Server4 Instead of outputting a message that the user could not be found on a specific server, I would like for it to only notify me if it finds the user on a server.

  Read the article

• Challenge 19 – An Explanation of a Query

  - by Dave Ballantyne

  I have received a number of requests for an explanation of my winning query of TSQL Challenge 19. This involved traversing a hierarchy of employees and rolling a count of orders from subordinates up to superiors. The first concept I shall address is the hierarchyId , which is constructed within the CTE called cteTree.   cteTree is a recursive cte that will expand the parent-child hierarchy of the personnel in the table @emp.  One useful feature with a recursive cte is that data can be ‘passed’ from the parent to the child data.  The hierarchyId column is similar to the hierarchyId data type that was introduced in SQL Server 2008 and represents the position of the person within the organisation. Let us start with a simplistic example Albert manages Bob and Eddie.  Bob manages Carl and Dave. The hierarchyId will represent each person’s position in this relationship in a single field.  In this simple example we could append the userID together into a varchar field as detailed below. This will enable us to select a branch of the tree by filtering using Where hierarchyId  ‘1,2%’ to select Bob and all his subordinates.  Naturally, this is not comprehensive enough to provide a full solution, but as opposed to concatenating the Id’s together into a varchar datatyped column, we can apply the same theory to a varbinary.  By CASTing the ID’s into a datatype of varbinary(4) ,4 is used as 4 bytes of data are used to store an integer and building a hierarchyId  from those.  For example: The important point to bear in mind for later in the query is that the binary data generated is 'byte order comparable'. ie We can ORDER a dataset with it and the resulting data, will be in the order required. Now, would probably be a good time to download the example file and, after the cte ‘cteTree’, uncomment the line ‘select * from cteTree’.  Mark this and all prior code and execute.  This will show you how this theory directly relates to the actual challenge data.  The only deviation from the above, is that instead of using the ID of an employee, I have used the row_number() ranking function to order each level by LastName,Firstname.  This enables me to order by the HierarchyId in the final result set so that the result set is in the required order. Your output should be something like the below.  Notice also the ‘Level’ Column that contains the depth that the employee is within the tree.  I would encourage you to ‘play’ with the query, change the order in the row_number() or the length of the cast in the hierarchyId to see how that effects the outcome.  The next cte, ‘cteTreeWithOrderCount’, is a join between cteTree and the @ord table, and COUNT’s the number of orders per employee.  A LEFT JOIN is employed here to account for the occasion where an employee has made no sales.   Executing a ‘Select * from cteTreeWithOrderCount’ will return the result set as below.  The order here is unimportant as this is only a staging point of the data and only the final result set in a cte chain needs an Order by clause, unless TOP is utilised. cteExplode joins the above result set to the tally table (Nums) for Level Occurances.  So, if level is 2 then 2 rows are required.  This is done to expand the dataset, to create a new column (PathInc), which is the (n+1) integers contained within the heirarchyid.  For example, with the data for Robert King as given above, the below 3 rows will be returned. From this you can see that the pathinc column now contains the values for Andrew Fuller and Steven Buchanan who are Robert King’s superiors within the tree.    Finally cteSumUp, sums the orders for each person and their subordinates using the PathInc generated above, and the final select does the final simple mathematics and filters to restrict the result set to only the ‘original’ row per employee.

  Read the article

• How To Publish Business Objects Query Service

  - by ssorrrell

  We are trying to copy a BO Query Service from one Universe to another. If you use the BO Query As A Service(QAAS) tool you can do this, but end up basically recreating the query service. It seems like the BusinessObjects.DSWS.* libraries allow you to read and write query services, but those don't appear in the QAAS tool. I think that those queries go into a different Universe than the QAAS tool pings. Perhaps there is a Universe for data and another for Web Service Queries. Monitoring the QAAS tool for HTTP traffic revealed that the BO Web Service used to run queries for the data they contain is also used to manage the Web Service queries. I was able to copy one Query Service into a new one in a new Universe using a Replace() on the XML string in QuerySpec to change the UniverseID. We can basically copy one Query Service to another Universe without manually rebuilding it except for one little thing. The QAAS tool includes a Publish button. This does something unknown, but important. Perhaps it makes some SOAP, WSDL or config files so that the copied Query Service is public. There doesn't seem to be any HTTP traffic to snoop on when it's doing this. The BusinessObjects.DSWS.* libraries include a Publish feature, but it's not for Query Services. It's for general files like Excel and PDF. Right now, we are relegated to using two tools. Does anyone know about how to Publish a BO Query Service programmatically just like the QAAS Tool?

  Read the article

• Query to find the data for every month of last year from the given date using mysql?

  - by Salil

  Hi All, I want data for the the last 1 year from the given date For ex:- I have date "2013-06-01" and i want data as follows also data i want is from three table using Group By or something else Month             Amount         Total_Data June 2013     100                5 May 2013       80                  4 -                     100                5 -                     100                5 July 2012       10                  2 I try following query but didn't workout SELECT DATE_FORMAT(rf.period, '%M %Y') as Month , sum(p.amount * ((100-q.amount)/100)) as Amount ,count(distinct q.label_id) as Total Data FROM table1 rf , table2 p , table3 q ,table4 a where rf.period BETWEEN '2013-06-01' AND '2013-06-01' and q.royalty_period BETWEEN '2013-06-01' AND '2013-06-01' and a.id = q.album_id and p.file_id = rf.id and p.upc = a.upc and p.local_revenue is not null GROUP BY Month Thanks in Advance, Salil Gaikwad

  Read the article

• Appengine datastore phantom entity - inconsistent state?

  - by aloo

  Getting a weird error on java appengine code that used to work fine (nothing has changed but the data in the datastore). I'm trying to iterate over the results of a query and change a few properties of the entities. The query does return a set of results, however, when I try to access the first result in the list, it throws an exception when trying to access any of its properties (but its key). Here's the exception: org.datanucleus.state.JDOStateManagerImpl isLoaded: Exception thrown by StateManager.isLoaded Could not retrieve entity of kind OnTheCan with key OnTheCan(3204258) org.datanucleus.exceptions.NucleusObjectNotFoundException: Could not retrieve entity of kind OnTheCan with key OnTheCan(3204258) at org.datanucleus.store.appengine.DatastoreExceptionTranslator.wrapEntityNotFoundException(DatastoreExceptionTranslator.java:60) And here is my code: PersistenceManager pm = PMF.get().getPersistenceManager(); Query query = null; List<OnTheCan> cans; query = pm.newQuery("SELECT this FROM " + OnTheCan.class.getName() + " WHERE open == true ORDER BY onTheCanId ASC"); query.setRange(0, num); cans = (List<OnTheCan>) query.execute(); for (OnTheCan c : cans) { System.err.println(c.getOnTheCanId()); // this works fine! getting the key works c.setOpen(false); // failure here with the above exception c.setAutoClosed(true); c.setEndTime(new Date(c.getStartTime().getTime() + 600000/*10*60*1000*/)); } pm.close(); The code throws the exception when trying to execute c.setOpen(false) - thats the first time I'm accessing or setting a property that isnt the key. So it seems there is a phantom entity in my datastore with key 3204258. THis entity doesn't really exist (queried the datastore from admin console) but for some reason its being returned by the query. Could my data store be in an inconsistent state? I've managed the following workaround by placing it as the first line in my for loop. Clearly an ugly hack: if (c.getOnTheCanId() == 3204258) { continue; } Any ideas?

  Read the article

• How can I get a distinct list of elements in a hierarchical query?

  - by RenderIn

  I have a database table, with people identified by a name, a job and a city. I have a second table that contains a hierarchical representation of every job in the company in every city. Suppose I have 3 people in the people table: [name(PK),title,city] Jim, Salesman, Houston Jane, Associate Marketer, Chicago Bill, Cashier, New York And I have thousands of job type/location combinations in the job table, a sample of which follow. You can see the hierarchical relationship since parent_title is a foreign key to title: [title,city,pay,parent_title] Salesman, Houston, $50000, CEO Cashier, Houston, $25000 CEO, USA, $1000000 Associate Marketer, Chicago, $75000 Senior Marketer, Chicago, $125000 ..... The problem I'm having is that my Person table is a composite key, so I don't know how to structure the start with part of my query so that it starts with each of the three jobs in the cities I specified. I can execute three separate queries to get what I want, but this doesn't scale well. e.g.: select * from jobs start with city = (select city from people where name = 'Bill') and title = (select title from people where name = 'Bill') connect by prior parent_title = title UNION select * from jobs start with city = (select city from people where name = 'Jim') and title = (select title from people where name = 'Jim') connect by prior parent_title = title UNION select * from jobs start with city = (select city from people where name = 'Jane') and title = (select title from people where name = 'Jane') connect by prior parent_title = title How else can I get a distinct list (or I could wrap it with a distinct if not possible) of all the jobs which are above the three people I specified?

  Read the article

• Bulk retrieval in HQL: could not execute native bulk manipulation query

  - by user179056

  Hello, We are getting a "could not execute native bulk manipulation query" error in HQL When we execute a query which is something like this. String query = "Select person.id from Person person where"; String bindParam = ""; List subLists = getChucnkedList(dd); for(int i = 0 ; i < subLists.size() ;i++){ bindParam = bindParam + " person.id in (:param" + i + ")"; if (i < subLists.size() - 1 ) { bindParam = bindParam + " OR " ; } } query = query + bindParam; final Query query1 = session.createQuery(query.toString()); for(int i = 0 ; i < subLists.size() ;i++){ query1.setParameterList("param" + i, subLists.get(i)); } List personIdsList = query1.list(); Basically to avoid the limit on IN clause in terms of number of ids which can be inserted (not more than 1000), we have created sublists of ids of not more than 1000 in number. We are using bind parameters to bind each sublist. However we still get error "could not execute native bulk manipulation query" How does one avoid the problem of limited parameters possible in IN query when parameters passed are more than 1000? regards Sameer

  Read the article

• How to combine a Distance and Keyword SQL query?

  - by Jason

  Hi Folks, I have a tables in my database called "points" and "category". A user will input info into both a location input and a keyword input text box. Then I want to find points in my table where the keyword matches either the "title" field in the points table, or the "category" but are within a certain distance from the user's location. I want to order the results by distance. Here are the 2 queries which btoh work independently: $mysql = "SELECT *, ( 3959 * acos( cos( radians('$search_lat') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('$search_lng') ) + sin( radians('$search_lat') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '$radius'"; $mysql2 = "SELECT * FROM `points` LEFT JOIN category USING ( category_id ) WHERE (point_title LIKE '%$esc_catsearch%' OR category.title LIKE '%$esc_catsearch%')"; Here is what I tried: $sql_search = sprintf("SELECT *,point_id FROM points WHERE point_title LIKE '%%%s%%' UNION SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '%s' ORDER BY distance LIMIT %d , %d", $esc_catsearch, mysql_real_escape_string($search_lat), mysql_real_escape_string($search_lng), mysql_real_escape_string($search_lat), mysql_real_escape_string($radius), $offset, $rowsPerPage); But it tells me there is no know column "distance". If I remove the "Order By" phrase then it works but I'm still not sure this is giving me the results I want. I also tried the query the other way around with the distance search first but that seems to ignore my keyword. Any thoughts would be much appreciated!

  Read the article

• Codeigniter Pagination: Run the Query Twice?

  - by Frank

  I'm using codeigniter and the pagination class. This is such a basic question, but I need to make sure I'm not missing something. In order to get the config items necessary to paginate results getting them from a MySQL database it's basically necessary to run the query twice is that right? In other words, you have to run the query to determine the total number of records before you can paginate. So I'm doing it like: Do this query to get number of results $this->db->where('something', $something); $query = $this->db->get('the_table_name'); $num_rows = $query->num_rows(); Then I'll have to do it again to get the results with the limit and offset. Something like: $this->db->where('something', $something); $this->db->limit($limit, $offset); $query = $this->db->get('the_table_name'); if($query->num_rows()){ foreach($query->result_array() as $row){ ## get the results here } } I just wonder if I'm actually doing this right in that the query always needs to be run twice? The queries I'm using are much more complex than what is shown above.

  Read the article

• Query Level 2 Caching throwing ClassCastException

  - by Sameer Malhotra

  Hi, I am using JPA and Hibernate for the database. I have configured (EHCacache) second level cache and query level cache, but just to make sure that caching is working I was trying to get the statistics which is throwing class cast exception.Any help will be highly appreciated. My main goal is to see all the objects which have been cached to make sure that the caching is working properly. Here is the code: public List<CodeValue> findByCodetype(String propertyName) { try { final String queryString = "select model from CodeValue model where model.codetype" + "= :propertyValue" + " order by model.code"; Query query = em.createQuery(queryString); query.setHint("org.hibernate.cacheable", true); query.setHint("org.hibernate.cacheRegion", "query.findByCodetype"); query.setParameter("propertyValue", propertyName); List resultList = query.getResultList(); org.hibernate.Session session = (Session) em.getDelegate(); SessionFactory sessionFactory = session.getSessionFactory(); Map cacheEntries = sessionFactory.getStatistics() .getSecondLevelCacheStatistics("query.findByCodetype") .getEntries(); logger.info("The statistics are: " + cacheEntries); return resultList; } catch (RuntimeException re) { logger.error("findByCodetype failed in trauma patient", re); throw re; } } The error is existing right when I am trying to print the statistics. Below is exception: [6/7/10 19:23:17:059 GMT] 00000034 SystemOut O java.lang.ClassCastException: org.hibernate.cache.QueryKey incompatible with org.hibernate.cache.CacheKey at org.hibernate.stat.SecondLevelCacheStatistics.getEntries(SecondLevelCacheStatistics.java:51) at com.idph.trauma.registry.service.TraumaPatientDAO.findByCodetype(TraumaPatientDAO.java:439) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:615) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at $Proxy209.findByCodetype(Unknown Source) Do you know what's going on?

  Read the article

• How to perform Linq select new with datetime in SQL 2008

  - by kd7iwp

  In our C# code I recently changed a line from inside a linq-to-sql select new query as follows: OrderDate = (p.OrderDate.HasValue ? p.OrderDate.Value.Year.ToString() + "-" + p.OrderDate.Value.Month.ToString() + "-" + p.OrderDate.Value.Day.ToString() : "") To: OrderDate = (p.OrderDate.HasValue ? p.OrderDate.Value.ToString("yyyy-mm-dd") : "") The change makes the line smaller and cleaner. It also works fine with our SQL 2008 database in our development environment. However, when the code deployed to our production environment which uses SQL 2005 I received an exception stating: Nullable Type must have a value. For further analysis I copied (p.OrderDate.HasValue ? p.OrderDate.Value.ToString("yyyy-mm-dd") : "") into a string (outside of a Linq statement) and had no problems at all, so it only causes an in issue inside my Linq. Is this problem just something to do with SQL 2005 using different date formats than from SQL 2008? Here's more of the Linq: dt = FilteredOrders.Where(x => x != null).Select(p => new { Order = p.OrderId, link = "/order/" + p.OrderId.ToString(), StudentId = (p.PersonId.HasValue ? p.PersonId.Value : 0), FirstName = p.IdentifierAccount.Person.FirstName, LastName = p.IdentifierAccount.Person.LastName, DeliverBy = p.DeliverBy, OrderDate = p.OrderDate.HasValue ? p.OrderDate.Value.Date.ToString("yyyy-mm-dd") : ""}).ToDataTable(); This is selecting from a List of Order objects. The FilteredOrders list is from another linq-to-sql query and I call .AsEnumerable on it before giving it to this particular select new query. Doing this in regular code works fine: if (o.OrderDate.HasValue) tempString += " " + o.OrderDate.Value.Date.ToString("yyyy-mm-dd");

  Read the article

• MySQL Gurus: How to pull a complex grid of data from MySQL database with one query?

  - by iopener

  Hopefully this is less complex than I think. I have one table of companies, and another table of jobs, and a third table with that contains a single entry for each employee in each job from each company. NOTE: Some companies won't have employees in some jobs, and some companies will have more than one employee in some jobs. The company table has a companyid and companyname field, the job table has a jobid and jobtitle field, and the employee table has employeeid, companyid, jobid and employeename fields. I want to build a table like this: +-----------+-----------+-----------+ | Company A | Company B | Company C | ------+-----------+-----------+-----------+ Job A | Emp 1 | Emp 2 | | ------+-----------+-----------+-----------+ Job B | Emp 3 | | Emp 4 | | | | Emp 5 | ------+-----------+-----------+-----------+ Job C | | Emp 6 | | | | Emp 7 | | | | Emp 8 | | ------+-----------+-----------+-----------+ I had previously been looping through a result set of jobs, and for each job, looping through a result set of each company, and for each company, looping through each employee and printing it in a table (gross, but performance was not supposed to be a consideration). The app has grown in popularity, and now we have 100 companies and hundreds of jobs, and the server is crapping out (all the id fields are indexed). Any suggestions on how to write a single query to get this data? I don't need the company names or job titles (obviously), but I do need some way to identify where each row from the result should be printed. I'm imagining a result set that just contained a long list of joined employees, and I could write a loop to use the companyid and employeeid values to tell me when to create a new cell or table row. This works as long as there aren't ZERO employees; I would need a NULL employee name for that I think? Am I completely on the wrong track? Thanks in advance for any ideas!

  Read the article

• How can i get rid of 'ORA-01489: result of string concatenation is too long' in this query?

  - by core_pro

  this query gets the dominating sets in a network. so for example given a network A<----->B B<----->C B<----->D C<----->E D<----->C D<----->E F<----->E it returns B,E B,F A,E but it doesn't work for large data because i'm using string methods in my result. i have been trying to remove the string methods and return a view or something but to no avail With t as (select 'A' as per1, 'B' as per2 from dual union all select 'B','C' from dual union all select 'B','D' from dual union all select 'C','B' from dual union all select 'C','E' from dual union all select 'D','C' from dual union all select 'D','E' from dual union all select 'E','C' from dual union all select 'E','D' from dual union all select 'F','E' from dual) ,t2 as (select distinct least(per1, per2) as per1, greatest(per1, per2) as per2 from t union select distinct greatest(per1, per2) as per1, least(per1, per2) as per1 from t) ,t3 as (select per1, per2, row_number() over (partition by per1 order by per2) as rn from t2) ,people as (select per, row_number() over (order by per) rn from (select distinct per1 as per from t union select distinct per2 from t) ) ,comb as (select sys_connect_by_path(per,',')||',' as p from people connect by rn > prior rn ) ,find as (select p, per2, count(*) over (partition by p) as cnt from ( select distinct comb.p, t3.per2 from comb, t3 where instr(comb.p, ','||t3.per1||',') > 0 or instr(comb.p, ','||t3.per2||',') > 0 ) ) ,rnk as (select p, rank() over (order by length(p)) as rnk from find where cnt = (select count(*) from people) order by rnk ) select distinct trim(',' from p) as p from rnk where rnk.rnk = 1`

  Read the article

• World's Most Challening MySQL SQL Query (least I think so...)

  - by keruilin

  Whoever answers this question can claim credit for solving the world's most challenging SQL query, according to yours truly. Working with 3 tables: users, badges, awards. Relationships: user has many awards; award belongs to user; badge has many awards; award belongs to badge. So badge_id and user_id are foreign keys in the awards table. The business logic at work here is that every time a user wins a badge, he/she receives it as an award. A user can be awarded the same badge multiple times. Each badge is assigned a designated point value (point_value is a field in the badges table). For example, BadgeA can be worth 500 Points, BadgeB 1000 Points, and so on. As further example, let's say UserX won BadgeA 10 times and BadgeB 5 times. BadgeA being worth 500 Points, and BadgeB being worth 1000 Points, UserX has accumulated a total of 10,000 Points ((10 x 500) + (5 x 1000)). The end game here is to return a list of top 50 users who have accumulated the most badge points. Can you do it?

  Read the article

< Previous Page | 67 68 69 70 71 72 73 74 75 76 77 78  | Next Page >