Search Results

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

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 728/1471 | < Previous Page | 724 725 726 727 728 729 730 731 732 733 734 735  | Next Page >

  • Round date to 10 minutes interval

    - by Peter Lang
    I have a DATE column that I want to round to the next-lower 10 minute interval in a query (see example below). I managed to do it by truncating the seconds and then subtracting the last digit of minutes. WITH test_data AS ( SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:09:59', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2099-01-01 10:00:33', 'YYYY-MM-DD HH24:MI:SS') d FROM dual ) -- #end of test-data SELECT d, TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60) FROM test_data And here is the result: 01.01.2010 10:00:00    01.01.2010 10:00:00 01.01.2010 10:05:00    01.01.2010 10:00:00 01.01.2010 10:09:59    01.01.2010 10:00:00 01.01.2010 10:10:00    01.01.2010 10:10:00 01.01.2099 10:00:33    01.01.2099 10:00:00 Works as expected, but is there a better way? EDIT: I was curious about performance, so I did the following test with 500.000 rows and (not really) random dates. I am going to add the results as comments to the provided solutions. DECLARE t TIMESTAMP := SYSTIMESTAMP; BEGIN FOR i IN ( WITH test_data AS ( SELECT SYSDATE + ROWNUM / 5000 d FROM dual CONNECT BY ROWNUM <= 500000 ) SELECT TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60) FROM test_data ) LOOP NULL; END LOOP; dbms_output.put_line( SYSTIMESTAMP - t ); END; This approach took 03.24 s.

    Read the article

  • What are the Limitations for Connecting to an Access Query in Excel

    - by thornomad
    I have an Access 2007 database that has a number of tables, some are fairly large (100,000+ records); I have created a union query to pull some of the same types of data from multiple tables into one large query for pivot table manipulation and reporting. For example: SELECT Language FROM Table1 UNION ALL SELECT Language FROM Table2 UNION ALL SELECT Language FROM Table3; This works. I found, quickly, however, that a union query will not show up when connecting to the datasource from Excel 2007. So, I created a second query to reference the union query. Like so: SELECT * FROM [The Above Union Query]; This query works and it, initially, was accessible from Excel. Time passed, I've added more data. Suddenly, when I connect to my Access database from Excel my query referencing the union has disappeared. MS Access shows no signs of an issue (data displays in Access) and my other non-union queries are showing up in Excel 2007 ... but not the one that references the union. What could be going on? Why did it disappear? I noticed if I switch some of the referenced tables in the union query to a smaller table (with less rows) all of sudden the query appears in Excel again. At least, I think that's what the difference is. I really can't put my finger on why some of the union queries won't show up and some will. Am stumped and need some guidance. Thanks.

    Read the article

  • how to combine the related version in group by

    - by randeepsp
    select count(a),b,c from APPLE join MANGO on (APPLE.link=MANGO.link) join ORANGE on (APPLE.link=ORANGE.link) where id='camel' group by b,c; the column b gives values like 1.0 1.0,R 1.0,B 2.0 2.0,B 2.0,R 3.0,C 3.0,R is there a way to modify the above quer so that all 1.0 and 1.0,R and 1.0,B are merged as 1.0 and 2.0,2.0,B are merged as 2.0 and same way for 3.0 and 4.0

    Read the article

  • DB Interface Design Optimization: Is it better to optimise for Fewer requests of smaller data size?

    - by Overflow
    The prevailing wisdom in webservices/web requests in general is to design your api such that you use as few requests as possible, and that each request returns therefore as much data as is needed In database design, the accepted wisdom is to design your queries to minimise size over the network, as opposed to minimizing the number of queries. They are both remote calls, so what gives?

    Read the article

  • Passing a outside variable into a <asp:sqldatasource> tag. ASP.NET 2.0

    - by MadMAxJr
    I'm designing some VB based ASP.NET 2.0, and I am trying to make more use of the various ASP tags that visual studio provides, rather than hand writing everything in the code-behind. I want to pass in an outside variable from the Session to identify who the user is for the query. <asp:sqldatasource id="DataStores" runat="server" connectionstring="<%$ ConnectionStrings:MY_CONNECTION %>" providername="<%$ ConnectionStrings:MY_CONNECTION.ProviderName %>" selectcommand="SELECT THING1, THING2 FROM DATA_TABLE WHERE (THING2 IN (SELECT THING2 FROM RELATED_DATA_TABLE WHERE (USERNAME = @user)))" onselecting="Data_Stores_Selecting"> <SelectParameters> <asp:parameter name="user" defaultvalue ="" /> </SelectParameters> </asp:sqldatasource> And on my code behind I have: Protected Sub Data_Stores_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles Data_Stores.Selecting e.Command.Parameters("user").Value = Session("userid") End Sub Oracle squaks at me with ORA-01036, illegal variable name. Am I declaring the variable wrong in the query? I thought external variables share the same name with a @ prefixed. from what I understand, this should be placing the value I want into the query when it executes the select. EDIT: Okay, thanks for the advice so far, first error was corrected, I need to use : and not @ for the variable declaration in the query. Now it generates an ORA-01745 invalid host/bind variable name. EDIT AGAIN: Okay, looks like user was a reserved word. It works now! Thanks for other points of view on this one. I hadn't thought of that approach.

    Read the article

  • How do I go about link web content in a database with a nested set model?

    - by wb
    My nested set table is as follows. create table depts ( id int identity(0, 1) primary key , lft int , rgt int , name nvarchar(60) , abbrv nvarchar(20) ); Test departments. insert into depts (lft, rgt, name, abbrv) values (1, 14, 'root', 'r'); insert into depts (lft, rgt, name, abbrv) values (2, 3, 'department 1', 'd1'); insert into depts (lft, rgt, name, abbrv) values (4, 5, 'department 2', 'd2'); insert into depts (lft, rgt, name, abbrv) values (6, 13, 'department 3', 'd3'); insert into depts (lft, rgt, name, abbrv) values (7, 8, 'sub department 3.1', 'd3.1'); insert into depts (lft, rgt, name, abbrv) values (9, 12, 'sub department 3.2', 'd3.2'); insert into depts (lft, rgt, name, abbrv) values (10, 11, 'sub sub department 3.2.1', 'd3.2.1'); My web content table is as follows. create table content ( id int identity(0, 1) , dept_id int , page_name nvarchar(60) , content ntext ); Test content. insert into content (dept_id, page_name, content) values (3, 'index', '<h2>welcome to department 3!</h2>'); insert into content (dept_id, page_name, content) values (4, 'index', '<h2>welcome to department 3.1!</h2>'); insert into content (dept_id, page_name, content) values (6, 'index', '<h2>welcome to department 3.2.1!</h2>'); insert into content (dept_id, page_name, content) values (2, 'what-doing', '<h2>what is department 2 doing?/h2>'); I'm trying to query the correct page content (from the content table) based on the url given. I can easily accomplish this task with a root department. However, querying a department with multiple depths is proving to be a little harder. For example: http://localhost/departments.asp?d3/ (Should return <h2>welcome to department 3!</h2>) http://localhost/departments.asp?d2/what-doing (Should return <h2>what is department 2 doing?</h2>) I'm not sure if this can be create in one query or if there will need to be a recursive function of some sort. Also, if there is nothing after the last / then assume we want the index page. How can this be accomplished? Thank you.

    Read the article

  • Need to use query column value in nested subquery

    - by Dustin
    It seems I cannot use a column from the parent query in a sub query. How can I refactor this query to get what I need? dbo.func_getRelatedAcnts returns a table of related accounts (all children from a given account). Events and Profiles are related to accounts. SELECT COUNT(r.reg_id) FROM registrations r JOIN profiles p ON (r.reg_frn_pro_id = p.pro_id) JOIN events e ON (r.reg_frn_evt_id = e.evt_id) WHERE evt_frn_acnt_id NOT IN (SELECT * FROM dbo.func_getRelatedAcnts(p.pro_frn_acnt_id))

    Read the article

  • FluentNHibernate error -- "Invalid object name"

    - by goober
    I'm attempting to do the most simple of mappings with FluentNHibernate & Sql2005. Basically, I have a database table called "sv_Categories". I'd like to add a category, setting the ID automatically, and adding the userid and title supplied. Database table layout: CategoryID -- int -- not-null, primary key, auto-incrementing UserID -- uniqueidentifier -- not null Title -- varchar(50) -- not null Simple. My SessionFactory code (which works, as far as I can tell): _SessionFactory = Fluently.Configure().Database( MsSqlConfiguration.MsSql2005 .ConnectionString(c => c.FromConnectionStringWithKey("SVTest"))) .Mappings(x => x.FluentMappings.AddFromAssemblyOf<CategoryMap>()) .BuildSessionFactory(); My ClassMap code: public class CategoryMap : ClassMap<Category> { public CategoryMap() { Id(x => x.ID).Column("CategoryID").Unique(); Map(x => x.Title).Column("Title").Not.Nullable(); Map(x => x.UserID).Column("UserID").Not.Nullable(); } } My Class code: public class Category { public virtual int ID { get; private set; } public virtual string Title { get; set; } public virtual Guid UserID { get; set; } public Category() { // do nothing } } And the page where I save the object: public void Add(Category catToAdd) { using (ISession session = SessionProvider.GetSession()) { using (ITransaction Transaction = session.BeginTransaction()) { session.Save(catToAdd); Transaction.Commit(); } } } I receive the error Invalid object name 'Category'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'Category'. I think it might be that I haven't told the CategoryMap class to use the "sv_Categories" table, but I'm not sure how to do that. Any help would be appreciated. Thanks!

    Read the article

  • Storing object into cache using Linq classes and velocity

    - by Arun
    I careated couple of linq classes & marked the datacontext as unidirectional. Out of four classes; one is main class while other three are having the one to many relationship with first one; When I load the object of main class & put into the memory OR serialize it into an XML file; I never get the child class data while it is maked as DataContractAttribute. How can I force object to put the child class data into XML file or into cache ?

    Read the article

  • Union Distinct rows but order them by number of occurrences in mysql

    - by Baversjo
    Hi I have the following query: SELECT o.id,o.name FROM object o WHERE ( o.description LIKE '%Black%' OR o.name LIKE '%Black%' ) UNION ALL SELECT o2.id,o2.name FROM object o2 WHERE ( o2.description LIKE '%iPhone%' OR o2.name LIKE '%iPhone%' ) Which procude the following: id name 2 New Black iPhone 1 New White iPhone 2 New Black iPhone I would like to UNION DISTINCT, but I would also like the result ordered by the number of occurrences of each identical row (primary: id).

    Read the article

  • Select rows in random order and then reverse it

    - by Faruz
    I need to select rows in random order and return a query which holds the rows in both regular order and in reverse order. This is done to simulate a fantasy draft for a basketball game I'm working on. For example, I need a result set as followed: team1 1 team2 2 team6 3 team9 4 team9 5 team6 6 team2 7 team1 8 As you can see, the first four teams are random then then following four are in reverse order. Hope I managed to explain the problem, if not - please comment and I'll explain further.

    Read the article

  • Filling a LOV in Oracle Apex based on data in another text box

    - by Martin Pugh
    I am fairly new to Oracle Apex, and have a problem. Our application currently has a method of entering data, with several text boxes and Optional List of Values. I would like to have an LOV based on information in another text box like so: select APPOINTMENT_ID PATIENT_ID from APPOINTMENT where PATIENT_ID = :P9_PAT_NUM where P9_PAT_NUM is a patient number in a text box. However, this would apparently only work if the text box has already been submitted, else it assumes the text box is null. Is there any way to get this working with an LOV, or perhaps some other method?

    Read the article

  • Update with inner join ?

    - by phenevo
    I have two databases: DB1 and DB2 How to do something like: update myServer.DB1.dbo.hotels.Name = myServer.DB2.dbo.hotels.Name join myServer.DB2.dbo.hotels on myServer.DB2.dbo.hotels.Code= myServer.DB1.dbo.hotels.Code where myServer.DB2.dbo.hotels.CountryCoe != myServer.DB1.dbo.hotels.CountryCode

    Read the article

  • How to do this query?

    - by Damiano
    Hello everybody! I have a mysql table with these columns: ID (auto-increment) ID_BOOK (int) PRICE (double) DATA (date) I know two ID_BOOK values, example, 1 and 2. QUERY: I have to extract all the PRICE (of the ID_BOOK=1 and ID_BOOK=2) where DATA is the same! Table example: 1 1 10.00 2010-05-16 2 1 11.00 2010-05-15 3 1 12.00 2010-05-14 4 2 18.00 2010-05-16 5 2 11.50 2010-05-15 Result example: 1 1 10.00 2010-05-16 4 2 18.00 2010-05-16 2 1 11.00 2010-05-15 5 2 11.50 2010-05-15 ID_BOOK=2 hasn't 2010-05-14 so i jump it. Thank you so much!

    Read the article

  • Check for active connection in NHibernate

    - by Dofs
    I have a system with a few different databases, and I would like to check if a certain database is down, and if so display a message to the user. Is it possible in NHibernate to check if there is an active connection to the database, without having to request data and then catch the exception?

    Read the article

  • Exporting many tables on Oracle

    - by Adomas
    Hi, I would like to know, how to export many tables from oracle DB. I use exp.exe, create file expdat.dmp and so on. I choose to export only tables and there I must write which ones. Is there any chance of getting all of them? thanks

    Read the article

  • Oracle sql developer can acess from loalhost but not ip address

    - by Welcome Always
    Here i have installed widows server 2008 64 orale 11g r2 64 now i want to access this from the ip address that i have set to my server not working i can't access trough the ip address.... when i use on my application .....local host it can get connected but when i add the ip for example 10.0.0.2 it's not connecting now simply also on local when i write the ip address it's not connecting but with localhost works fine..... so even i have used listener. regards

    Read the article

  • kohana quotes in query.

    - by peter
    hey, i want to format a date in mysql using DATE_FORMAT(tblnews.datead, '%M %e, %Y, %l:%i%p') i cant seem to get the quotes right , so i keep getting errors. how would you put this in a query?

    Read the article

  • Is there a library / tool to query MySQL data files (MyISAM / InnoDB) without the server? (the SQLit

    - by MGW
    Oftentimes I want to query my MySQL data directly without a server running or without having access to the server (but having read / write rights to the files). Is there a tool or maybe even a library around to query MySQL data files like it is possible with SQLite? I'm specifically looking for InnoDB and MyISAM support. Performance is not a factor. I don't have any knowledge about MySQL internals, but I presume it should be possible to do and not too hard to get the specific code out? Thank you for any suggestions!

    Read the article

  • Database Design Question: GUID + Natural Numbers

    - by Alan
    For a database I'm building, I've decided to use natural numbers as the primary key. I'm aware of the advantages that GUID's allow, but looking at the data, the bulk of row's data were GUID keys. I want to generate XML records from the database data, and one problem with natural numbers is that I don't want to expose my database key's to the outside world, and allow users to guess "keys." I believe GUID's solve this problem. So, I think the solution is to generate a sparse, unique iD derived from the natural ID (hopefully it would be 2-way), or just add an extra column in the database and store a guid (or some other multibyte id) The derived value is nicer because there is no storage penalty, but it would be easier to reverse and guess compared to a GUID. I'm (buy) curious as to what others on SO have done, and what insights they have.

    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 | 724 725 726 727 728 729 730 731 732 733 734 735  | Next Page >