Search Results

Search found 29956 results on 1199 pages for 'query builder methods'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 173/1199 | < Previous Page | 169 170 171 172 173 174 175 176 177 178 179 180  | Next Page >

  • What is causing a Hibernate SQL query exception?

    - by Dark Star1
    Hi all and sorry in advance for this post but I've spent way too much time going around in circles so I'm hoping someone could shed a light on it here for me. I updated a webapp on Tomcat and I'm getting the following error which didn't exist on the previous version. Though I am quite confident that the part of code I modifed isn't to blame as I have tested the app on two different dev servers. The production server is configured thus: CentOS 5.4 virtual server with tomcat 5.5.23 running mysql 5.0.77. The two dev servers are: Windows XP SP2 running tomcat 5.5.23 with mysql 5.1.49 Mac OSX 10.6.6 Running tomcat 6 with mysql 5.1.51 The application was developed using struts (1.1 as far as I can gather) with hibernate 3 as the peristence layer. It only fails on the production server for some reason I can't fathom. I'd like to draw your attention to the java.sql.SQLException near the bottom. After some long searching I found this but because it was posted years ago (about 1 year before development started on this app I'm sure Hibernate has evolved from that version. as I can't find a way of implementing his solution. I use Eclipse Helios as an IDE. Thanks in advance for taking your time to read this, to all who manage to reply. javax.servlet.ServletException: org.hibernate.exception.SQLGrammarException: could not execute query at fr.company.action.login.LoginAction.execute(LoginAction.java:219) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at fr.company.util.EncodingFilter.doFilter(EncodingFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:113) at java.lang.Thread.run(Thread.java:636) javax.servlet.ServletException: org.hibernate.exception.SQLGrammarException: could not execute query at fr.company.action.login.LoginAction.execute(LoginAction.java:219) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at fr.company.util.EncodingFilter.doFilter(EncodingFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:113) at java.lang.Thread.run(Thread.java:636) java.lang.Exception: org.hibernate.exception.SQLGrammarException: could not execute query at fr.company.dao.GenericDAO.findOne(GenericDAO.java:204) at fr.company.dao.UserDAO.findOneUser(UserDAO.java:146) at fr.company.service.UserPeer.logUser(UserPeer.java:72) at fr.company.action.login.LoginAction.execute(LoginAction.java:127) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at fr.company.util.EncodingFilter.doFilter(EncodingFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:113) at java.lang.Thread.run(Thread.java:636) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2153) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) at org.hibernate.loader.Loader.list(Loader.java:2024) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:300) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1128) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749) at fr.company.dao.GenericDAO.findOne(GenericDAO.java:198) ... 26 more Caused by: java.sql.SQLException: Unknown column 'user0_1_.poloSize' in 'field list' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2994) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139) at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2150) ... 35 more

    Read the article

  • Named query not known error trying to call a stored proc using Fluent NHibernate

    - by Hamman359
    I'm working on setting up NHibernate for a project and I have a few queries that, due to their complexity, we will be leaving as stored procedures. I'd like to be able to use NHibernate to call the sprocs, but have run into an error I can't figure out. Since I'm using Fluent NHibernate I'm using mixed mode mapping as recommended here. However, when I run the app I get a "Named query not known: AccountsGetSingle" exception and I can't figure out why. I think I might have a problem with my HBM mapping since I'm not very familiar with using them but I'm not sure. My NHibernate configuration code is: private ISessionFactory CreateSessionFactory() { return Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005 .ConnectionString((conn => conn.FromConnectionStringWithKey("CIDB"))) .ShowSql()) .Mappings(m => { m.HbmMappings.AddFromAssemblyOf<Account>(); m.FluentMappings.AddFromAssemblyOf<Account>(); }) .BuildSessionFactory(); } My hbm.xml file is: <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <sql-query name="AccountsGetSingle"> <return alias="Account" class="Core, Account"></return> exec AccountsGetSingle </sql-query> </hibernate-mapping> And the code where I am calling the sproc looks like this: public Account Get() { return _conversation.Session .GetNamedQuery("AccountsGetSingle") .UniqueResult<Account>(); } Any thoughts or ideas would be appreciated. Thanks.

    Read the article

  • WCF Ria Services Error : Load operation failed for query 'GetTranslationProgress'

    - by Manoj
    Hello, I am using WCF Ria Services beta in my silverlight application. I have a long running task on the server which keeps writing its status to a database table. I have a "GetTranslationProgress" Ria Service Query method which queries the state to get the status. This query method is called continuously at a regular interval of 5 seconds until a status = Finished or Error is retrieved. In some cases if the task on the server is running for a long duration 2-3 minutes then I receives this error:- Load operation failed for query 'GetTranslationProgress'. The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error. at System.Windows.Ria.OperationBase.Complete(Exception error) at System.Windows.Ria.LoadOperation.Complete(Exception error) at System.Windows.Ria.DomainContext.CompleteLoad(IAsyncResult asyncResult) at System.Windows.Ria.DomainContext.<c_DisplayClass17.b_13(Object ) The error occurs intermittently and I have no idea what could be causing this error. I have checked most of the forums and sites but I have not been able to find out any solution to this issue. Please help.

    Read the article

  • Building a generic page "Query Analyzer" like in Asp.net with SMO

    - by Rodrigo
    Hello, I'm build a web page in ASP.net is supposed to work just like Query Analyzer. It has basically a textarea to input the sql command, and an Execute button. I'm using SMO to execute the command in the database, as follows: //Create object SMO Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(oConn)); //To execute the command server.ConnectionContext.ExecuteNonQuery(tbx_cmd.Text); //OR myDataset = server.ConnectionContext.ExecuteWithResults(tbx_cmd.Text); The problem is that the textarea command can contain anything, from a stored procedure (with GO's statements) and any select command that return resultsets, just like Query Analyzer. But, if I have GO statements, I only can perform the query using ExecuteNonQuery method. If I use the ExecuteWithResults method, it raises errors because of the GO statements. And if I want the resultsets to be available, of course I can only use the ExecuteWithResults method. Does anybody knows how can I do to execute the same command texts at the same time? Thank you!

    Read the article

  • Generating the query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

    - by TheImirOfGroofunkistan
    I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

    Read the article

  • SQL MIN in Sub query causes huge delay

    - by Spencer
    I have a SQL query that I'm trying to debug. It works fine for small sets of data, but in large sets of data, this particular part of it causes it to take 45-50 seconds instead of being sub second in speed. This subquery is one of the select items in a larger query. I'm basically trying to figure out when the earliest work date is that fits in the same category as the current row we are looking at (from table dr) ISNULL(CONVERT(varchar(25),(SELECT MIN(drsd.DateWorked) FROM [TableName] drsd WHERE drsd.UserID = dr.UserID AND drsd.Val1 = dr.Val1 OR (((drsd.Val2 = dr.Val2 AND LEN(dr.Val2) > 0) AND (drsd.Val3 = dr.Val3 AND LEN(dr.Val3) > 0) AND (drsd.Val4 = dr.Val4 AND LEN(dr.Val4) > 0)) OR (drsd.Val5 = dr.Val5 AND LEN(dr.Val5) > 0) OR ((drsd.Val6 = dr.Val6 AND LEN(dr.Val6) > 0) AND (drsd.Val7 = dr.Val7 AND LEN(dr.Val2) > 0))))), '') AS WorkStartDate, This winds up executing a key lookup some 18 million times on a table that has 346,000 records. I've tried creating an index on it, but haven't had any success. Also, selecting a max value in this same query is sub second in time, as it doesn't have to execute very many times at all. Any suggestions of a different approach to try? Thanks!

    Read the article

  • ProgrammingError when aggregating over an annotated & grouped Django ORM query

    - by ento
    I'm trying to construct a query to get the "average, maximum, minimum number of items purchased by a single user". The data source is this simple sales record table: class SalesRecord(models.Model): id = models.IntegerField(primary_key=True) user_id = models.IntegerField() product_code = models.CharField() price = models.IntegerField() created_at = models.DateTimeField() A new record is inserted into this table for every item purchased by a user. Here's my attempt at building the query: q = SalesRecord.objects.all() q = q.values('user_id').annotate( # group by user and count the # of records count=Count('id'), # (= # of items) ).order_by() result = q.aggregate(Max('count'), Min('count'), Avg('count')) When I try to execute the code, a ProgrammingError is raised at the last line: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT sales_records.user_id AS user_id, COUNT(sales_records.`' at line 1") Django's error screen shows that the SQL is SELECT FROM (SELECT `sales_records`.`player_id` AS `player_id`, COUNT(`sales_records`.`id`) AS `count` FROM `sales_records` WHERE (`sales_records`.`created_at` >= %s AND `sales_records`.`created_at` <= %s ) GROUP BY `sales_records`.`player_id` ORDER BY NULL) subquery It's not selecting anything! Can someone please show me the right way to do this? Hacking Django I've found that clearing the cache of selected fields in django.db.models.sql.BaseQuery.get_aggregation() seems to solve the problem. Though I'm not really sure this is a fix or a workaround. @@ -327,10 +327,13 @@ # Remove any aggregates marked for reduction from the subquery # and move them to the outer AggregateQuery. + self._aggregate_select_cache = None + self.aggregate_select_mask = None for alias, aggregate in self.aggregate_select.items(): if aggregate.is_summary: query.aggregate_select[alias] = aggregate - del obj.aggregate_select[alias] + if alias in obj.aggregate_select: + del obj.aggregate_select[alias] ... yields result: {'count__max': 267, 'count__avg': 26.2563, 'count__min': 1}

    Read the article

  • Oracle command hangs when using view for "WHERE x IN..." subquery

    - by Calvin Fisher
    I'm working on a web service that fetches data from an oracle data source in chunks and passes it back to an indexing/search tool in XML format. I'm the C#/.NET guy, and am kind of fuzzy on parts of Oracle. Our Oracle team gave us the following script to run, and it works well: SELECT ROWID, [columns] FROM [table] WHERE ROWID IN ( SELECT ROWID FROM ( SELECT ROWID FROM [table] WHERE ROWID > '[previous_batch_last_rowid]' ORDER BY ROWID ) WHERE ROWNUM <= 10000 ) ORDER BY ROWID 10,000 rows is an arbitrary but reasonable chunk size and ROWID is sufficiently unique for our purposes to use as a UID since each indexing run hits only one table at a time. Bracketed values are filled in programmatically by the web service. Now we're going to start adding views to the indexing, each of which will union a few separate tables. Since ROWID would no longer function as a unique identifier, they added a column to the views (VIEW_UNIQUE_ID) that concatenates the ROWIDs from the component tables to construct a UID for each union. But this script does not work, even though it follows the same form as the previous one: SELECT VIEW_UNIQUE_ID, [columns] FROM [view] WHERE VIEW_UNIQUE_ID IN ( SELECT VIEW_UNIQUE_ID FROM ( SELECT VIEW_UNIQUE_ID FROM [view] WHERE ROWID > '[previous_batch_last_view_unique_id]' ORDER BY VIEW_UNIQUE_ID ) WHERE ROWNUM <= 10000 ) ORDER BY VIEW_UNIQUE_ID It hangs indefinitely with no response from the Oracle server. I've waited 20+ minutes and the SQLTools dialog box indicating a running query remains the same, with no progress or updates. I've tested each subquery independently and each works fine and takes a very short amount of time (<= 1 second), so the view itself is sound. But as soon as the inner two SELECT queries are added with "WHERE VIEW_UNIQUE_ID IN...", it hangs. Why doesn't this query work for views? In what important way are they not interchangeable here?

    Read the article

  • Date query with Hibernate on Timestamp Column in PostgreSQL

    - by Shashikant Kore
    A table has timestamp column. A sample value in that could be 2010-03-30 13:42:42. With Hibernate, I am doing a range query Restrictions.between("column-name", fromDate, toDate). The Hibernate mapping for this column is as follows. <property name="orderTimestamp" column="order_timestamp" type="java.util.Date" /> Let's say, I want to find out all the records that have the date 30th March 2010 and 31st March 2010. A range query on this field is done as follows. Date fromDate = new SimpleDateFormat("yyyy-MM-dd").parse("2010-03-30"); Date toDate = new SimpleDateFormat("yyyy-MM-dd").parse("2008-03-31"); Expression.between("orderTimestamp", fromDate, toDate); This doesn't work. The query is converted to respective timestamps as "2010-03-30 00:00:00" and "2010-03-31 00:00:00". So, all the records for the 31st March 2010 are ignored. A simple solution to this problem could be to have the end date as "2010-03-31 23:59:59." But, I would like to know if there is way to match only the date part of the timestamp column. Also, is Expression.between() inclusive of both limits? Documentation doesn't throw any light on this.

    Read the article

  • CQRS - The query side

    - by mattcodes
    A lot of the blogsphere articles related to CQRS (command query repsonsibility) seperation seem to imply that all screens/viewmodels are flat. e.g. Name, Age, Location Of Birth etc.. and thus the suggestion that implementation wise we stick them into fast read source etc.. single table per view mySQL etc.. and pull them out with something like primitive SqlDataReader, kick that nasty nhibernate ORM etc.. However, whilst I agree that domain models dont mapped well to most screens, many of the screens that I work with are more dimensional, and Im sure this is pretty common in LOB apps. So my question is how are people handling screen where by for example it displays a summary of customer details and then a list of their orders with a [more detail] link etc.... I thought about keeping with the straight forward SQL query to the Query Database breaking off the outer join so can build a suitable ViewModel to View but it seems like overkill? Alternatively (this is starting to feel yuck) in CustomerSummaryView table have a text/big (whatever the type is in your DB) column called Orders, and the columns for the Order summary screen grid are seperated by , and rows by |. Even with XML datatype it still feeel dirty. Any thoughts on an optimal practice?

    Read the article

  • Rails add a param to the end of a search query

    - by bob
    Hello, I am trying to implement a sort on the returned results of a search with rails. Filter by: 'recent' % | 'lowest_price' % | 'highest_price' % here are my links. I would like to add a filter to the end of my search query. So for example, I submit a search and I get back results and in my url I have http://localhost:3000/junks?search=handbag&condition=&category=&main_submit=Go! I would then like the user to be able to click one of the above links and have the page come back with a new query (i have this set up in the controller like such) con_id = params[:condition] if params[:category].nil? || params[:category].empty? cat_id = params[:category] results = Junk.search params[:search], :with => { :category_id => cat_id, :condition_id => con_id } case params[:filter] when "lowest_price" @junks = results.lowest_price.paginate :page => params[:page], :per_page => 12 when "highest_price" @junks = results.highest_price.paginate :page => params[:page], :per_page => 12 else @junks = results.paginate :page => params[:page], :per_page => 12 end else I would like the user to be able to click on of the above links and append a filter param to the end of the search query so that my controller can pick it up and call the correct database queries as seen in the case statement above. I'm guessing the url will look like this http://localhost:3000/junks?search=handbag&condition=&category=&main_submit=Go!&filter="lowest_price" How can I do this? Is there a better way?

    Read the article

  • Django custom SQL returning single row of results when query returns 2?

    - by Alvin
    I have a custom SQL call that is returning different results to the template than I get when I run the same query against the database directly, 1 row vs 2 Query - copied from Django Debug Toolbar: SELECT ((Sum(new_recruit_interviews) / Sum(opportunities_offered)) * 100) as avg_recruit, ((Sum(inspections) / Sum(presentations)) * 100) as avg_inspect, ((Sum(contracts_signed) / Sum(roof_approvals)) * 100) as avg_contracts, ((Sum(adjusters) / Sum(contracts_signed)) * 100) as avg_adjusters, ((Sum(roof_approvals) / Sum(adjusters)) *100) as roof_approval_avg, ((Sum(roof_turned_in) / Sum(adjusters)) * 100) as roof_jobs_avg, Sum(roof_turned_in) as roof_jobs_total, ((Sum(siding_approvals) / Sum(adjusters)) *100) as siding_approval_avg, ((Sum(siding_turned_in) / Sum(adjusters)) * 100) as siding_jobs_avg, Sum(siding_turned_in) as siding_jobs_total, ((Sum(gutter_approvals) / Sum(adjusters)) *100) as gutter_approval_avg, ((Sum(gutter_turned_in) / Sum(adjusters)) * 100) as gutter_jobs_avg, Sum(gutter_turned_in) as gutter_jobs_total, ((Sum(window_approvals) / Sum(adjusters)) *100) as window_approval_avg, ((Sum(window_turned_in) / Sum(adjusters)) * 100) as window_jobs_avg, Sum(window_turned_in) as window_jobs_total, (Sum(roof_turned_in) + Sum(siding_turned_in) + Sum(gutter_turned_in) + Sum(window_turned_in)) as total_jobs, (((Sum(collections_jobs_new) + Sum(collections_jobs_previous)) / (Sum(roof_turned_in) + Sum(siding_turned_in) + Sum(gutter_turned_in) + Sum(window_turned_in))) * 100) as total_collections, sales_report_salesmen.location_id as detail_id, business_unit_location.title as title FROM sales_report_salesmen Inner Join auth_user ON sales_report_salesmen.user_id = auth_user.id Inner Join business_unit_location ON sales_report_salesmen.location_id = business_unit_location.id GROUP BY location_id Results from direct query running the above query: INSERT INTO `` (`avg_recruit`, `avg_inspect`, `avg_contracts`, `avg_adjusters`, `roof_approval_avg`, `roof_jobs_avg`, `roof_jobs_total`, `siding_approval_avg`, `siding_jobs_avg`, `siding_jobs_total`, `gutter_approval_avg`, `gutter_jobs_avg`, `gutter_jobs_total`, `window_approval_avg`, `window_jobs_avg`, `window_jobs_total`, `total_jobs`, `total_collections`, `detail_id`, `title`) VALUES (95.3968, 92.8178, 106.9622, 90.2928, 103.5420, 103.5670, 4152, 100.2494, 106.8845, 4285, 120.1297, 86.2559, 3458, 92.9658, 106.1611, 4256, 16151, 4.281469, 12, 'St Paul, MN'); VALUES (90.2982, 73.3723, 97.8474, 104.5433, 97.7585, 86.1848, 1884, 109.9268, 109.3321, 2390, 81.0156, 96.4318, 2108, 91.7200, 123.8792, 2708, 9090, 4.531573, 13, 'Denver, CO'); Results from template: {'roof_jobs_total': Decimal('4152'), 'gutter_jobs_total': Decimal('3458'), 'avg_adjusters': Decimal('90.2928'), 'title': u'St Paul, MN', 'window_approval_avg': Decimal('92.9658'), 'total_collections': Decimal('4.281469'), 'gutter_approval_avg': Decimal('120.1297'), 'avg_recruit': Decimal('95.3968'), 'siding_approval_avg': Decimal('100.2494'), 'window_jobs_total': Decimal('4256'), 'detail_id': 12L, 'siding_jobs_avg': Decimal('106.8845'), 'avg_inspect': Decimal('92.8178'), 'roof_approval_avg': Decimal('103.5420'), 'roof_jobs_avg': Decimal('103.5670'), 'total_jobs': Decimal('16151'), 'window_jobs_avg': Decimal('106.1611'), 'avg_contracts': Decimal('106.9622'), 'gutter_jobs_avg': Decimal('86.2559'), 'siding_jobs_total': Decimal('4285')} Tried tweaking it a few ways and running the results through various for loops, keep getting the same result where my results are a single row through the Django template and the expected results (through console) have 2 rows The row that is coming back is the same as the first row returned through the console query so I'm thinking that it is running correctly just a matter of passing the results through... for good measure this is the code I'm using to generate the query (yes it's a bit ugly, been playing with it) def sql_grouped(table, fields, group_by=False, where=False): from django.db import connection query = 'SELECT %s FROM %s' % (fields, table) if where: query = query + ' WHERE %s' % (where) if group_by: query = query + ' GROUP BY %s' % (group_by) cursor = connection.cursor() cursor.execute(query) desc = cursor.description data = [dict(zip([col[0] for col in desc], row)) for row in cursor.fetchall()] return data[0] any feedback is greatly appreciated - been tinkering with this since I realized I could skip a few steps by generating my averages directly within the SQL rather than post-process

    Read the article

  • sql: Group by x,y,z; return grouped by x,y with lowest f(z)

    - by Sai Emrys
    This is for http://cssfingerprint.com I collect timing stats about how fast the different methods I use perform on different browsers, etc., so that I can optimize the scraping speed. Separately, I have a report about what each method returns for a handful of URLs with known-correct values, so that I can tell which methods are bogus on which browsers. (Each is different, alas.) The related tables look like this: CREATE TABLE `browser_tests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bogus` tinyint(1) DEFAULT NULL, `result` tinyint(1) DEFAULT NULL, `method` varchar(255) DEFAULT NULL, `url` varchar(255) DEFAULT NULL, `os` varchar(255) DEFAULT NULL, `browser` varchar(255) DEFAULT NULL, `version` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=33784 DEFAULT CHARSET=latin1 CREATE TABLE `method_timings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `method` varchar(255) DEFAULT NULL, `batch_size` int(11) DEFAULT NULL, `timing` int(11) DEFAULT NULL, `os` varchar(255) DEFAULT NULL, `browser` varchar(255) DEFAULT NULL, `version` varchar(255) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28849 DEFAULT CHARSET=latin1 (user_agent is broken down pre-insert into browser, version, and os from a small list of recognized values using regex; I keep the original user-agent string just in case.) I have a query like this that tells me the average timing for every non-bogus browser / version / method tuple: select c, avg(bogus) as bog, timing, method, browser, version from browser_tests as b inner join ( select count(*) as c, round(avg(timing)) as timing, method, browser, version from method_timings group by browser, version, method having c > 10 order by browser, version, timing ) as t using (browser, version, method) group by browser, version, method having bog < 1 order by browser, version, timing; Which returns something like: c bog tim method browser version 88 0.8333 184 reuse_insert Chrome 4.0.249.89 18 0.0000 238 mass_insert_width Chrome 4.0.249.89 70 0.0400 246 mass_insert Chrome 4.0.249.89 70 0.0400 327 mass_noinsert Chrome 4.0.249.89 88 0.0556 367 reuse_reinsert Chrome 4.0.249.89 88 0.0556 383 jquery Chrome 4.0.249.89 88 0.0556 863 full_reinsert Chrome 4.0.249.89 187 0.0000 105 jquery Chrome 5.0.307.11 187 0.8806 109 reuse_insert Chrome 5.0.307.11 123 0.0000 110 mass_insert_width Chrome 5.0.307.11 176 0.0000 231 mass_noinsert Chrome 5.0.307.11 176 0.0000 237 mass_insert Chrome 5.0.307.11 187 0.0000 314 reuse_reinsert Chrome 5.0.307.11 187 0.0000 372 full_reinsert Chrome 5.0.307.11 12 0.7500 82 reuse_insert Chrome 5.0.335.0 12 0.2500 102 jquery Chrome 5.0.335.0 [...] I want to modify this query to return only the browser/version/method with the lowest timing - i.e. something like: 88 0.8333 184 reuse_insert Chrome 4.0.249.89 187 0.0000 105 jquery Chrome 5.0.307.11 12 0.7500 82 reuse_insert Chrome 5.0.335.0 [...] How can I do this, while still returning the method that goes with that lowest timing? I could filter it app-side, but I'd rather do this in mysql since it'd work better with my caching.

    Read the article

  • Complex SQL query... names of returned variables

    - by Andrew P.
    Excuse me for what I'm sure is an elementary question for most of you, but I have an issue with table columns from separate tables having the same name as one another, and trying to select from both tables in the same query. Okay, so this is my code: $q_value = $mdb2->quote($_POST['query']); $field = $_POST['field']; $sql = "SELECT m.*, l.name FROM memberlist m, mail_lists l WHERE m.$field=$q_value AND l.id = m.list ORDER BY m.id"; $l_list = $mdb2->queryAll($sql, '', 'MDB2_FETCHMODE_ASSOC'); The table memberlist has the following columns: id, email, list, sex, name and the table mail_lists has the following columns: id, name After running the query, I later loop through the results with a foreach like so: foreach ($l_list as $l){ //blahblah } The problem is that the column 'name' in mail_lists refers to the names of the list, while the column 'name' in memberlist refers to the name of the member. When I later access $l-name (within the foreach), will I get m.name, or l.name? Furthermore, how do I get access to the other? Or will I just have to do two separate queries?

    Read the article

  • MySQL stuck on "using filesort" when doing an "order by"

    - by noko
    I can't seem to get my query to stop using filesort. This is my query: SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` FROM `pilots` p LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) AND (s.`pilot` = p.`id`) ) WHERE p.`game` = 1 AND p.`id` <> 2 AND s.`sector` = 43 AND s.`hull` > 0 ORDER BY p.`last_move` DESC Table structures: CREATE TABLE IF NOT EXISTS `pilots` ( `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT, `game` tinyint(3) unsigned NOT NULL DEFAULT '0', `last_move` int(10) NOT NULL DEFAULT '0', UNIQUE KEY `id` (`id`), KEY `last_move` (`last_move`), KEY `game_id_lastmove` (`game`,`id`,`last_move`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; CREATE TABLE IF NOT EXISTS `ships` ( `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT, `game` tinyint(3) unsigned NOT NULL DEFAULT '0', `pilot` mediumint(5) unsigned NOT NULL DEFAULT '0', `sector` smallint(5) unsigned NOT NULL DEFAULT '0', `hull` smallint(4) unsigned NOT NULL DEFAULT '50', UNIQUE KEY `id` (`id`), KEY `game` (`game`), KEY `pilot` (`pilot`), KEY `sector` (`sector`), KEY `hull` (`hull`), KEY `game_2` (`game`,`pilot`,`sector`,`hull`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; The explain: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p ref id,game_id_lastmove game_id_lastmove 1 const 7 Using where; Using filesort 1 SIMPLE s ref game,pilot,sector... game_2 6 const,fightclub_alpha.p.id,const 1 Using where; Using index edit: I cut some of the unnecessary pieces out of my queries/table structure. Anybody have any ideas?

    Read the article

  • Linq Query Performance , comparing Compiled query vs Non-Compiled.

    - by AG.
    Hello Guys, I was wondering if i extract the common where clause query into a common expression would it make my query much faster, if i have say something like 10 linq queries on a collection with exact same 1st part of the where clause. I have done a small example to explain a bit more . public class Person { public string First { get; set; } public string Last { get; set; } public int Age { get; set; } public String Born { get; set; } public string Living { get; set; } } public sealed class PersonDetails : List<Person> { } PersonDetails d = new PersonDetails(); d.Add(new Person() {Age = 29, Born = "Timbuk Tu", First = "Joe", Last = "Bloggs", Living = "London"}); d.Add(new Person() { Age = 29, Born = "Timbuk Tu", First = "Foo", Last = "Bar", Living = "NewYork" }); Expression<Func<Person, bool>> exp = (a) => a.Age == 29; Func<Person, bool> commonQuery = exp.Compile(); var lx = from y in d where commonQuery.Invoke(y) && y.Living == "London" select y; var bx = from y in d where y.Age == 29 && y.Living == "NewYork" select y; Console.WriteLine("All Details {0}, {1}, {2}, {3}, {4}", lx.Single().Age, lx.Single().First , lx.Single().Last, lx.Single().Living, lx.Single().Born ); Console.WriteLine("All Details {0}, {1}, {2}, {3}, {4}", bx.Single().Age, bx.Single().First, bx.Single().Last, bx.Single().Living, bx.Single().Born); So can some of the guru's here give me some advice if it would be a good practice to write query like var lx = "Linq Expression " or var bx = "Linq Expression" ? Any inputs would be highly appreciated. Thanks, AG

    Read the article

  • Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

    - by TheImirOfGroofunkistan
    I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

    Read the article

  • SqlDataAdaptor why get a different query

    - by Murat
    Hi All, I have a database reader class. But i want to use this class, sometimes SqlDataAdaptor.Fill() method get another query to Datatable. I look at Query in SqlCommand Instance and my query is correct but returning values from different table? What is the problem? This My Code public static DataTable GetLatestRecords(string TableName, string FilterFieldName, int RecordCount, params DBFieldAndValue[] FilterFields) { DataTable DT = new DataTable(); Type ClassType = typeof(T); string SelectString = string.Format("SELECT TOP ({0}) * FROM {1}", RecordCount, TableName); if (FilterFields.Length > 0) { SelectString += " WHERE 1 = 1 "; for (int index = 0; index < FilterFields.Length; index++) SelectString += FilterFields[index].ToString(index); } SelectString += string.Format(" ORDER BY {0} DESC", FilterFieldName); try { SqlConnection Connection = GetConnection<T>(); SqlCommand Command = new SqlCommand(SelectString, Connection); for (int index = 0; index < FilterFields.Length; index++) Command.Parameters.AddWithValue("@" + FilterFields[index].Field + "_" + index, FilterFields[index].Value); if (Connection.State == ConnectionState.Closed) Connection.Open(); Command.CommandText = SelectString; SqlDataAdapter DA = new SqlDataAdapter(Command); DT.Dispose(); DT = new DataTable(); DA.Fill(DT); if (Connection.State == ConnectionState.Open) Connection.Close(); } catch (Exception ex) { WriteLogStatic(ex.Message); throw new Exception(@"Veritabani islemleri sirasinda hata olustu!\nAyrintilar 'C:\Temp' dizini altindadir.\n" + ex.Message); } return DT; }

    Read the article

  • Is possible to reuse subqueries?

    - by Gothmog
    Hello, I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in the same row the element information plus several records information. Structure could be explain like this: table [ id, name ] [1, '1'], [2, '2'] table2 [ id, type, value ] [1, 1, '2009-12-02'] [1, 2, '2010-01-03'] [1, 4, '2010-01-03'] [2, 1, '2010-01-02'] [2, 2, '2010-01-02'] [2, 2, '2010-01-03'] [2, 3, '2010-01-07'] [2, 4, '2010-01-07'] And this is want I would like to achieve: result [id, name, Column1, Column2, Column3, Column4] [1, '1', '2009-12-02', '2010-01-03', , '2010-01-03'] [2, '2', '2010-01-02', '2010-01-02', '2010-01-07', '2010-01-07'] The following query gets the proper result, but it seems to me extremely inefficient, having to iterate table2 for each column. Would be possible in anyway to do a subquery and reuse it? SELECT a.id, a.name, (select min(value) from table2 t where t.id = subquery.id and t.type = 1 group by t.type) as Column1, (select min(value) from table2 t where t.id = subquery.id and t.type = 2 group by t.type) as Column2, (select min(value) from table2 t where t.id = subquery.id and t.type = 3 group by t.type) as Column3, (select min(value) from table2 t where t.id = subquery.id and t.type = 4 group by t.type) as Column4 FROM (SELECT distinct id FROM table2 t WHERE (t.type in (1, 2, 3, 4)) AND t.value between '2010-01-01' and '2010-01-07') as subquery LEFT JOIN table a ON a.id = subquery.id

    Read the article

  • Media query from a specific folder

    - by sensei
    I would like to understand how I can use a cursor to jpg files in a folder specified in the sdcard. I'm trying to select with a cursor the jpg files in a specific folder, and I tried this: This is the code: public static Uri getRandomImage(ContentResolver resolver) { String[] projection = new String[] { BaseColumns._ID, }; String folder = "/sdcard/DCIM/Wallpaper/"; folder = folder + "%"; Uri uri = Media.EXTERNAL_CONTENT_URI; String[] whereArgs = new String[]{folder}; Cursor cursor = resolver.query(uri, projection, null, whereArgs, MediaColumns._ID); if (cursor == null || cursor.getCount() <= 0) { return null; } cursor.moveToPosition(new Random().nextInt(cursor.getCount())); return Uri.withAppendedPath(uri, cursor.getString(0)); } but this code gives me error here is the logcat: E/AndroidRuntime(11986): FATAL EXCEPTION: main E/AndroidRuntime(11986): android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x26a490 E/AndroidRuntime(11986): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:158) E/AndroidRuntime(11986): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:114) E/AndroidRuntime(11986): at android.content.ContentProviderProxy.bulkQueryInternal(ContentProviderNative.java:330) E/AndroidRuntime(11986): at android.content.ContentProviderProxy.query(ContentProviderNative.java:366) E/AndroidRuntime(11986): at android.content.ContentResolver.query(ContentResolver.java:245) E/AndroidRuntime(11986): at it.bisemanuDEV.slidepuzzle.SelectImagePreference.getRandomImage(SelectImagePreference.java:126) E/AndroidRuntime(11986): at it.bisemanuDEV.slidepuzzle.TileView.newGame(TileView.java:156) E/AndroidRuntime(11986): at it.bisemanuDEV.slidepuzzle.SlidePuzzleActivity.onOptionsItemSelected(SlidePuzzleActivity.java:377) E/AndroidRuntime(11986): at android.app.Activity.onMenuItemSelected(Activity.java:2762) E/AndroidRuntime(11986): at com.android.internal.policy.impl.PhoneWindow.onMenuItemSelected(PhoneWindow.java:730) E/AndroidRuntime(11986): at com.android.internal.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:143) E/AndroidRuntime(11986): at com.android.internal.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:855) E/AndroidRuntime(11986): at com.android.internal.view.menu.IconMenuView.invokeItem(IconMenuView.java:532) E/AndroidRuntime(11986): at com.android.internal.view.menu.IconMenuItemView.performClick(IconMenuItemView.java:122) E/AndroidRuntime(11986): at android.view.View$PerformClick.run(View.java:8819) E/AndroidRuntime(11986): at android.os.Handler.handleCallback(Handler.java:603) E/AndroidRuntime(11986): at android.os.Handler.dispatchMessage(Handler.java:92) E/AndroidRuntime(11986): at android.os.Looper.loop(Looper.java:123) E/AndroidRuntime(11986): at android.app.ActivityThread.main(ActivityThread.java:4627) E/AndroidRuntime(11986): at java.lang.reflect.Method.invokeNative(Native Method) E/AndroidRuntime(11986): at java.lang.reflect.Method.invoke(Method.java:521) E/AndroidRuntime(11986): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868) E/AndroidRuntime(11986): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626) E/AndroidRuntime(11986): at dalvik.system.NativeStart.main(Native Method)

    Read the article

  • CQRS - The query side

    - by mattcodes
    A lot of the blogsphere articles related to CQRS (command query repsonsibility) seperation seem to imply that all screens/viewmodels are flat. e.g. Name, Age, Location Of Birth etc.. and thus the suggestion that implementation wise we stick them into fast read source etc.. single table per view mySQL etc.. and pull them out with something like primitive SqlDataReader, kick that nasty nhibernate ORM etc.. However, whilst I agree that domain models dont mapped well to most screens, many of the screens that I work with are more dimensional, and Im sure this is pretty common in LOB apps. So my question is how are people handling screen where by for example it displays a summary of customer details and then a list of their orders with a [more detail] link etc.... I thought about keeping with the straight forward SQL query to the Query Database breaking off the outer join so can build a suitable ViewModel to View but it seems like overkill? Alternatively (this is starting to feel yuck) in CustomerSummaryView table have a text/big (whatever the type is in your DB) column called Orders, and the columns for the Order summary screen grid are seperated by , and rows by |. Even with XML datatype it still feeel dirty. Any thoughts on an optimal practice?

    Read the article

  • Place Query Results into Array then Implode?

    - by jason
    Basically I pull an Id from table1, use that id to find a site id in table2, then need to use the site ids in an array, implode, and query table3 for site names. I cannot implode the array correctly first I got an error, then used a while loop. With the while loop the output simply says: Array $mysqli = mysqli_connect("server", "login", "pass", "db"); $sql = "SELECT MarketID FROM marketdates WHERE Date = '2010-04-04 00:00:00' AND VenueID = '2'"; $result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli)); $dates_id = mysqli_fetch_assoc ( $result ); $comma_separated = implode(",", $dates_id); echo $comma_separated; //This Returns 79, which is correct. $sql = "SELECT SIteID FROM bookings WHERE BSH_ID = '1' AND MarketID = '$comma_separated'"; $result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli)); // This is where my problems start $SIteID = array(); while ($newArray = mysqli_fetch_array($result, MYSQLI_ASSOC)) { $SIteID[] = $newArray[SIteID]; } $locationList = implode(",",$SIteID); ?> Basically what I need to do is correctly move the query results to an array that I can implode and use in a 3rd query to pull names from table3.

    Read the article

  • How to parse xml in sql server to process NULL value in DateTime DataType.

    - by Shantanu Gupta
    I have created a sample query in sql server to parse data from xml and to display it right now. Although I will be inserting this data in my table but before that I am facing a simple problem. I want to insert NULL in datetime field ADDED_DATE="NULL" as shown in xml given below. But when I executes this query. It gives me error Conversion failed when converting datetime from character string. What mistake am i doing. Please highlight my mistake. declare @xml varchar(1000) set @xml= ' <ROOT> <TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP> <TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP> </ROOT> ' declare @handle int exec sp_xml_preparedocument @handle output, @xml select * from OPENXML(@handle,'/ROOT/TX_MAP',1) with ( FK_GUEST_ID INT ,FK_CATEGORY_ID VARCHAR(10) ,ATTRIBUTE VARCHAR(100) ,[DESCRIPTION] VARCHAR(100) ,IS_ACTIVE VARCHAR(10) ,ADDED_BY VARCHAR(100) ,ADDED_DATE DATETIME NULL ,MODIFIED_BY VARCHAR(100) ,MODIFIED_DATE DATETIME NULL ) I am using Sql Server 2005.

    Read the article

  • MS Query Analyzer / Management Studio replacement?

    - by kprobst
    I've been using SQL Server since version 6.5 and I've always been a bit amazed at the fact that the tools seem to be targeted to DBAs rather than developers. I liked the simplicity and speed of the Query Analyzer for example, but hated the built-in editor, which was really no better than a syntax coloring-capable Notepad. Now that we have Management Studio the management part seems a bit better but from a developer standpoint the tools is even worse. Visual Studio's excellent text editor... without a way to customize keyboard bindings!? Don't get me started on how unusable is the tree-based management hierarchy. Why can't I re-root the tree on a list of stored procs for example the way the Enterprise Manager used to allow? Now I have a treeview that needs to be scrolled horizontally, which makes it eminently useless. The SQL server support in Visual Studio is fantastic for working with stored procedures and functions, but it's terrible as a general ad hoc data query tool. I've tried various tools over the years but invariably they seem to focus on the management side and shortchange the developer in me. I just want something with basic admin capabilities, good keyboard support and requisite DDL functionality (ideally something like the Query Analyzer). At this point I'm seriously thinking of using vim+sqlcmd and a console... I'm that desperate :) Those of you who work day in and day out with SQL Server and Visual Studio... do you find the tools to be adequate? Have you ever wished they were better and if you have found something better, could you share please? Thanks!

    Read the article

  • #Error showing up in multiple LEFT JOIN statement Access query when value should be NULL

    - by lar
    I'm trying to return an ID's last 4 years of data, if existing. The table (call it A_TABLE) looks like this: ID, Year, Val The idea behind the query is this: for each ID/Year in the table, LEFT JOIN with Year-1, Year-2, and Year-3 (to get 4 years of data) and then return Val for each year. Here's the SQL: SELECT a.ID, a.year AS [Year], a.Val AS VAL, a1.year AS [Year-1], a1.Val AS [VAL-1], a2.year AS [Year-2], a2.Val AS [VAL-2], a3.year AS [Year-3], a3.Val AS [VAL-3] FROM ( ([A_TABLE] AS a LEFT JOIN [A_TABLE] AS a1 ON (a.ID = a1.ID) AND (a.year = a1.year+1)) LEFT JOIN [A_TABLE] AS a2 ON (a.ID = a2.ID) AND (a.year = a2.year+2)) LEFT JOIN [A_TABLE] AS a3 ON (a.ID = a3.ID) AND (a.year = a3.year+3) The problem is that, for past years where there is no data (eg, Year-1), I see "#Error" in the appropriate VAL column (eg, [VAL-1]). The weird thing is, I see the expected "null" in the Year column (eg, [YEAR-1]). Some sample data: ID YEAR VAL Dave 2004 1 Dave 2006 2 Dave 2007 3 Dave 2008 5 Dave 2009 0 outputs like this: ID YEAR VAL YEAR-1 VAL-1 YEAR-2 VAL-2 YEAR-3 VAL-3 Dave 2004 1 #Error #Error #Error Dave 2006 2 #Error 2004 1 #Error Dave 2007 3 2006 2 #Error 2004 1 Dave 2008 5 2007 3 2006 2 #Error Dave 2009 0 2008 5 2007 3 2006 2 Does that make sense? Why am I getting the appropriate NULL val for the non-existent YEARs, but an #Error for the non-existent VALs? (This is Access 2000. Conditional statements like "IIf(a1.val is null, -999, a1.val)" do not seem to do anything.) EDIT: It turns out that the errors are somehow caused by the fact that A_TABLE is actually a query. When I put all the data into an actual table and run the same query, everything shows up as it should. Thanks for the help, everyone.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 169 170 171 172 173 174 175 176 177 178 179 180  | Next Page >