Search Results

Search found 111084 results on 4444 pages for 'linq to sql code generato'.

Page 164/4444 | < Previous Page | 160 161 162 163 164 165 166 167 168 169 170 171  | Next Page >

• Storing a set of criteria in another table

  - by bendataclear

  I have a large table with sales data, useful data below: RowID Date Customer Salesperson Product_Type Manufacturer Quantity Value 1 01-06-2004 James Ian Taps Tap Ltd 200 £850 2 02-06-2004 Apple Fran Hats Hats Inc 30 £350 3 04-06-2004 James Lawrence Pencils ABC Ltd 2000 £980 ... Many rows later... ... 185352 03-09-2012 Apple Ian Washers Tap Ltd 600 £80 I need to calculate a large set of targets from table containing values different types, target table is under my control and so far is like: TargetID Year Month Salesperson Target_Type Quantity 1 2012 7 Ian 1 6000 2 2012 8 James 2 2000 3 2012 9 Ian 2 6500 At present I am working out target types using a view of the first table which has a lot of extra columns: SELECT YEAR(Date) , MONTH(Date) , Salesperson , Quantity , CASE WHEN Manufacturer IN ('Tap Ltd','Hats Inc') AND Product_Type = 'Hats' THEN True ELSE False END AS IsType1 , CASE WHEN Manufacturer = 'Hats Inc' AND Product_Type IN ('Hats','Coats') THEN True ELSE False END AS IsType2 ... ... , CASE WHEN Manufacturer IN ('Tap Ltd','Hats Inc') AND Product_Type = 'Hats' THEN True ELSE False END AS IsType24 , CASE WHEN Manufacturer IN ('Tap Ltd','Hats Inc') AND Product_Type = 'Hats' THEN True ELSE False END AS IsType25 FROM SalesTable WHERE [some stuff here] This is horrible to read/debug and I hate it!! I've tried a few different ways of simplifying this but have been unable to get it to work. The closest I have come is to have a third table holding the definition of the types with the values for each field and the type number, this can be joined to the tables to give me the full values but I can't work out a way to cope with multiple values for each field. Finally the question: Is there a standard way this can be done or an easier/neater method other than one column for each type of target? I know this is a complex problem so if anything is unclear please let me know. Edit - What I need to get: At the very end of the process I need to have targets displayed with actual sales: Type Year Month Salesperson TargetQty ActualQty 2 2012 8 James 2000 2809 2 2012 9 Ian 6500 6251 Each row of the sales table could potentially satisfy 8 of the types. Some more points: I have 5 different columns that need to be defined against the targets (or set to NULL to include any value) I have between 30 and 40 different types that need to be defined, several of the columns could contain as many as 10 different values For point 2, if I am using a row for each permutation of values, 2 columns with 10 values each would give me 100 rows for each sales person for each month which is a lot but if this is the only way to define multiple values I will have to do this. Sorry if this makes no sense!

  Read the article

• Question about SQL Server HierarchyID depth-first performance

  - by AndalusianCat

  I am trying to implement hierarchyID in a table (dbo.[Message]) containing roughly 50,000 rows (will grow substantially in the future). However it takes 30-40 seconds to retrieve about 25 results. The root node is a filler in order to provide uniqueness, therefor every subsequent row is a child of that dummy row. I need to be able to traverse the table depth-first and have made the hierarchyID column (dbo.[Message].MessageID) the clustering primary key, have also added a computed smallint (dbo.[Message].Hierarchy) which stores the level of the node. Usage: A .Net application passes through a hierarchyID value into the database and I want to be able to retrieve all (if any) children AND parents of that node (besides the root, as it is filler). A simplified version of the query I am using: @MessageID hierarchyID /* passed in from application */ SELECT m.MessageID, m.MessageComment FROM dbo.[Message] as m WHERE m.Messageid.IsDescendantOf(@MessageID.GetAncestor((@MessageID.GetLevel()-1))) = 1 ORDER BY m.MessageID From what I understand, the index should be detected automatically without a hint. From searching forums I have seen people utilizing index hints, at least in the case of breadth-first indexes, as apparently CLR calls may be opaque to the query optimizer. I have spent the past few days trying to find a solution for this issue, but to no avail. I would greatly appreciate any assistance, and as this is my first post, I apologize in advance if this would be considered a 'noobish' question, I have read the MS documentation and searched countless forums, but have not came across a succinct description of the specific issue.

  Read the article

