Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

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 688/1156 | < Previous Page | 684 685 686 687 688 689 690 691 692 693 694 695  | Next Page >

  • Query - Trying to SUM one field based on content of another field

    - by ShaneL
    Table: DayOfWeek Enrollments Monday 35 Monday 12 Saturday 25 Tuesday 15 Monday 9 Tuesday 15 Basically I'm trying to sum the total enrolments for each day. so the Output will look like: DayOfWeek Enrollments Monday 56 Saturday 25 Tuesday 30 I've spent around 4 hours trying to work this out trying many many different ways but no luck. The problem I'm having is i can count how many enrollments for each day but can't have it aligned with the correct day when i run the query e.g. I want The total to be on the same line as the day it was calculated from. (I hope that is clear enough)

    Read the article

  • select row from table and substitute a field with one from another column if it exists

    - by EarthMind
    I'm trying construct a PostgreSQL query that does the following but so far my efforts have been in vain. Problem: There are two tables: A and B. I'd like to select all columns from table A (having columns: id, name, description) and substitute the "A.name" column with the value of the column "B.title" from table B (having columns: id, table_A_id title, langcode) where B.table_A_id is 5 and B.langcode is "nl" (if there are any rows). My attempts: SELECT A.name, case when exists(select title from B where table_A_id = 5 and langcode= 'nl') then B.title else A.name END FROM A, B WHERE A.id = 5 and B.table_A_id = 5 and B.langcode = 'nl' -- second try: SELECT COALESCE(B.title, A.name) as name from A, B where A.id = 5 and B.table_A_id = 5 and exists(select title from B where table_A_id = 5 and langcode= 'nl') I've tried using a CASE and COALESCE() but failed due to my inexperience with both concepts. Thanks in advance.

    Read the article

  • select row from table and substitute a field with one from another column if not null

    - by EarthMind
    I'm trying construct a PostgreSQL query that does the following but so far my efforts have been in vain. Problem: There are two tables: A and B. I'd like to select all columns from table A (having columns: id, name, description) and substitute the "A.name" column with the value of the column "B.title" from table B (having columns: id, table_A_id title, langcode) where B.table_A_id is 5 and B.langcode is "nl" (if there are any rows). I've tried using a CASE and COALESCE() but failed due to my inexperience with both concepts. Thanks in advance.

    Read the article

  • How can I synchronize one set of data with another?

    - by RenderIn
    I have an old database and a new database. The old records were converted to the new database recently. All our old applications continue to point to the old database, but the new applications point to the new database. Currently the old database is the only one being updated, so throughout the day the new database becomes out of sync. It is acceptable for the new database to be out of sync for a day, so until all our applications are pointed to the new database I just need to write a nightly cron job that will bring it up to date. I do not want to purge the new database and run the complete conversion script each night, as that would reduce uptime and would create a mess in our auditing of that table. I'm thinking about selecting all the data from the old database, converting it to the new database structure in memory, and then checking for the existence of each record before inserting it in the new database. After that's done, I'd select everything from the new database and check if it exists in the old one, and if not delete it. Is this the simplest way to do this?

    Read the article

  • query to return three records for each customer application based on the options declared in the pre

    - by kumarreddy
    tables look like this table1---customer application columns--- application id--primary key, name, ssn, ... ... table2----balance(actually its a view) columns--- amount balance, application id ...... ...... table3 ---- options columns--- optionid, option value(1,2,3,4), ...... ........ .... table4 ----- ratios columns--- ratios id, option value, ratio value, applicationid(have to think about it), ........ table 4(detail) option value, Ratios 1 ----- 30 1 ----- 40 1 ----- 30 2 ---- 100 2 ----- 0 2 ------ 0 3 ---- 60 3 ------ 30 3 ----- 10 4 ---- 50 4 ----- 30 4 ----- 20 as is the case...now i need to get three records for each customer application with varying balances in proportion of ratios declared in table 4 corresponding to option values...... plz let me know where i was unclear about returning records thanks in advance

    Read the article

  • pgSQL query error

    - by running4surival
    i tried using this query: "SELECT * FROM guests WHERE event_id=".$id." GROUP BY member_id;" and I'm getting this error: ERROR: column "guests.id" must appear in the GROUP BY clause or be used in an aggregate function can anyone explain how i can work around this?

    Read the article

  • custom sorting or ordering a table without resorting the whole shebang

    - by fuugus
    for ten years we've been using the same custom sorting on our tables, i'm wondering if there is another solution which involves fewer updates, especially since today we'd like to have a replication/publication date and would'nt like to have our replication replicate unnecessary entries. i had a look into nested sets, but it does'nt seem to do the job for us. base table: id | a_sort ---+------- 1 10 2 20 3 30 after inserting insert into table (a_sort) values(15) an entry at the second position. id | a_sort ---+------- 1 10 2 20 3 30 4 15 ordering the table with select * from table order by a_sort and resorting all the a_sort entries, updating at least id=(2,3,4) will of course produce the desired output id | a_sort ---+------- 1 10 4 20 2 30 3 40 the column names, the column count, datatypes, a possible join, possible triggers or the way the resorting is done is/are irrelevant to the problem. also we've found some pretty neat ways to do this task fast. only; how the heck can we reduce the updates in the db to 1 or 2 max. seems like an awfully common problem. the captain obvious in me thougth once "use an a_sort float(53), insert using a fixed value of ordervaluefirstentry+abs(ordervaluefirstentry-ordervaluenextentry)/2".. but this would only allow around 1040 "in between" entries - so never resorting seems a bit problematic ;)

    Read the article

  • MySQL, select from different table... IF

    - by gubbfett
    I'm having a small trouble since it was a long time ago i studies databases and querys. For example i'll have two tables for cd:s, one with data and one with alternative translations. In the CD-table i have the original language, and it looks something like this Table for CDs (cds): id | name | language ----------------------- 1 | aaa | en 2 | bbb | en 3 | ccc | fi Table for languages (languages): cd_id | language | name ----------------------- 1 | fi | AAA 1 | de | AAACHTUNG 3 | en | CCC Now, i want to get all these cd:s in for example german, if there's no translation made i want it to be in the original language... How can i do this?

    Read the article

  • Is it possible to modify the value of a record's primary key in Oracle when child records exist?

    - by Chris Farmer
    I have some Oracle tables that represent a parent-child relationship. They look something like this: create table Parent ( parent_id varchar2(20) not null primary key ); create table Child ( child_id number not null primary key, parent_id varchar2(20) not null, constraint fk_parent_id foreign key (parent_id) references Parent (parent_id) ); This is a live database and its schema was designed long ago under the assumption that the parent_id field would be static and unchanging for a given record. Now the rules have changed and we really would like to change the value of parent_id for some records. For example, I have these records: Parent: parent_id --------- ABC123 Child: child_id parent_id -------- --------- 1 ABC123 2 ABC123 And I want to modify ABC123 in these records in both tables to something else. It's my understanding that one cannot write an Oracle update statement that will update both parent and child tables simultaneously, and given the FK constraint, I'm not sure how best to update my database. I am currently disabling the fk_parent_id constraint, updating each table independently, and then enabling the constraint. Is there a better, single-step way to update this content?

    Read the article

  • Filter Phrase Query

    - by alsuelo
    I try to filter a phrase to make a search in my website i've this query, this code working with one word but when i type wit more than one isn't working becuase the print is without spaces. $phrase = $this->getState($this->context.".filter_phrase"); printf("Original string: %s\n", $phrase); if(!empty($phrase)) { $escaped = $db->escape($phrase, true); printf("Escaped string: %s\n", $escaped); $quoted = $db->quote("%" . $escaped . "%" , false); $query->where ('a.title LIKE ' .$quoted); } Example i type king and the output is king , when i type the king the output is theking, i want to know if exist any way to conserve the blank spaces.

    Read the article

  • Table in DB for generating primary keys?

    - by Sapphire
    Do you ever use a separate table for "generating" artificial primary keys for DB (and why)? What I mean is to have a table with two columns, table name and current ID - with which you could get new "ID" for some table by simply locking the row with that table name, getting the current value of the key, increment it by one, and unlock the row. Why would you prefer this over standard integer identity column? P.S. The "idea" is from Fowlers Patterns of Enterprise Application Architecture, btw...

    Read the article

  • T-SQL Self Join in combination with aggregate function

    - by Nick
    Hi, i have the following table. CREATE TABLE [dbo].[Tree]( [AutoID] [int] IDENTITY(1,1) NOT NULL, [Category] [varchar](10) NULL, [Condition] [varchar](10) NULL, [Description] [varchar](50) NULL, CONSTRAINT [PK_Tree] PRIMARY KEY CLUSTERED ( [AutoID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO the data looks like this: INSERT INTO [Test].[dbo].[Tree] ([Category] ,[Condition] ,[Description]) VALUES ('1','Alpha','Type 1') INSERT INTO [Test].[dbo].[Tree] ([Category] ,[Condition] ,[Description]) VALUES ('1','Alpha','Type 1') INSERT INTO [Test].[dbo].[Tree] ([Category] ,[Condition] ,[Description]) VALUES ('2','Alpha','Type 2') INSERT INTO [Test].[dbo].[Tree] ([Category] ,[Condition] ,[Description]) VALUES ('2','Alpha','Type 2') go I try now to do the following: SELECT Category,COUNT(*) as CategoryCount FROM Tree where Condition = 'Alpha' group by Category but i wish also to get the Description for each Element. I tried several subqueries, self joins etc. i always come to the problem that the subquery cannot return more than one record. The problem is caused by a poor database design which i cannot change and i run out of ideas to get this done in a single query ;-(

    Read the article

  • Speeding up a group by date query on a big table in postgres

    - by zaius
    I've got a table with around 20 million rows. For arguments sake, lets say there are two columns in the table - an id and a timestamp. I'm trying to get a count of the number of items per day. Here's what I have at the moment. SELECT DATE(timestamp) AS day, COUNT(*) FROM actions WHERE DATE(timestamp) >= '20100101' AND DATE(timestamp) < '20110101' GROUP BY day; Without any indices, this takes about a 30s to run on my machine. Here's the explain analyze output: GroupAggregate (cost=675462.78..676813.42 rows=46532 width=8) (actual time=24467.404..32417.643 rows=346 loops=1) -> Sort (cost=675462.78..675680.34 rows=87021 width=8) (actual time=24466.730..29071.438 rows=17321121 loops=1) Sort Key: (date("timestamp")) Sort Method: external merge Disk: 372496kB -> Seq Scan on actions (cost=0.00..667133.11 rows=87021 width=8) (actual time=1.981..12368.186 rows=17321121 loops=1) Filter: ((date("timestamp") >= '2010-01-01'::date) AND (date("timestamp") < '2011-01-01'::date)) Total runtime: 32447.762 ms Since I'm seeing a sequential scan, I tried to index on the date aggregate CREATE INDEX ON actions (DATE(timestamp)); Which cuts the speed by about 50%. HashAggregate (cost=796710.64..796716.19 rows=370 width=8) (actual time=17038.503..17038.590 rows=346 loops=1) -> Seq Scan on actions (cost=0.00..710202.27 rows=17301674 width=8) (actual time=1.745..12080.877 rows=17321121 loops=1) Filter: ((date("timestamp") >= '2010-01-01'::date) AND (date("timestamp") < '2011-01-01'::date)) Total runtime: 17038.663 ms I'm new to this whole query-optimization business, and I have no idea what to do next. Any clues how I could get this query running faster?

    Read the article

  • Design pattern for data entry forms with LINQ2SQL

    - by petebob796
    I am about to start a new winforms data entry application, it already has the database designed which I am comfortable with. I was going to use LINQ2SQL to access the tables to keep things type safe but am now wondering about design patterns, something I am just getting into. Since LINQ is giving me objects to use should I still create classes in between to hold the validation code and helper methods or should these just go in with the UI? It just seems I will end up with classes sat in between with little code which will cause the UI classes to have code just getting and setting values in the intermediate class and returning from validation to flag errors... Any good reading on this? Should I consider the entity framework (or similar) instead?

    Read the article

  • Date type in oracle does not include time values

    - by Matt
    I have a PHP application using an Oracle XE database. Whenever I add a date the hours minutes, and seconds seem to get left out. Is there some special format, or type I should use to be able to store this? I have tried using to_date, and specifying the format I am using. Many thanks for any suggestions from this confused MySql dveloper.

    Read the article

  • Comma separated values in a database field

    - by John Doe
    I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example: Id | Code ---------------------- 0001 | IN,ON,ME,OH 0002 | ON,VI,AC,ZO 0003 | QA,PS,OO,ME What I'm trying to do is create a stored procedure so that I can pass in a codes like "ON,ME" and have it return every product that contains the "ON" or "ME" code. Since the codes are comma separated, I don't know how I can split those and search them. Is this possible using only TSQL? Edit: It's a mission critical table. I don't have the authority to change it.

    Read the article

  • Crash when checking BOF property of pessimistic locked ADO recordset

    - by Patrick
    Bit of an odd one for you: I've got two connections to a database, on one I've opened a _RecordsetPtr with a pessimistic lock. I can no longer send an UPDATE command on the other connection. I can send a SELECT command on the second connection and data is returned. If I use a read only lock then there are no problems however when I use a pessimistic lock on the second connection as well I can check the State == adStateOpen but the program hangs when I test the BOF property! If I don't test the BOF property and try to call moveNext on the second connection the software hangs If I do neither of these I am able to access the data via the second connection but trying to access the data from the first connection causes the software to hang. Any one seen anything similar as I'm a bit stuck? EDIT : it wasn't hanging, someone had put a 30 minute timeout on the connection and I wasn't waiting that long while testing...

    Read the article

  • PHP timeslot booking*

    - by boyee007
    regarding of this question.. PHP Booking timeslot I tried 'GROUP BY' id_timeslot still didnt work, as its only showing the booked timeslot not available i tried that solution, but give me an error and not quite understand how to use 'coelence' table timeslot (id_timeslot integer); table doctor (id_doctor integer); table bookslot (id_bookslot, id_doctor, id_timeslot integer); insert into doctor (id_doctor) values (1 = doc_A), (2 = doc_B), (3 = doc_C); insert into TimeSlot (id_timeslot) values (1 = 10:00:00), (2 = 10:15:00), (3 = 10:30:00), (4 = 10:45:00); insert into bookslot (id_doctor,id_timeslot) values (1,1), (1,5), (2,1), (2,4), (3,1); Join mysql table $q = $mysqli->query("SELECT * FROM bookslot RIGHT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot LEFT JOIN doctor ON bookslot.id_doctor = doctor.id_doctor "); echoing result and checking if it matches todays date or else set available while($r = $q->fetch_array(MYSQLI_ASSOC)) : echo '<tr>'; echo '<td align="center">' . $r['times'] . '</td>'; if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 1): echo '<td><a href="#available" class="booked">booked</a></td>'; else : echo '<td><a href="#" class="available">available</a></td>'; endif; if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 2): echo '<td><a href="#available" class="booked">booked</a></td>'; else : echo '<td><a href="#" class="available">available</a></td>'; endif; if($r['booked_date'] == date('Y-m-d') && $r['id_doctor'] == 3): echo '<td><a href="#available" class="booked">booked</a></td>'; else : echo '<td><a href="#" class="available">available</a></td>'; endif; echo '</tr>'; endwhile; result from webpage and i want the result look like: id_timeslot doc_A doc_B doc_C ---------------------------------------------- 1 booked booked booked 2 available available available 3 available available available 4 available booked available 5 booked available available Any other solution please!

    Read the article

  • SqlCeCommand ExecuteNonQuery performance issue

    - by Michael
    I've been asked to resolve an issue with a .Net/SqlServerCe application. Specifically, after repeated inserts against the db, performance becomes increasingly degraded. In one instance at ~200 rows, in another at ~1000 rows. In the latter case the code being used looks like this: Dim cm1 As System.Data.SqlServerCe.SqlCeCommand = cn1.CreateCommand cm1.CommandText = "INSERT INTO Table1 Values(?,?,?,?,?,?,?,?,?,?,?,?,?)" For j = 0 To ds.Tables(0).Rows.Count - 1 'this is 3110 For i = 0 To 12 cm1.Parameters(tbl(i, 0)).Value = Vals(j,i) 'values taken from a different db Next cm1.ExecuteNonQuery() Next The specifics aren't super important (like what 'tbl' is, etc) but rather whether or not this code should be expected to handle this number of inserts, or if the crawl I'm witnessing is to be expected.

    Read the article

  • count(*) vs count(column-name) - which is more correct?

    - by bread
    Does it make a difference if you do count(*) vs count(column-name) as in these two examples? I have a tendency to always write count(*) because it seems to fit better in my mind with the notion of it being an aggregate function, if that makes sense. But I'm not sure if it's technically best as I tend to see example code written without the * more often than not. count(*): select customerid, count(*), sum(price) from items_ordered group by customerid having count(*) > 1; vs. count(column-name): SELECT customerid, count(customerid), sum(price) FROM items_ordered GROUP BY customerid HAVING count(customerid) > 1;

    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 | 684 685 686 687 688 689 690 691 692 693 694 695  | Next Page >