Here is a simple LINQ query (based on NorthWind) that returns a list of Customers. Each customer contains a list of Orders. from c in Customers join o in Orders on c.CustomerID equals o.CustomerID into CO select new {c, CO} This works fine and the SQL generated is also fine. Now I want to go one step further. I want each Order object ...
When I try to get the sum of a column from a table I get the error 'Arithmetic overflow error converting expression to data type int' because the resulting number is to big for an INT. So I tried to CAST to a BIGINT using SELECT CAST(SUM(columnname) AS BIGINT) FROM tablename but I get the same error. Any ideas what i'm doing wrong? ...
Hello. I wonder how could i print a row number for sql statement where is using order. Currently i tried ROWNUM but as i understand it works only for unsorted result set. SELECT rownum, a.lg_id, a.full_name, a.sort_order FROM activity_type_lang a where a.lg_id = 'en' order by a.full_name; TIA ...
Hello, is there any posibillity to turn off Linq replacing empty strings with null on nullable varchar columns? I could set the column to NOT NULL, but the scheme is used by other application too, so I would like to avoid that. I've tried to set column not nullable in DBML, but then I get "System.Data.Linq.ChangeConflictException: Raw...
Here goes a yet another SQL question about dates… I'm building a calendaring application using PHP and Postgres that would display events spanning days, weeks or possibly months. Each event has a start date and an end date, and selecting them by range is not a problem; however, it would be useful for me if Postgres could split multi-wee...
Hello, I have a table which has the following id timestamp ------------------------------------- 1 1247046037 4 1247047437 5 1247047438 6 1247048738 Now I want to return all those ids which are 3 minutes apart from the previous id. In the above example it should return 1,4,6. How should I go about doing this?...
I have two tables: CREATE TABLE InmarsatZenith.dbo.ClientJob (JobRef int PRIMARY KEY NOT NULL, IntRef uniqueidentifier, CopyDeadline datetime, PublicationDate datetime, Repeat bit, BusinessType nvarchar(25), Sector nvarchar(30), Lang nvarchar(15), Format nvarchar(25), CreativeRotation nvarchar(50), TipinType nvarchar(25)) and CREATE ...
I would like to load/save a dict to/from my sqlite DB, but am having some problems figuring out a simple way to do it. I don't really need to be able to filter, etc., based on the contents so a simple conversion to/from string is fine. The next-best thing would be foreign keys. Please don't post links to huge examples, my head would exp...
Hello: I've this cursor declaration: DECLARE CursorArticulo CURSOR FOR SELECT HstAt.IdArticulo, SUM(HstAt.Cantidad) As SumaCantidad, HstAt.Precio FROM HstArticulosTickets hstAT INNER JOIN HstTickets HstT ON hstAT.IdTicket=hstT.IdTicket WHERE hstT.NumUsuarioEmisor=@UsuarioAct ...
Hi there, I'm building a Useful Links database for a Classic ASP site. The table has the usual ID, Title, URL, Description, DateAdded and DateModified. I also want to record in the table each time a link has been clicked or viewed. I wondered if anyone had built such a system or knows of a way that I could accomplish this? Thank you. ...
I am importing csv to db using bulk insert. It is the comma delimited csv file. No text qualifiers for all fields. But some fields may have comma as part of the data. for eg, ADDRESS field value. Those values are surronded with double quotes. Those double quotes appear only if the field value has comma in it otherwise values are not su...
HI all, I have an issue where i have to search rows in a datagrid view based on the key value given explicitly through textbox. This key value should be of any column in the datagridview. But unforutunately it works only for one column (in the below code column used is b) string ss; ss = textBox1.Text; ...
Using the "Northwind" sample: I would like to create an insert trigger on the OrderDetails table so that every time an OrderDetail is inserted, it gets the current value of the UnitCost as defined in the Products table. It should be something like this, but I have trouble to get it right. Can you help ? CREATE TRIGGER Trigger1 ON Ord...
I have a table which looks like index customer_number ABC CWD ROE BEE 1 1 0 0 0 1 and I want to return only the field names that have value 1 in this case 'BEE' I found that by SHOW FIELDS I can get the names of the fields but how I can say show the field names where field value = 1? ...
Is it acceptable to dynamically generate the total of the contents of a field using up to 10k records instead of storing the total in a table? I have some reasons to prefer on-demand generation of a total, but how bad is the performance price on an average home PC? (There would be some joins -ORM managed- involved in figuring the total....
Greetings, fellow coders! I have this table that contains categories and subcategories (actually I don't, but let's stick to a classic example): Id ParentId Name 1 NULL A 2 1 B 3 2 C 4 3 D 5 NULL B 6 5 D Is there a way for me to get cat...
Newbie question : I would like to store lengthy .sql scripts in my solution and execute them programmatically. I've already figured out how to execute a string containing my sql script but I haven't figured out how to read the string from a file that would be stored in the solution (under a /Scripts subfolder for example). Many thanks, ...
When taking a database from a relatively un-normalized form and normalizing it, what, if any, changes in resource utilization might one expect? For example, normalization often means more tables get created from fewer which means the database now has a higher number of tables, but many of them are quite small, allowing the often used on...
I currently have a query similar to: SELECT users.fname, users.lname, sales.date FROM users LEFT JOIN sales ON users.id=sales.user And this gives me the result of something like: Fname | Lname | Date Jeff | K | 2/12/08 Jeff | K | 5/18/08 Jeff | K | 2/22/09 Bill | D | 3/12/08 Bill | D | 12/9/08 This is wha...
Good afternoon all. I'm going to post the stored procedure in it's entire glory. Feel free to rip it to shreds. The author won't mind. DECLARE @itemTypeID INT SELECT @itemTypeID=ItemTypeID FROM dbo.ItemTypes WHERE ItemTypeName = 'Advert' BEGIN SELECT a.Active, a.ParentClass, a.Classification, a.Variant, FV."Full Views", ...