• SQL Server JDBC Exception

  - by user267581

  When using ANT to build my Java application I keep getting this error. I have tried multiple times to use SQLJDBC.JAR and SQLJDBC4.JAR but continually receive this error message. I am completely stumpped why this error is received even after upgrading to sqljdbc4.jar. [javadoc] java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

  Read the article

• Counting number of children in hierarchical SQL data

  - by moontear

  Hello, for a simple data structure such as so: ID parentID Text Price 1 Root 2 1 Flowers 3 1 Electro 4 2 Rose 10 5 2 Violet 5 6 4 Red Rose 12 7 3 Television 100 8 3 Radio 70 9 8 Webradio 90 For reference, the hierarchy tree looks like this: ID Text Price 1 Root |2 Flowers |-4 Rose 10 | |-6 Red Rose 12 |-5 Violet 5 |3 Electro |-7 Television 100 |-8 Radio 70 |-9 Webradio 90 I'd like to count the number of children per level. So I would get a new column "NoOfChildren" like so: ID parentID Text Price NoOfChildren 1 Root 8 2 1 Flowers 3 3 1 Electro 3 4 2 Rose 10 1 5 2 Violet 5 0 6 4 Red Rose 12 0 7 3 Television 100 0 8 3 Radio 70 1 9 8 Webradio 90 0 I read a few things about hierarchical data, but I somehow get stuck on the multiple inner joins on the parentIDs. Maybe someone could help me out here. moon

  Read the article

