Search Results

Search found 27468 results on 1099 pages for 'sql quiz'.

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 609/1099 | < Previous Page | 605 606 607 608 609 610 611 612 613 614 615 616  | Next Page >

  • Unit testing a SQL code generator

    - by Tom H.
    The team I'm on is currently writing code in TSQL to generate TSQL code that will be saved as scripts and later run. We're having a little difficulty in separating our unit tests between testing the code generator parts and testing the actual code that they generate. I've read through another similar question, but I was hoping to get some specific examples of what kind of unit test cases we might have. As an example, let's say that I have a bit of code that simply generates a DROP statement for a view, given the view schema and name. Do I just test that the generated code matches some expected outcome using string comparisons and then in a later integration or system test make sure that the drop actually drops the view if it exists, does nothing if the view doesn't exist, or raises an error if the view is one that we are marking as not allowing a drop? Thanks for any advice!

    Read the article

  • MSSQL STOREDPROC SELECTING FROM A FIELD FROM 1 TABLE USING LIKE TO CREATE MORE THAN 1 COLUM IN A DAT

    - by djshortbus
    I have a ASPX.NET DataGrid and im trying to USE a select LIKE 'X'% from a table that has 1 field called location. im trying to display the locations that start with a certain letter (example wxxx,axxx,fxxx,) in different columns in my data grid. SELECT DISTINCT LM.LOCATION AS '0 LOCATIONS' , LM.COUNTLEVEL AS 'COUNTLEVEL' FROM SOH S WITH(NOLOCK) JOIN LOCATIONMASTER LM ON LM.LMID = S.LMID WHERE LM.COUNTLEVEL = 1 AND LM.LOCATION NOT IN ('RECOU','PROBLEM','TOSTOCK','PYXVLOC') AND LM.LOCATION LIKE '0%' SELECT DISTINCT LM.LOCATION AS 'A LOCATIONS' , LM.COUNTLEVEL AS 'COUNTLEVEL' FROM SOH S WITH(NOLOCK) JOIN LOCATIONMASTER LM ON LM.LMID = S.LMID WHERE LM.COUNTLEVEL = 1 AND LM.LOCATION NOT IN ('RECOU','PROBLEM','TOSTOCK','PYXVLOC') AND LM.LOCATION LIKE 'A%'

    Read the article

  • help with exception handling in linq

    - by stackoverflowuser
    I have the following code to retrieve customer name, total (orders ), sum (order details) for reach customer in Northwind database. The problem with below code is that it raises an exception since a few customers dont have any entry in orders table. I know using the query syntax (join) the exception can be avoided. I want to know if the same can be handled with the extension method syntax. CustomerOrderDataContext db = new CustomerOrderDataContext(); var customerOrders = db.Customers.Select(c => new { CompanyName = c.CompanyName, TotalOrders = c.Orders.Count(), TotalQuantity = c.Orders.SelectMany(o => o.Order_Details).Sum(o=>o.Quantity) });

    Read the article

  • Is it possible to have a tableless select with multiple rows?

    - by outis
    A SELECT without a FROM clause gets us a multiple columns without querying a table: SELECT 17+23, REPLACE('bannanna', 'nn', 'n'), RAND(), CURRENT_TIMESTAMP; How can we write a query that results in multiple rows without referring to a table? Basically, abuse SELECT to turn it into a data definition statement. The result could have a single column or multiple columns. I'm most interested in a DBMS neutral answer, but others (e.g. based on UNPIVOT) are welcome. There's no technique application behind this question; it's more theoretical than practical.

    Read the article

  • Problem with LINQ in C#

    - by David Bonnici
    I am encountering a problem when using LINQ in C#, I am constantly getting "Specified cast is not valid". This is what I am trying to do. I create a class in which I declare all the columns of the table. [Table(Name="tbl_Aff")] public class Affiliate { [Column] public string name; [Column] public string firstname; [Column] public string surname; [Column] public string title; } I then declare a strongly typed DataContext in which I declare all Table collections as members of the context. public partial class Database : DataContext { public Table affiliate; public Database() : base(Settings.getConnectionString()) { } //This method gets the connection string by reading from an XML file. } public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Database database = new Database(); try { var q = from a in database.affiliate select a; foreach (var aff in q) // Here I get the error "Specified cast is not valid" { lblMessage.InnerHtml += aff.name + ""; } } catch (Exception ex) { System.Console.WriteLine(ex.Message); } } }

    Read the article

  • How to manage multiple versions of the same record

    - by Darvis Lombardo
    I am doing short-term contract work for a company that is trying to implement a check-in/check-out type of workflow for their database records. Here's how it should work... 1) A user creates a new entity within the application. There are about 20 related tables that will be populated in addition to the main entity table. 2) Once the entity is created the user will mark it as the master. 3) Another user can make changes to the master only by "checking out" the entity. Multiple users can checkout the entity at the same time. 4) Once the user has made all the necessary changes to the entity, they put it in a "needs approval" status. 5) After an authorized user reviews the entity, they can promote it to master which will put the original record in a tombstoned status. The way they are currently accomplishing the "check out" is by duplicating the entity records in all the tables. The primary keys include EntityID + EntityDate, so they duplicate the entity records in all related tables with the same EntityID and an updated EntityDate and give it a status of "checked out". When the record is put into the next state (needs approval), the duplication occurs again. Eventually it will be promoted to master at which time the final record is marked as master and the original master is marked as dead. This design seems hideous to me, but I understand why they've done it. When someone looks up an entity from within the application, they need to see all current versions of that entity. This was a very straightforward way for making that happen. But the fact that they are representing the same entity multiple times within the same table(s) doesn't sit well with me, nor does the fact that they are duplicating EVERY piece of data rather than only storing deltas. I would be interested in hearing your reaction to the design, whether positive or negative. I would also be grateful for any resoures you can point me to that might be useful for seeing how someone else has implemented such a mechanism. Thanks! Darvis

    Read the article

  • How to avoid timestamp issue in a long query?

    - by pingi
    Hi, I have the following 2 tables: items: id int primary key bla text events: id_items int num int when timestamp without time zone ble text composite primary key: id_items, num and want to select to each item the most recent event (the newest 'when'). I wrote an request, but I don't know if it could be written more efficiently. Also on PostgreSQL there is a issue with comparing Timestamp objects: 2010-05-08T10:00:00.123 == 2010-05-08T10:00:00.321 so I select with 'MAX(num)' Any thoughts how to make it better? Thanks. SELECT i.*, ea.* FROM items AS i JOIN ( SELECT t.s AS t_s, t.c AS t_c, max(e.num) AS o FROM events AS e JOIN ( SELECT DISTINCT id_item AS s, MAX(when) AS c FROM events GROUP BY s ORDER BY c ) AS t ON t.s = e.id_item AND e.when = t.c GROUP BY t.s, t.c ) AS tt ON tt.t_s = i.id JOIN events AS ea ON ea.id_item = tt.t_s AND ea.cas = tt.t_c AND ea.num = tt.o;

    Read the article

  • Linq is returning too many results when joined

    - by KallDrexx
    In my schema I have two database tables. relationships and relationship_memberships. I am attempting to retrieve all the entries from the relationship table that have a specific member in it, thus having to join it with the relationship_memberships table. I have the following method in my business object: public IList<DBMappings.relationships> GetRelationshipsByObjectId(int objId) { var results = from r in _context.Repository<DBMappings.relationships>() join m in _context.Repository<DBMappings.relationship_memberships>() on r.rel_id equals m.rel_id where m.obj_id == objId select r; return results.ToList<DBMappings.relationships>(); } _Context is my generic repository using code based on the code outlined here. The problem is I have 3 records in the relationships table, and 3 records in the memberships table, each membership tied to a different relationship. 2 membership records have an obj_id value of 2 and the other is 3. I am trying to retrieve a list of all relationships related to object #2. When this linq runs, _context.Repository<DBMappings.relationships>() returns the correct 3 records and _context.Repository<DBMappings.relationship_memberships>() returns 3 records. However, when the results.ToList() executes, the resulting list has 2 issues: 1) The resulting list contains 6 records, all of type DBMappings.relationships(). Upon further inspection there are 2 for each real relationship record, both are an exact copy of each other. 2) All relationships are returned, even if m.obj_id == 3, even though objId variable is correctly passed in as 2. Can anyone see what's going on because I've spent 2 days looking at this code and I am unable to understand what is wrong. I have joins in other linq queries that seem to be working great, and my unit tests show that they are still working, so I must be doing something wrong with this. It seems like I need an extra pair of eyes on this one :)

    Read the article

  • MSDN about stored procedure default return value

    - by Ilya
    Hello, Could anyone point exactly where MSDN says thet every user stored procedure returns 0 by default if no error happens? In other words, could I be sure that example code given below when being a stored procedure IF someStatement BEGIN RETURN 1 END should always return zero if someStatement is false and no error occurs? I know that it actually works this way, but I failed to find any explicit statement about this from Microsoft.

    Read the article

  • Delete backup files older than 7 days

    - by rockbala
    Using osql command, SSQL backup of a database is created. It is saved to Disk. Then renamed to match the date of the day backup was taken. All these files are saved in a single folder all the time. for example: Batch1.bat does the following 1) Created backup.bak 2) renamed to backup 12-13-2009.bak (this is done by the combination of % ~ - etc to get the date parameter) This is now automated to take backup everyday by Task scheduler in Windows. Can the batch file also be modified to delete backup files older than 7 days? if so, how ? If it is not possible via batch file, other than manually deleting the files are there any other alternatives to automate the deleting job ? Thanks in advance, Balaji S

    Read the article

  • Precision of Interval for PL/SQL Function value

    - by Gary
    Generally, when you specify a function the scale/precision/size of the return datatype is undefined. For example, you say FUNCTION show_price RETURN NUMBER or FUNCTION show_name RETURN VARCHAR2. You are not allowed to have FUNCTION show_price RETURN NUMBER(10,2) or FUNCTION show_name RETURN VARCHAR2(20), and the function return value is unrestricted. This is documented functionality. Now, I get an precision error (ORA-01873) if I push 9999 hours (about 400 days) into the following. The limit is because the default days precision is 2 DECLARE v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return INTERVAL DAY TO SECOND IS v_hhmm INTERVAL DAY (4) TO SECOND(0); BEGIN v_hhmm := to_dsinterval('PT9999H'); RETURN v_hhmm; -- END hhmm_to_interval; BEGIN v_int := hhmm_to_interval; end; / and it won't allow the precision to be specified directly as part of the datatype returned by the function. DECLARE v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return INTERVAL DAY (4) TO SECOND IS v_hhmm INTERVAL DAY (4) TO SECOND(0); BEGIN v_hhmm := to_dsinterval('PT9999H'); RETURN v_hhmm; -- END hhmm_to_interval; BEGIN v_int := hhmm_to_interval; end; / I can use a SUBTYPE DECLARE subtype t_int is INTERVAL DAY (4) TO SECOND(0); v_int INTERVAL DAY (4) TO SECOND(0); FUNCTION hhmm_to_interval return t_int IS v_hhmm INTERVAL DAY (4) TO SECOND(0); BEGIN v_hhmm := to_dsinterval('PT9999H'); RETURN v_hhmm; -- END hhmm_to_interval; BEGIN v_int := hhmm_to_interval; end; / Any drawbacks to the subtype approach ? Any alternatives (eg some place to change a default precision) ? Working with 10gR2.

    Read the article

  • Does the optimizer filter subqueries with outer where clauses

    - by Mongus Pong
    Take the following query: select * from ( select a, b from c UNION select a, b from d ) where a = 'mung' Will the optimizer generally work out that I am filtering a on the value 'mung' and consequently filter mung on each of the queries in the subquery. OR will it run each query within the subquery union and return the results to the outer query for filtering (as the query would perhaps suggest) In which case the following query would perform better : select * from ( select a, b from c where a = 'mung' UNION select a, b from d where a = 'mung' ) Obviously query 1 is best for maintenance, but is it sacrificing much performace for this? Which is best?

    Read the article

  • mysql whats wrong with this query?

    - by Hailwood
    I'm trying to write a query that selects from four tables campaignSentParent csp campaignSentEmail cse campaignSentFax csf campaignSentSms css Each of the cse, csf, and css tables are linked to the csp table by csp.id = (cse/csf/css).parentId The csp table has a column called campaignId, What I want to do is end up with rows that look like: | id | dateSent | emailsSent | faxsSent | smssSent | | 1 | 2011-02-04 | 139 | 129 | 140 | But instead I end up with a row that looks like: | 1 | 2011-02-03 | 2510340 | 2510340 | 2510340 | Here is the query I am trying SELECT csp.id id, csp.dateSent dateSent, COUNT(cse.parentId) emailsSent, COUNT(csf.parentId) faxsSent, COUNT(css.parentId) smsSent FROM campaignSentParent csp, campaignSentEmail cse, campaignSentFax csf, campaignSentSms css WHERE csp.campaignId = 1 AND csf.parentId = csp.id AND cse.parentId = csp.id AND css.parentId = csp.id; Adding GROUP BY did not help, so I am posting the create statements. csp CREATE TABLE `campaignsentparent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `campaignId` int(11) NOT NULL, `dateSent` datetime NOT NULL, `account` int(11) NOT NULL, `status` varchar(15) NOT NULL DEFAULT 'Creating', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 cse/csf (same structure, different names) CREATE TABLE `campaignsentemail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parentId` int(11) NOT NULL, `contactId` int(11) NOT NULL, `content` text, `subject` text, `status` varchar(15) DEFAULT 'Pending', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=140 DEFAULT CHARSET=latin1 css CREATE TABLE `campaignsentsms` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parentId` int(11) NOT NULL, `contactId` int(11) NOT NULL, `content` text, `status` varchar(15) DEFAULT 'Pending', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=141 DEFAULT CHARSET=latin1

    Read the article

  • MYSQL join - reference external field from nested select?

    - by PHP thinker
    Is it allowed to reference external field from nested select? E.g. SELECT FROM ext1 LEFT JOIN (SELECT * FROM int2 WHERE int2.id = ext1.some_id ) as x ON 1=1 in this case, this is referencing ext1.some_id in nested select. I am getting errors in this case that field ext1.some_id is unknow. Is it possible? Is there some other way?

    Read the article

  • LINQ Query returns false when it should be true.

    - by deliriousDev
    I have the following LINQ query written by a former developer and it isn't working when it should. public bool IsAvailable(Appointment appointment) { var appointments = _appointmentRepository.Get; var shifts = _scheduleRepository.Get; var city = _customerRepository.Find(appointment.CustomerId).City ?? appointment.Customer.City; const int durationHour = 1; DateTime scheduledEndDate = appointment.ScheduledTime.Add(new TimeSpan(durationHour, 0, 0)); var inWorkingHours = shifts .Where(x => //Check if any available working hours x.Employee.City == city && x.ShiftStart <= appointment.ScheduledTime && x.ShiftEnd >= scheduledEndDate && //check if not booked yet !appointments .Where(a => (appointment.Id == 0 || a.Id != appointment.Id) && a.Employee.Id == x.Employee.Id && ( (a.ScheduledTime <= appointment.ScheduledTime && appointment.ScheduledTime <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) || (a.ScheduledTime <= scheduledEndDate && scheduledEndDate <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) )) .Select(a => a.Employee.Id) .Contains(x.Employee.Id) ); if (inWorkingHours.Any()) { var assignedEmployee = inWorkingHours.FirstOrDefault().Employee; appointment.EmployeeId = assignedEmployee.Id; appointment.Employee = assignedEmployee; return true; } return false; } The query is suppose to handle the following scenarios Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time But Does not match any employees in same city - (Return true, Assign as "Unassigned") Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time AND Employee for that shift is in the same city as the customer (Return True AND Assign to the employee) If the customer is NOT in the same city as an employee we assign the appointment as "Unassigned" as along as the scheduledTime is within an of the employees shift start/end times If the customer is in the same city as an employee we assign the appointment to one of the employees (firstOrdefault) and occupy that timeslot. Appointments CAN NOT overlap (Assigned Ones). Unassigned can't overlap each other. This query use to work (I've been told). But now it doesn't and I have tried refactoring it and various other paths with no luck. I am now on week two and just don't know where the issue in the query is or how to write it. Let me know if I need to post anything further. I have verified appointments, shifts, city all populate with valid data so the issue doesn't appear to be with null or missing data.

    Read the article

  • SqlServer2008 + expensive union

    - by Tim Mahy
    Hi al, we have 5 tables over which we should query with user search input throughout a stored procedure. We do a union of the similar data inside a view. Because of this the view can not be materialized. We are not able to change these 5 tables drastically (like creating a 6th table that contains the similar data of the 5 tables and reference that new one from the 5 tables). The query is rather expensive / slow what are our other options? It's allowed to think outside the box. Unfortunately I cannot give more information like the table/view/SP definition because of customer confidentiality... greetings, Tim

    Read the article

  • SMO some times doesn't display the instances in sql2008 cluster

    - by Cute
    Hi I have used SMO API.in that i have used SmoApplication.EnumAvailableServers(FALSE) and from that i have filtered local instances i have used this approch insted of true to make this as convinent for remote sqldiscovery also.using that api created a dll and use that dll in c++. Now this is working in all combinations but some times it is failed to retrieve the instannces in win2008 sql2008 cluster combination. if i run the exe for 5 times it got succeed for 3 times and failed for two times... What is wromg with win-sql2008 cluster .is there any additional changes needed to make it work properrly.My firewall is off and also added exception for tcp port 1433. Anyy help is greately Appreciated... Thanks in Advance.

    Read the article

  • Simple Database normalization question...

    - by user365531
    Hi all, I have a quick question regarding a database that I am designing and making sure it is normalized... I have a customer table, with a primary key of customerId. It has a StatusCode column that has a code which reflects the customers account status ie. 1 = Open, 2 = Closed, 3 = Suspended etc... Now I would like to have another field in the customer table that flags whether the account is allowed to be suspended or not... certain customers will be automatically suspended if they break there trading terms... others not... so the relevant table fields will be as so: Customers (CustomerId(PK):StatusCode:IsSuspensionAllowed) Now both fields are dependent on the primary key as you can not determine the status or whether suspensions are allowed on a particular customer unless you know the specific customer, except of course when the IsSuspensionAllowed field is set to YES, the the customer should never have a StatusCode of 3 (Suspended). It seems from the above table design it is possible for this to happen unless a check contraint is added to my table. I can't see how another table could be added to the relational design to enforce this though as it's only in the case where IsSuspensionAllowed is set to YES and StatusCode is set to 3 when the two have a dependence on each other. So after my long winded explanation my question is this: Is this a normalization problem and I'm not seeing a relational design that will enforce this... or is it actually just a business rule that should be enforced with a check contraint and the table is in fact still normalized. Cheers, Steve

    Read the article

  • C# Select clause returns system exception instead of relevant object

    - by Kashif
    I am trying to use the select clause to pick out an object which matches a specified name field from a database query as follows: objectQuery = from obj in objectList where obj.Equals(objectName) select obj; In the results view of my query, I get: base {System.SystemException} = {"Boolean Equals(System.Object)"} Where I should be expecting something like a Car, Make, or Model Would someone please explain what I am doing wrong here? The method in question can be seen here: // this function searches the database's table for a single object that matches the 'Name' property with 'objectName' public static T Read<T>(string objectName) where T : IEquatable<T> { using (ISession session = NHibernateHelper.OpenSession()) { IQueryable<T> objectList = session.Query<T>(); // pull (query) all the objects from the table in the database int count = objectList.Count(); // return the number of objects in the table // alternative: int count = makeList.Count<T>(); IQueryable<T> objectQuery = null; // create a reference for our queryable list of objects T foundObject = default(T); // create an object reference for our found object if (count > 0) { // give me all objects that have a name that matches 'objectName' and store them in 'objectQuery' objectQuery = from obj in objectList where obj.Equals(objectName) select obj; // make sure that 'objectQuery' has only one object in it try { foundObject = (T)objectQuery.Single(); } catch { return default(T); } // output some information to the console (output screen) Console.WriteLine("Read Make: " + foundObject.ToString()); } // pass the reference of the found object on to whoever asked for it return foundObject; } } Note that I am using the interface "IQuatable<T>" in my method descriptor. An example of the classes I am trying to pull from the database is: public class Make: IEquatable<Make> { public virtual int Id { get; set; } public virtual string Name { get; set; } public virtual IList<Model> Models { get; set; } public Make() { // this public no-argument constructor is required for NHibernate } public Make(string makeName) { this.Name = makeName; } public override string ToString() { return Name; } // Implementation of IEquatable<T> interface public virtual bool Equals(Make make) { if (this.Id == make.Id) { return true; } else { return false; } } // Implementation of IEquatable<T> interface public virtual bool Equals(String name) { if (this.Name.Equals(name)) { return true; } else { return false; } } } And the interface is described simply as: public interface IEquatable<T> { bool Equals(T obj); }

    Read the article

  • LINQ - Contains with anonymous type

    - by Marlos
    When using this code (simplified for asking): var rows1 = (from t1 in db.TABLE1 where (t1.COLUMN_A == 1) select new { t1.COLUMN_B, t1.COLUMN_C }); var rows2 = (from t2 in db.TABLE2 where (rows1.Contains(t2.COLUMN_A)) select t2; I got the following error: The type arguments for method 'System.Linq.Enumerable.Contains(System.Collections.Generic.IEnumerable, TSource)' cannot be inferred from the usage. Try specifying the type arguments explicitly. I need to filter the first result by COLUMN_B, but I don't know how. Is there a way to filter it?

    Read the article

  • WSSQL query for multiple computers at once

    - by Josh
    I can run normal searches just fine. Windows 7 won't let me add a network share to my local index, but I can query the remote index just fine. The problem is that I can't find a way to query two indexes at once. I was hoping that something like this would work: SELECT System.ItemName FROM compA.SystemIndex, compB.SystemIndex WHERE SCOPE='file://compA/pathA' OR SCOPE='file://compB/pathB' but it doesn't. For simple queries, I can query compA and compB separately and then merge the results myself, but I'm hoping for a better way. Anybody here have some experience with this?

    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 | 605 606 607 608 609 610 611 612 613 614 615 616  | Next Page >