• optimizing an sql query using inner join and order by

  - by Sergio B

  I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort? EXPLAIN SELECT SQL_NO_CACHE `groups`.* FROM `groups` INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id WHERE ((`memberships`.user_id = 1) AND (`memberships`.`status_code` = 1 AND `memberships`.`manager` = 0)) ORDER BY groups.created_at DESC LIMIT 5;` +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | memberships | ref | grp_usr,grp,usr,grp_mngr | usr | 5 | const | 5 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | groups | eq_ref | PRIMARY | PRIMARY | 4 | sportspool_development.memberships.group_id | 1 | | +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | groups | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | groups | 1 | index_groups_on_name | 1 | name | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_privacy_setting | 1 | privacy_setting | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_created_at | 1 | created_at | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_id_and_created_at | 1 | id | A | 6 | NULL | NULL | | BTREE | | | groups | 1 | index_groups_on_id_and_created_at | 2 | created_at | A | 6 | NULL | NULL | YES | BTREE | | +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | memberships | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | memberships | 0 | grp_usr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 0 | grp_usr | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | usr | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp_mngr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp_mngr | 2 | manager | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 3 | status_code | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 4 | manager | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 2 | status_code | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 3 | manager | A | 2 | NULL | NULL | YES | BTREE | | +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

  Read the article

• Properties.Settings Ambiguity after adding LINQ to SQL

  - by Emmanuel Smith

  I have recently linked a database to my C# service by creating a LINQ to SQL item in my solution. Everything was fine and dandy as I was continuing to code, but then I suddenly noticed that there where 16 Ambiguity errors. e.g. Ambiguity between 'EmailService.Properties.Settings.defaultInstance' and 'EmailService.Properties.Settings.defaultInstance' What happened was that the database file decided to create it's own Setting.Designer.cs; So now I have a Settings.Designer.cs and a Settings1.Designer.cs (which was created by the LINQ to SQL file) in my Properties folder. Is there anyway that I can change where the database file is referencing its settings so I can delete the duplicate?

  Read the article

• Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAV

  - by brz dot net

  I have to find the indentid from the status table based on below two conditions: 1. If there are more than one record having same indentid in status table and the same indentID has count1 in feasibilitystatus table then I don't want to display the record. 2. If there is only one record of indentid in status table and the same indentID has count0 in feasibilitystatus table then I don't want to display the record. Query: select distinct s.indentid from status s where s.status='true' and s.indentid not in( select case when count(s.indentid)>1 then (select indentid from feasibilitystatus group by indentid having count(indentid)>1) else (select indentid from feasibilitystatus group by indentid having count(indentid)>0) end as indentid from status) Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

  Read the article

• Simple Linq Dynamic Query question

  - by Dr. Zim

  In Linq Dynamic Query, Scott Guthrie shows an example Linq query: var query = db.Customers. Where("City == @0 and Orders.Count >= @1", "London", 10). OrderBy("CompanyName"). Select("new( CompanyName as Name, Phone)"); Notice the projection new( CompanyName as Name, Phone). If I have a class like this: public class CompanyContact { public string Name {get;set;} public string Phone {get;set;} } How could I essentially "cast" his result using the CompanyContact data type without doing a foreach on each record and dumping it in to a different data structure? To my knowledge the only .Select available is the Dymanic Query version which only takes a string and parameter list.

  Read the article

• To call SelectMany dynamically in the way of System.Linq.Dynamic

  - by user341127

  In System.Linq.Dynamic, there are a few methods to form Select, Where and other Linq statements dynamically. But there is no for SelectMany. The method for Select is as the following: public static IQueryable Select(this IQueryable source, string selector, params object[] values) { if (source == null) throw new ArgumentNullException("source"); if (selector == null) throw new ArgumentNullException("selector"); LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, null, selector, values); IQueryable result = source.Provider.CreateQuery( Expression.Call( typeof(Queryable), "Select", new Type[] { source.ElementType, lambda.Body.Type }, source.Expression, Expression.Quote(lambda))); return result; } I tried to modify the above code, after hours working, I couldn't find a way out. Any suggestions are welcome. Ying

  Read the article

• Just installed Sql Server 2008 and cannot access it

  - by Mendy

  I just install Sql Server 2008 on a new server and I cannot connect to it. I'm trying to connect using Windows Authentication to the localhost but I get the following error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

  Read the article

• Using code snippets in Visual Studio

  - by devoured elysium

  Right now when I want to use the cw snippet, I'm typing cw, tab, tab. Is this the correct(fastest!) way of doing it? If there'd be some way of only having to hit once the tab key or any other key i'd be glad to know it.

  Read the article

• How can I fill SQL Server table from excel only using sql query?

  - by Phsika

  How can I do that with Microsoft.ACE.OLEDB.12.0? CREATE TABLE [dbo].[Addresses_Temp] ( [FirstName] VARCHAR(20), [LastName] VARCHAR(20), [Address] VARCHAR(50), [City] VARCHAR(30), [State] VARCHAR(2), [ZIP] VARCHAR(10) ) GO INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] ) SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Source\Addresses.xlsx;IMEX=1', 'SELECT * FROM [Sayfa1$]') How can I do that?

  Read the article

• How do I copy a CLOB from Oracle into SQL Server

  - by NYARROW

  The CLOB is XML data that is 8k (sometimes 32k). Any suggestions?

  Read the article

• Recursive Query Help

  - by Josh

  I have two tables in my database schema that represent an entity having a many-to-many relationship with itself. Role --------------------- +RoleID +Name RoleHasChildRole --------------------- +ParentRoleID +ChildRoleID Essentially, I need to to be able to write a query such that: Given a set of roles, return the unique set of all related roles recursively. This is an MSSQL 2008 Database.

  Read the article

• How to find level of employee position using RECURSIVE COMMON TABLE EXPRESSION

  - by user309381

  ;with Ranked(Empid,Mngrid,Empnm,RN,level) As ( select Empid,Mngrid ,Empnm ,row_number() over (order by Empid)AS RN , 0 as level from dbo.EmpMngr ), AnchorRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN ,level from Ranked ), RecurRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN,level from AnchorRanked Union All select Ranked.Empid,Ranked.Mngrid,Ranked.Empnm,Ranked.RN,Ranked.level + 1 from Ranked inner join RecurRanked on Ranked.Empid = RecurRanked.Empid AND Ranked.RN = RecurRanked.RN+1) select Empid,Empnm,level from RecurRanked

  Read the article

• Can we write subquery in between SELECT and FROM

  - by Mohsin

  i want to know, how to write subquery in between SELECT and FROM as SELECT Col_Name,(Subquery) From Table_Name Where Some_condition

  Read the article

• Making rows in to colums using sql query

  - by vaibhav

  I have a temp table in which have one coloum with four rows. Table ------ vaibhav IBM 12 'T' I need a temp table Col1 Col2 Col3 Col4 ------ ----- ----- ------ Vaibhav IBM 12 'T' Do anyone have any idea.

  Read the article

• How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

  - by Mark Struzinski

  I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question.

  Read the article

• DBCC SHRINKFILE 1 sproc for multiple databases

  - by user67545

  I have a need to execute DBCC SHRINKFILE for multiple db's within the same sproc. I could create multiple sprocs so it runs within the given context, but I was curious if there were alternatives?

  Read the article

• sql server - sum Comma seperated value from a column

  - by Aseem Gautam

  There is a column in database which contains comma seperated values like: 0.00,12.45,14.33 and so on. I need to sum this inside a stored procedure. One way which I can think of is to split and convert it into a table using a function and then sum it. Any other ideas?

  Read the article

• Datatype to save excel file in sql server?

  - by gowri-ganapathy

  Hi, I have a table in which there are two columns : 1. import type, 2. Excel import template. The second column - "Excel import template" should store the whole excel file. How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ? Thanks in advance !

  Read the article

• Counting number of children in hierarchical SQL data

  - by moontear

  for a simple data structure such as so: ID parentID Text Price 1 Root 2 1 Flowers 3 1 Electro 4 2 Rose 10 5 2 Violet 5 6 4 Red Rose 12 7 3 Television 100 8 3 Radio 70 9 8 Webradio 90 For reference, the hierarchy tree looks like this: ID Text Price 1 Root |2 Flowers |-4 Rose 10 | |-6 Red Rose 12 |-5 Violet 5 |3 Electro |-7 Television 100 |-8 Radio 70 |-9 Webradio 90 I'd like to count the number of children per level. So I would get a new column "NoOfChildren" like so: ID parentID Text Price NoOfChildren 1 Root 8 2 1 Flowers 3 3 1 Electro 3 4 2 Rose 10 1 5 2 Violet 5 0 6 4 Red Rose 12 0 7 3 Television 100 0 8 3 Radio 70 1 9 8 Webradio 90 0 I read a few things about hierarchical data, but I somehow get stuck on the multiple inner joins on the parentIDs. Maybe someone could help me out here. moon

  Read the article

• sql inValid Object Name how to solve it?

  - by Phsika

  error returns to me in below codes:Msg 208, Level 16, State 1, Line 1 Invalid object name 'ENG_PREP'. insert into ENG_PREP VALUES('572012-01-1,572012-01-2,572012-01-3,572013-01-1,572013-01-2', '', '500', '', 'A320 P.001-A', 'Removal of the LH Wing Safety Rope', '', '', '', '0', '', 'AF', '12-00-00-081-001', '', '', '', '', '', '', '' )

  Read the article

• SQL help on a name dilemma

  - by Ardman

  I have a column which has surname and firstname plus salutation in. e.g. Bloggs,Joe,Mr I need to break this out into Bloggs Joe Mr as 3 seperate columns. Any ideas appreciated. How, the other thing is I won't know how many commas are in the initial column.

  Read the article

• C#/SQL: How to create dynamic tables/fields in C# using SQL Statements

  - by eibhrum

  Hi, I just wanna ask if there's a way in C#/ASP.NET to create 'dynamic' tables/fields similar to phpMyAdmin: Any suggestions/comments?

  Read the article

< Previous Page | 160 161 162 163 164 165 166 167 168 169 170 171  | Next Page >