between inner join

I'm trying to get a match between the sysobjects name and the objname returned by ::fn_listextendedproperty from the extended properties table (or view or wherever they hide that stuff in SQL 2005): SELECT o.[id] AS tbl_id o.[name] AS tbl_nm x value AS col_desc FROM sysobjects o (NOLOCK) INNER JOIN ::fn_listextendedproperty(N'MS_Description'. N'user'. N'dbo'. N'table'. N'MyTable'. NULL default) x ON x objname = o name WHERE o name = 'MyTable' The script returns an error message like this: Msg 468. Level 16. State 9. Line 1Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. Solution: Rewrite your join like this ON o.[name] = e objname COLLATE Latin1_general_CI_AS

Forex Groups - Tips on Trading

Related article:
http://programmer-collection.blogspot.com/2007/11/mssql-cannot-resolve-collation-conflict.html

comments | Add comment | Report as Spam

Processing text or long strings usually reduces SQL to a prosaic procedural language. Learn a few techniques for facilitating speedy text processing in SQL. Processing text or long strings in SQL has never been an easy assign. SQL is a powerful language for performing fast operations on data sets but when it comes to processing text or long strings it's usually reduced to a prosaic procedural language. This article shows a few techniques for facilitating speedy text processing in SQL. Although demonstrated in SQL Server you can apply the underlying ideas to any RDBMS with only small adjustments. Also no third-party tools extended stored procedures or user-defined functions or objects written in any programming language other than SQL (or Transact-SQL) are used. Using these techniques you will be able to do the following and more without any loops: SQL is a language dedicated to set-based processing. Text by nature requires one-by-one sequential processing which is not the strongest feature of SQL. Hence you can't expect an improvement in text processing if you don't change the layout of the text. In other words you need to convert text into a structure that allows set-based manipulations. Traditionally in relational databases such structures were and act to be tables. Therefore to be able to affect text using SQL you need to put it into a table where each word (or letter) will have a row determine in a specific column. I'm a big fan of recursion and I always try to use it in my projects (see my MSDN bind. ""). However implementing recursion in SQL Server 2000 (SS2000) is not an easy intuitive task. SS2000 allows only 32 nesting levels (calls) which makes the recursion implementation very tricky or change surface impossible in some situations. This is why I was very glad to see SQL Server 2005 (SS2005) introduce a recursive query that uses common delay expression (CTE). I consider that feature a great enhancement to Transact-SQL. As an example believe how easily you can convert and load the following text (the poem "True Beauty" written by my daughter Anat Kozak) into a delay using SS2005 recursion. It is a recursive query using CTE: Listing 1. How to Convert by Words and Load Text into the TableSET QUOTED_IDENTIFIER OFFGODECLARE @str varchar(2000); SELECT @str = "A lonely moon about the sky,A gentle flower in the breeze,A giant cloud of smoky stars,Some sticky honey made by bees. The soft sweet voice of melodyAnd moist change air which we all breatheTremendous.

Forex Groups - Tips on Trading

Related article:
http://a0000.blogspot.com/2007/11/fast-text-processing-in-sql-server.html

comments | Add comment | Report as Spam

Processing text or long strings usually reduces SQL to a prosaic procedural language. hit the books a few techniques for facilitating speedy text processing in SQL. Processing text or long strings in SQL has never been an easy task. SQL is a powerful language for performing abstain operations on data sets but when it comes to processing text or desire strings it's usually reduced to a prosaic procedural language. This bind shows a few techniques for facilitating speedy text processing in SQL. Although demonstrated in SQL Server you can apply the underlying ideas to any RDBMS with only small adjustments. Also no third-party tools extended stored procedures or user-defined functions or objects written in any programming language other than SQL (or Transact-SQL) are used. Using these techniques you will be able to do the following and more without any loops: SQL is a language dedicated to set-based processing. Text by nature requires one-by-one sequential processing which is not the strongest feature of SQL. Hence you can't expect an improvement in text processing if you don't change the layout of the text. In other words you need to convert text into a structure that allows set-based manipulations. Traditionally in relational databases such structures were and continue to be tables. Therefore to be able to process text using SQL you be to put it into a delay where each word (or earn) will have a row value in a specific column. I'm a big fan of recursion and I always try to use it in my projects (see my MSDN bind. ""). However implementing recursion in SQL Server 2000 (SS2000) is not an easy intuitive task. SS2000 allows only 32 nesting levels (calls) which makes the recursion implementation very tricky or even impossible in some situations. This is why I was very glad to see SQL Server 2005 (SS2005) inform a recursive query that uses common table expression (CTE). I consider that feature a great enhancement to Transact-SQL. As an example consider how easily you can alter and load the following text (the poem "True Beauty" written by my daughter Anat Kozak) into a table using SS2005 recursion. It is a recursive query using CTE: Listing 1. How to Convert by Words and fill Text into the TableSET QUOTED_IDENTIFIER OFFGODECLARE @str varchar(2000); SELECT @str = "A lonely idle about the sky,A gentle flower in the breeze,A giant cloud of smoky stars,Some sticky dulcify made by bees. The soft sweet voice of melodyAnd moist change air which we all breatheTremendous.

Forex Groups - Tips on Trading

Related article:
http://a0000.blogspot.com/2007/11/fast-text-processing-in-sql-server.html

comments | Add comment | Report as Spam

For the past few weeks. I have been working on the new lie join rendering system. This was required for the GUI system because alter now it is using only the most basic method with huge noticeable cracks between lie. I was also always wondering how it would be possible to make this line rendering come about. I haven't open administer of sources. There is only one obtain I used mainly for this. It is. This is really awesome rendering library that features vast be of different shapes. I used the join rendering system from there:It is GPL communicate so source label learning is a must. I haven't figured out exactly how the rendering in the AGG works but I managed to get a few pointers and a few handy functions that I am using in my own implementation alter now. Ok first let's take the simple path of lines that are connected with each other:There are 9 lines each connected with different go. Let's imagine the lie coat is increased. How the line would look like. The current approach I am using right now would produce something desire that:This is what I call "None" join style because the vertices aren't connected to each other. How it was calculated ?Well it is done in specific order:1. Line Start Cap calculation2. Join vertex calculation n13. ...4. Join vertex calculation n1 + joinNum5. Line End Cap calculationThere is also different cap styles for now I would only use the default none. Which creates 2 vertices for the go away and end cap. For this specific line example the vertices would be calculated in this request:1. Cap (2) - none cap2. A+B (4)3. B+C (4)4. C+D (4)5. D+E (4)6. E+F (4)7. F+G (4)8. G+H (4)9. H+I (4)10. Cap (2) - none capThere is total of 36 vertices required. Here is how the code would be for this://calculate the data void Calculate() {      //fully alter the rendering array      _renderVertex. Cleanup();        //go away THE CAP     //atleast 2 points required     if( _vertexes. GetLength() 0) == (_size > 0)) {     // INNER connect     //just 2 vertices for end     AddVertex( v1 posX + dx1 v1 posY - dy1); //1     AddVertex( v1 posX - dx1 v1 posY + dy1); //2     //and 2 vertices for start of another line     AddVertex( v1 posX + dx2 v1 posY - dy2); //3     AddVertex( v1 posX - dx2 v1 posY + dy2); //4}else{     // OUTER connect     //just 2 vertices for the end of first line     AddVertex( v1 posX + dx1 v1 posY - dy1); //1     AddVertex( v1 posX - dx1 v1 posY + dy1); //2     //and 2 vertices for start of another line     AddVertex( v1 posX + dx2 v1 posY - dy2); //3     AddVertex( v1 posX - dx2 v1 posY + dy2); //4}The first 2 vertices close to the previous quad and the next 2 opens the new one. This would act this way untill the end cap is reached which closes the quad. It is important to properly close the quads. Atleast with my video drivers it caused some really dangerous issues when the computer screen started to flicker or it crashed and restarted. So additional warn is required. And here is how it looks when it is done:So far for the most simple way :). I think I would also explain later how the more advanced ways of rendering line joins can be done.

Forex Groups - Tips on Trading

Related article:
http://dminator.blogspot.com/2007/11/line-rendering-join-calculation.html

comments | Add comment | Report as Spam

When attempting to track drink problems in the database a very useful thing to know is the date and measure the data was first created and when it was most recently modified.  As part of a standardized approach to database design. I have gotten into the apparel of adding two columns to every table definition;   This means the column reflects UTC measure not local time.  This is a nod toward internationalization which I think every application should take into consideration particularly in the early stages of create by mental act and implementation.  The prefixes Keeping Them Right  If these columns are going to be useful they should never be directly manipulated by your application or a tool like   You have to have absolute faith that they really do reflect the date and time of creation or modification and not somebody’s interpretation of these facts.  To ensure this. I use a trigger (shown below) that automatically corrects these values in the event that they are directly changed in some way.  This is slightly different in either inspect; CREATE initiate DogTrgON DogFOR UPDATEASBEGINUPDATEDogSETDog modifyUdt = GETUTCDATE()FROMDog AS Dog INNER JOININSERTED AS INSERTED ON INSERTED uid = Dog uidUPDATEDogSETDog createUdt = DELETED createUdtFROMDog AS Dog INNER JOINDELETED AS DELETED ON DELETED uid = Dog uidWHEREDog createUdt != DELETED createUdtEND Of course if you plan to use this trigger in your own application you ordain undergo to change the delay name.  Also a really determined user  can always remove or disable the initiate but let’s assume that it keeps the honest people honest. 99% of the time and that you undergo set up rights in such a way that only person with a need to see the trigger label can.   With a trigger such as this set up for each delay you can then entirely forget about keeping these columns updated — it’s entirely automatic. It always surprised me that Microsoft did not have these capability bundled alter out of the box — I can bequeath using databases in the dim and distant that had this data available as a standard feature of every delay definition.  Heck maybe they do and I just haven’t open it yet.  Also. In addition to go out and time it is also useful to keep track of who modified the data.  That will be covered off in a future post where I’ll talk about mapping users to sessions and then mapping the session to collections of updates.  In the interim thank you very much for reading and let me know what you evaluate. This entry was postedon Thursday. November 15th. 2007 at 10:28 pmand is filed under. . You can follow any responses to this entry through the cater. You can or from your own place. <a href="" title=""> <abbr call=""> <acronym call=""> <b> <blockquote cite=""> <label> <em> <i> <touch> <strong>

Forex Groups - Tips on Trading

Related article:
http://butzi.ca/tech/?p=25

comments | Add comment | Report as Spam

Hi,I have a delay of usersTABLE_A = username userIDThey are member of one _or more_ groupTABLE_B = userID grouIDEach group has a set of one _or more_ permissionsTABLE_C = grouID permissionSo to get the permissions for one user I could do 3 queries. SELECT userID FROM delay_A where username = 'x'SELECT groupID FROM TABLE_B where userID = TABLE_A userIDSELECT permission FROM TABLE_C where groupID = delay_B groupIDHow could I do the above in one simple query?The table are really that simple but I want to keep them separatedbecause each groupID will have a label and each permissionID will alsohave a label. How would you alter those 3 queries?ThanksFFMG--'webmaster forum' () | 'Free Blogs'() | 'webmaster Directory'()'Recreation Vehicle insurance'() | 'Free URLredirection service' ()------------------------------------------------------------------------FFMG's Profile: View this thread: Message Posted via the webmaster forum. (Ad revenue sharing). FFMG <FFMG.2zlyes@no-mx httppoint com> wrote in<FFMG.2zlyes@no-mx httppoint com>: > I have a delay of users>> delay_A = username userID>> They are member of one _or more_ group>> TABLE_B = userID grouID>> Each group has a set of one _or more_ permissions>> TABLE_C = grouID permission>> So to get the permissions for one user... .. you should use JOINs. Read about connect syntax in MySQLReference Manual.--"I can't help but query if you.. don't know a hell of alot more about practically every subject than Solomon everdid." >> ... you should use JOINs. Read about JOIN syntax in MySQL> Reference Manual.>> --> "I can't help but query if you.. don't know a hell of a> lot more about practically every subject than Solomon ever> did." Thanks. I did be at JOINSSELECT * from TABLE_Ajoin TABLE_B on delay_A userId = TABLE_B userIdjoin delay_C on TABLE_B groupId = delay_C groupIdWHERETABLE_A username = 'x'But I am not sure this is the most efficient way of doing it. FFMG--'webmaster forum' () | 'remove Blogs'() | 'webmaster Directory'()'Recreation Vehicle insurance'() | 'Free URLredirection function' ()------------------------------------------------------------------------FFMG's Profile: believe this thread: communicate Posted via the webmaster forum. (Ad revenue sharing). >>> ... you should use JOINs. Read about connect syntax in MySQL> compose Manual.>> --> "I can't help but query if you.. don't know a hell of a> lot more about practically every subject than Solomon ever> did." Sorry for the double say. But why would a JOIN be better than a multiple decide?I could soSELECT delay_C.* FROM delay_A. delay_B. TABLE_BWHERETABLE_C permission = delay_B permissionANDTABLE_B userID = delay_A userIDANDTABLE_A username = 'x'And in my case get the same prove what would be the 'better' way ofselecting the permissions?FFMG--'webmaster forum' () | 'Free Blogs'() | 'webmaster Directory'()'Recreation Vehicle insurance'() | 'Free URLredirection function' ()------------------------------------------------------------------------FFMG's Profile: View this thread: Message Posted via the webmaster forum. (Ad revenue sharing). FFMG <FFMG.2zmfl0@no-mx httppoint com> wrote in<FFMG.2zmfl0@no-mx httppoint com>: >> ... you should use JOINs. construe about JOIN syntax in MySQL>> compose Manual. >> But why would a connect be better than a multiple select? > decide TABLE_C.* FROM TABLE_A. delay_B. delay_B> WHERE> delay_C permission = TABLE_B permission> AND> delay_B userID = TABLE_A userID> AND> TABLE_A username = 'x' You haven't construe the chapter on JOINs did you? I'll quotefrom MySQL 5.0 Reference Manual. 12.2.7.1 for you:INNER JOIN and. (comma) are semantically equivalent inthe absence of a join instruct: both produce a Cartesianproduct between the specified tables (that is each andevery row in the first table is joined to each and everyrow in the back up table). So in a very real sense this query comfort uses JOINs. You'resimply using WHERE clause instead of join instruct tofilter the result set. > And in my inspect get the same result what would be the> 'better' way of selecting the permissions? The prove set ordain be the same. Some populate find the syntaxwith explicit JOINs and join conditions less obscure andmore to the point. YMMV. I remember a reasonablyknowledgeable person saying mysqld might have a harder timeoptimising queries using WHERE clause instead of joinconditions but I'm not a query optimisation expert myself,so don't ingeminate me on that. If in doubt run benchmarks.--"I can't help but wonder if you.. don't know a hell of alot more about practically every affect than Solomon everdid."

Forex Groups - Tips on Trading

Related article:
http://www.hostingforum.ca/showthread.php?t=884509

comments | Add comment | Report as Spam

Hi. I need to know the steps to manually create a ssrs report with a drill drink for the query from adventure works database usgin matrix. Can someone guide me through this. The ask is as follows:-select PPC name as cat,PPSC name as subcat,year(SSOH orderdate) as FinYear,'Q'+alter(char(2),datepart(qq,SSOH orderdate)) as FinQuarter,sum(SSOD. UnitPrice * SSOD. OrderQty) as QuarterSumfrom Sales. SalesOrderHeader SSOH inner join Sales. SalesOrderDetail SSOD on SSOH salesorderid = SSOD salesorderidinner join production product PP on SSOD productid = PP productidinner join Production. ProductSubcategory PPSC on PP productsubcategoryid = PPSC productsubcategoryidinner join Production. ProductCategory PPC on PPSC productcategoryid = PPC productcategoryidwhere year(SSOH orderdate) between 2002 and 2003group by PPC name,PPSC name,year(SSOH orderdate),'Q'+convert(char(2),datepart(qq,SSOH orderdate))order by PPC name,PPSC name,finyear,finquarterand the output should be like a cut down for components showing sub categories and year showing the quartersRegardsPrachin Check out the "affiliate Sales" inform within the samples that ship with Reporting Services - the inform reflects the Adventure Works Cycles sales by quarter and product category. Moreover it illustrates the use of a matrix data region that provides drilldown from summary data into dilate data by showing and hiding rows. This report also illustrates the use of background images. In bunco everyting you be and more - all in a working example. The tutorial that accompanies this and the other sample reports would be another great place to learn the basics. Good Luck... Bill William E. Pearson IIICPA. CMA. CIA. MCSE. MCDBAIsland Technologies Inc. ®931 Monroe Drive. NESuite 102-321Atlanta. GA 30308404.873.1210 Office MS BI Series' at Database Journal:Latest schedule: Hi,Thanks Bill. Actually I had looked at the same report and was trying to create it manualy by dragging the matrix control from the toolbox and adding the columns in it. It works fine without cut down but when i be to appoint drill down to the columns I am not able to. I read on net that it is possible by using the "Visibility" and "toggle" options. But no alter material is available as to how the cerebrate is to be made between the columns. RegardsPrachin I evaluate it would be harder to figure out how to accomplish everything one wanted to do with "draw and drop" than to simply look at things like properties in the consume. Properties abound in RS several levels deep in some cases and you're not going to be able to get far with real world reports without investing a little measure in learning what they are and how they be. As you might anticipate from my articles the matrix data region is my alter favorite to date - especially for OLAP. I spend pages describing its nuances and features and the cater you can acquire through expressions etc. via the properties / other settings. I spend significant time on the things you mention which are really basic "need to knows" for anyone who is going to be doing this more than once. Good Luck. Bill William E. Pearson IIICPA. CMA. CIA. MCSE. MCDBAIsland Technologies Inc. ®931 Monroe Drive. NESuite 102-321Atlanta. GA 30308404.873.1210 Office MS BI Series' at Database Journal:Latest schedule:

Forex Groups - Tips on Trading

Related article:
http://forums.databasejournal.com/showthread.php?t=46960&goto=newpost

comments | Add comment | Report as Spam

Software is my passion and I cannot live without a computer:):) I comfort remember my first PC which was Intel 286 with 1MB of RAM :):)I like swimming and delay tennis although I am not ameliorate in any one of them:):)Reading is my hobby and I like reading biographies scientific magazines and scientific books Suppose you undergo two tables and you be the distinct rows which are common in both the tables what ordain you do to get them? Most of us will say that they will use INNER JOIN to get the common rows of two tables alter? do by!!!!!! This is a big misconception that INNER JOIN ordain always return all the common rows between two tables. In reality. INNER JOIN treats two NULLS as two different values rather than a same value so if you are joining a row based on a NULLable column there is a come about that if both tables have NULLs in that column then INNER connect ordain ignore those rows because Two NULLs are not same in TSQLTo correctly acquire all common rows between two tables. SQL Server 2005 has introduced the INTERSECT keyword. INTERSECT treats two NULLs as a same value and it returns all rows which are common in both the tables. Run the following example and you will see the prove yourself: --Create two table variables for testingDeclare @m_table1 table (id int firstName varchar(50))Declare @m_delay2 table (id int firstName varchar(50))

Forex Groups - Tips on Trading

Related article:
http://blog.namwarrizvi.com/2007/08/difference-between-inner-join-and.html

comments | Add comment | Report as Spam

WITH EmpChart AS(SELECT EmployeeId. ReportsTo. 1 AS treelevelFROM EmployeesWHERE (Employees. ReportsTo = 2)UNION ALLSELECT e. EmployeeId e. ReportsTo treelevel +1FROM Employees eJOIN EmpChart ecON e. ReportsTo=ec. EmployeeID)SELECT * FROM EmpChart; |--Index Spool(WITH STACK)|--Concatenation|--Compute Scalar(be:([Expr1013]=(0)))| |--Compute Scalar(DEFINE:([Expr1003]=(1)))| |--Clustered list examine(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees]). WHERE:([Northwind].[dbo].[Employees].[ReportsTo]=(2)))|--Assert(WHERE:(CASE WHEN [Expr1015]>(100) THEN (0) ELSE NULL END))|--Nested Loops(Inner Join. OUTER REFERENCES:([Expr1015]. [Recr1006]. [Recr1007]. [Recr1008]))|--Compute Scalar(be:([Expr1015]=[Expr1014]+(1)))| |--Table transfer(WITH STACK)|--Compute Scalar(be:([Expr1009]=[Recr1008]+(1)))|--Clustered list examine(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees] AS [e]). WHERE:([Northwind].[dbo].[Employees].[ReportsTo] as [e].[ReportsTo]=[Recr1006])) The list spool is also a lazy spool here meaning rows get inserted into the spool during execution of the recursive move also. Additionally the rows from the index transfer is read using a stack-like mechanism otherwise the recursive part may tour the same rows again. Here is how to read the plan with the recursive query: |--Index Spool(WITH STACK)|--Concatenation|--Compute Scalar(DEFINE:([Expr1013]=(0)))| |--Compute Scalar(be:([Expr1003]=(1)))| |--Clustered list Scan(disapprove:([Northwind].[dbo].[Employees].[PK_Employees]). WHERE:([Northwind].[dbo].[Employees].[ReportsTo]=(2))) The anchor move of the recursive CTE first gets executed and the transfer is created with index. say the stack option also in the spool. This indicates that rows are read in a FIFO manner. |--Nested Loops(Inner connect. OUTER REFERENCES:([Expr1015]. [Recr1006]. [Recr1007]. [Recr1008]))|--Compute Scalar(be:([Expr1015]=[Expr1014]+(1)))| |--Table Spool(WITH STACK)|--Compute Scalar(be:([Expr1009]=[Recr1008]+(1)))|--Clustered Index examine(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees] AS [e]). WHERE:([Northwind].[dbo].[Employees].[ReportsTo] as [e].[ReportsTo]=[Recr1006])) This is the nested circle join between the spool (created in step #1 for the anchor ask) and the recursive part of the query. Since this is eager transfer rows will be populated into the spool also until the recursion is completed or the maximum aim is reached. The maximum aim check is done using the insist operator above the nested loop join: 3. Now the way to express which spools are related or the same is to look at the properties of the transfer operator in the query plan output. The index spool has a property called NodeId which will referenced by the table transfer as PrimaryNodeId property in another part of the plan. Lastly. SQL Server can also create a plan with an eager spool which can be seen below for the query. In case of eager transfer query execution can act only after the eager transfer has been fully created. This is different from the lazy spool. | |--Table Spool| |--Hash Match(Inner connect. HASH:([c].[CustomerID])=([o].[CustomerID]). RESIDUAL:([Northwind].[dbo].[Customers].[CustomerID] as [c].[CustomerID]=[Northwind].[dbo].[Orders].[CustomerID] as [o].[CustomerID]))| |--Index Scan(disapprove:([Northwind].[dbo].[Customers].[Region] AS [c]))| |--Clustered list Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o])) |--Compute Scalar(DEFINE:([Expr1004]=alter_IMPLICIT(int,[Expr1010],0)))| |--Stream Aggregate(DEFINE:([Expr1010]=COUNT([Northwind].[dbo].[Orders].[ShipVia] as [o].[ShipVia])))| |--Hash be(Aggregate. chop:([o].[ShipVia]). RESIDUAL:([Northwind].[dbo].[Orders].[ShipVia] as [o].[ShipVia] = [Northwind].[dbo].[Orders].[ShipVia] as [o].[ShipVia]))| |--Table Spool 3. Similarly the ShipCountry distinct ascertain is computed using the same spool. You can see this by looking at the NodeId and PrimaryNodeId properties of the spool operators in the query plan or showplan xml. |--Compute Scalar(DEFINE:([Expr1005]=alter_IMPLICIT(int,[Expr1011],0)))|--Stream add up(be:([Expr1011]=ascertain([Northwind].[dbo].[Orders].[ShipCountry] as [o].[ShipCountry])))|--Hash be(add up. HASH:([o].[ShipCountry]). RESIDUAL:([Northwind].[dbo].[Orders].[ShipCountry] as [o].[ShipCountry] = [Northwind].[dbo].[Orders].[ShipCountry] as [o].[ShipCountry]))|--Table Spool 4. Finally since ascertain() add up always returns one row we just do a nested loop join between the two parts of the channelise above to return a row. |--Nested Loops(Inner Join)|--Compute Scalar(be:([Expr1004]=alter_IMPLICIT(int,[Expr1010],0)))....|--Compute Scalar(DEFINE:([Expr1005]=alter_IMPLICIT(int,[Expr1011],0)))

Forex Groups - Tips on Trading

Related article:
http://blogs.msdn.com/sqltips/archive/2007/08/30/spool-operators-in-query-plan.aspx

comments | Add comment | Report as Spam

I have been playing with LINQ from quite a few days I should say I am impressed with it. It does nearly everything out of box. Yes nearly every thing out of box. And the ORM is also very very powerful. In This affix I ordain dilate how to use Joins in LINQ queries. Many a times we want a query where by we retrieve the data from one table and some the related data from the other delay. Let says we undergo a category table and a posts table. Now when I acquire all the records of the posts I also want to have the related category name (which is there in the category table). So I need to make a join between three tables to get the records. Here is the LINQ Query to do the job. join cp in BlogBLL. CategoryPosts on p. PostId equals cp. PostId join c in BlogBLL. Categories on cp. CategoryID equals c. CategoryID So basically to alter a join between two tables we use the join keyword. After specifying the join keyword we need to give the column name on which the join will be made. And then we be to provide the condition on which the join ordain be made. Hence the on keyword with the condition. It’s so simple. If you experience a little bit of SQL then this syntax should be a cakewalk.

Forex Groups - Tips on Trading

Related article:
http://www.vikramlakhotia.com/post.aspx?postid=207

comments | Add comment | Report as Spam

decide iCalls_Calls. Call_ID,iCalls_Calls. Requestor,write,Scope,iCalls_Calls. Status_ID,iCalls_Status. Status_ID,iCalls_Status. Status_Label from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls. Status_ID=iCalls_Status. Status_ID ) inner join iCalls_Users on iCalls_Calls. Requestor=iCalls_Users. User_ID) left outer join iCalls_Messages on iCalls_Calls. Call_ID=iCalls_Messages. label_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls. Status_ID <> 6 order by iCalls_Calls. label_ID I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and back up '10' so the create i be is 5 / 10. And i need to put this query in a variable (Countrec) (as i need to bind Countrec to a repeater enumerate ) like select ascertain(*) from iCalls_Events where label_ID = " & Session("label_ID") & " ( / )select ascertain(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec select iCalls_Calls. label_ID,iCalls_Calls. Requestor,Type,Scope,iCalls_Calls. Status_ID,iCalls_Status. Status_ID,iCalls_Status. Status_denominate select Count(*) from iCalls_Events where Call_ID = " & Session("label_ID") & " ( / )select ascertain(*) from iCalls_Events where label_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls. Status_ID=iCalls_Status. Status_ID ) inner join iCalls_Users on iCalls_Calls. Requestor=iCalls_Users. User_ID) left outer join iCalls_Messages on iCalls_Calls. label_ID=iCalls_Messages. Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls. Status_ID <> 6 request by iCalls_Calls. Call_ID decide ascertain(*),iCalls_Calls. Call_ID,iCalls_Calls. Requestor,Type,Scope,iCalls_Calls. Status_ID,iCalls_Status. Status_ID,iCalls_Status. Status_Label from iCalls_Events inner join iCalls_Calls on(iCalls_Events. Call_ID = iCalls_Calls. Call_ID) inner join iCalls_Status on (iCalls_Calls. Status_ID=iCalls_Status. Status_ID ) inner join iCalls_Users on (iCalls_Calls. Requestor=iCalls_Users. User_ID) left outer join iCalls_Messages on (iCalls_Calls. Call_ID=iCalls_Messages. Call_ID) where Requestor='" & Session("User_ID") & "' AND iCalls_Calls. Status_ID <> 6 and Call_ID = Session. Call_ID and Events_Flag <> 0 order by iCalls_Calls. Call_IDthis syntax is correct but u r using more than four join it leads to performance issue u check this query by running estimated execution intend.

Forex Groups - Tips on Trading

Related article:
http://www.dotnetspider.com/qa/Question99449.aspx

comments | Add comment | Report as Spam

In our measure SQL post we talked about the three basic types of JOINs enterprising young SQL junkies might use to gather data from different tables in their databases. "Enterprising..." Oh how I change myself up some times. Seriously. Don't make me explain why that's funny. Anyway in between feature Trek-related examples we blabbed on and on about INNER JOINs three kinds of OUTER JOINs and the almost completely useless CROSS connect. You might bequeath though that every one of those brilliant examples involved querying existing data with SELECT statements. That's fine and dandy. After all that's also how you'll use those JOINs about 98% of the time. However. JOINs aren't just confined to decide statements... No sir. You can use JOINs in your INSERT. modify and remove statements too. Sit back and watch. First let's resurrect our consume tables from the last post. If you'll recall we had a STARSHIP delay that contained the following data: So let's have some fun. First we're going to add another preserve to our OFFICER table. Let's say that we undergo a dashing new command who we want to assign to the USS Justinian. feature hurry HR gave us the info we be for the firstname lastname and rank fields (Justin. Stanley and Grand Poobah respectively) but we comfort need to know the alter value to insert into the currentshipid field. One way to go about that would be to simply be it up first by throwing a decide statement at our STARSHIP table writing down the shipid value that corresponds to the USS Justinian then manually adding that to our normal INSERT statement: Obviously that works just fine and you'll probably end up doing that most of the time. But damn it. Jim! This post is about using JOINs (or ordain be eventually...) so that's what we're cover hell gonna do. Before we get to that move though we be to get familiar with the INSERT... SELECT syntax. So let's write that ask. The first part looks desire your regular ol' attach statement. You'll notice though that the syntax changes a bit right after we specify the column names of the destination delay: (say: I didn't need to contract a determine for the officerid column because we set it up as an IDENTITY field that'll autoincrement when new records are added when the table was initially created.) Almost seems to overcomplicate things doesn't it? Maybe you thought that you could get away with something like this instead: A lot of people probably try that one first then get pissed when it doesn't work. I'm one of them. Thing you undergo to remember is that the SELECT statement doesn't necessarily return a scalar determine but that's what the INSERT statement (written in this change) requires. Even if you write the decide part so that it only returns one preserve (using TOP for instance) this query will still poo itself. And that brings us to a big warning: when you're using the INSERT... decide statement be 100% certain that the SELECT move only returns a hit row. Otherwise there's a pretty good come about that you'll end up INSERTing more rows than you intended. For example what if I was trying to appoint this new command to Picard's Enterprise instead? If I just changed the WHERE statement in my query to 'WHERE s name = 'Enterprise',' I'd be in for a rude awakening. That query would go results for both ships named Enterprise in my table and therefore would attach two rows into the command table with my new guy's info one with shipid = 1 and another with shipid = 2 (the shipid's for the two ships named 'Enterprise' in my STARSHIP delay). So let's get to the JOIN example. Let's say that we have another new command to add to the delay. Again. Star Fleet HR gives us his firstname lastname and rank ('Luke'. 'Skoubo'. 'Junior Lackey') but this time they also tell us that he's to answer under Grand Poobah Stanley. come up again we could just use a SELECT statement to find the currentshipid for Stanley's preserve and use that to create your attach. Boring. We're going to use a JOIN so we only have to write one ask and don't undergo to reach fishing through our desk for a pen and Post-It say: Simple eh? Again though just undergo to alter sure that the SELECT move only pulls a hit record so you don't end up INSERTing more than you expected. UPDATEs work pretty much the same way and frankly. I can think of many more situations in which I've used a connect in UPDATEs than INSERTs. For example what if Star Fleet decided to implement some cost-cutting measures by decommissioning the old Constitution class Enterprise and transferring all it's personnel over to.. oh what's it called? That one displace that Grand Poobah Stanley runs? As the Star hurry DBA we need to verify that the data in the OFFICER delay reflects that change and adjust our currentshipid fields accordingly: Now Kirk and Spock are Stanley's minions. Like the attach example. I still had to be careful that the subquery I used to find Stanley's currentshipid only returned on row. However unlike that previous query wacky things won't happen if my subquery returns multiple results. Instead it'll just fail altogether with a warning that it can only return a hit record if I expect things to actually bring home the bacon. Finally a remove example. It's very similar to the others but I find it a little counter-intuitive in its redudancy. With the DELETE... connect statement you have to list the table twice using the FROM statement to mouth the JOIN. Check out the example. Let's say that the Reliant is destroyed while Chekov and Khaaaaaan are both still on board. feature Fleet anxious to sweep that dirty little hijacking under the rug asks you to delete all records of OFFICERs assigned to that ride at the measure: You might try something like "remove FROM command INNER connect..." but you'll faily miserably. Sorry but I didn't write the language or standards. come up. I don't experience about you but that's about all the JOINing I can act for awhile. As always happy to hear any questions comments or suggestions. Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Forex Groups - Tips on Trading

Related article:
http://www.nerdliness.com/article/2007/08/17/oh-joy-more-joins

comments | Add comment | Report as Spam

Install Northwind for SQL Express and use Visual Studio and DLINQ to query it is remove and comes with Visual Studio but the sample Northwind database isn’t included. You can transfer consume databases Northwind and Pubs from. (or you can use the Fox Upsizing wizard to displace the fox native Northwind to SQL convey or SQL Server) To install Northwind or migrate any other MSDE data kill a stored procedure using the dominate lie program SQLCMD see -------------------------------------------------------------------------------------------------------------------------------- 1> exec sp_attach_db 'NorthWind','C:\SQL Server 2000 consume Databases\northwnd mdf','C:\SQL Server 2000 Sample Databases\northwnd ldf' Converting database 'NorthWind' from version 539 to the current version 611. Database 'NorthWind' running the grade go from version 539 to version 551. Database 'NorthWind' running the upgrade step from version 551 to version 552. Database 'NorthWind' running the grade step from version 552 to version 553. Database 'NorthWind' running the grade step from version 553 to version 554. Database 'NorthWind' running the upgrade go from version 554 to version 589. Database 'NorthWind' running the upgrade go from version 589 to version 590. Database 'NorthWind' running the grade step from version 590 to version 593. Database 'NorthWind' running the grade go from version 593 to version 597. Database 'NorthWind' running the upgrade go from version 597 to version 604. Database 'NorthWind' running the grade go from version 604 to version 605. Database 'NorthWind' running the upgrade go from version 605 to version 606. Database 'NorthWind' running the upgrade go from version 606 to version 607. Database 'NorthWind' running the grade step from version 607 to version 608. Database 'NorthWind' running the grade go from version 608 to version 609. Database 'NorthWind' running the grade go from version 609 to version 610. Database 'NorthWind' running the upgrade step from version 610 to version 611. To enable other users on other machines to connect to SQL Express enable remote connectivity: see Go to Server Explorer right move on Data Connections choose Add Connection. For Data Source decide Microsoft SQL Server For Server label type “.\SQLExpress� then Select database label Northwind. grow Server Explorer->SQLExpress. Northwind\Tables and draw/displace Customers. Orders and request Details onto the DataClasses1 dbml design surface. Expand Stored Procedures and Drag/Drop “Ten Most Expensive Products� onto the alter move of the designer. In Solution Explorer move on “show All Files� and act a be at all the code that was generated for you in DataClassses1. Designer. VB. In order to get the be amount ordered per customer we be to calculate the sum of orders per customer. This is a 3 table join: To calculate the amount for a particular request the detail table lie items need to be examined: The amount ordered is the sum of the dilate quantity multiplied by the unit determine (less any reject) "DRIVER=SQL Server;SERVER=.\sqlexpress;DATABASE=northwind;Trusted_Connection=Yes" The Linq generated SQL statement sent to SQL convey can be seen from the log: decide (alter(Real,(alter(Decimal(29,4),CONVERT(Int,[t2].[Quantity]))) * [t2].[UnitPrice])) * (@p0 - [t2].[Discount]) AS [value]. [t0].[CustomerID] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20808.0 say: the AsEnumerable is required only for Beta 2: it’s due to a bug with composite keys that will be fixed. Now a more complicated ask: What are NorthWind’s beat customers by calculating Sales total by customer with % of all customer orders. PctTotal = Sum(100 * (det. Quantity * det. UnitPrice * (1 - det. Discount)) / _ The Fox label executes nearly instantaneously but the Linq code took a long time. The log showed that the Aggregate was being calculated repeatedly. A simple optimization was to calculate the constant SumDetails before the ask and it executed much faster. PctTotal = Sum(100 * (det. Quantity * det. UnitPrice * (1 - det. Discount)) / _ BTW try typing some of the label to undergo the intellisense within VB Linq! If you would like to acquire an telecommunicate when updates are made to this affix please register Subscribe to this affix's comments using MSDN Blog Postings &raquo; lay Northwind for SQL convey and use Visual Studio and DLINQ to ask it Friday. August 17. 2007 10:00 PM by re: lay Northwind for SQL convey and use Visual Studio and DLINQ to query it Friday. August 17. 2007 10:17 PM by In this affix ( Install Northwind for SQL Express and use Visual Studio and DLINQ to ask it ) I posted

Forex Groups - Tips on Trading

Related article:
http://blogs.msdn.com/calvin_hsia/archive/2007/08/17/install-northwind-for-sql-express-and-use-visual-studio-and-dlinq-to-query-it.aspx

comments | Add comment | Report as Spam

accept to our new external user forums with added features such as RSS feeds a look rating system improved search and navigation capabilities and contributions from Business Objects staff. Our can still be viewed in read only change. Our forums accept our customers to interact with each other for advice on product uses and best-practices. The forums are not intended to be a replacement for telecommunicate or interactive web give. To ensure rapid issue resolution please log support cases through the divide of our site or by phone. I have finally decided to recompile a desire standing (and working) application from VS 2002 (. NET 1) to VS 2005 (. NET 2). The grade process was succcessful. As I opened and saved each existing report I was presented with warning about reports being saved in the new format which I accepted. When I ran the application half of the reports did not bring home the bacon. Most commonly the errors were "Unknown ask Engine Error". "Failed To Open Rowset". "remove Parameter" etc. Admitably I was always using (and comfort use in my newer VS 2005 projects without any problems) a combination of Datasets and SQL Server tables in a mixed source scenario. Crystal warns me about "More than one database driver has been used..." but I'm still able to act Database Expert Linking etc. The reason is that I prepare more complex data in the dataset and usually link to lookup tables in the MS SQL Server. I always use Inner Join Links (in the database linking expert) as I experience that the data in lookup tables exists and the data that I undergo prepared in the dataset has all realtions set. In VS 2002 if I move on the show SQL Statement and the alter grayed out dialog shows no SQL statement. In VS 2005 however the SQL dialog box shows the SQL statement it intends to execute and this invariably contains go across JOIN between some of the tables. A go across connect then retrieves thousands of records instead of one and the report fails with "Unknown ask Engine Error". I've used SQL Trace to observe SQL statements being executed against the old and new version and they are different. The old version executes more then one SQL statement (it brakes the execution in smaller fragments neccessary to retrieve data). The new version is trying to kill this "Get Everthing the First Time" ask that has a CROSS JOIN (eeeek) inside. The results are unpredictable. If I rearange links in the Database Expert window I might get the result I want. But I still don't undersand the logic where and when will the go across JOIN be used as I implicitly act each cerebrate as INNER connect. Why would CR ever use CROSS connect? Cross Joins are useless.

Forex Groups - Tips on Trading

Related article:
http://technicalsupport.businessobjects.com/cs/forums/thread/8409.aspx

comments | Add comment | Report as Spam

Join Queries A join ask is a ask retrieving records from more than one table. Records from different tables are usually joined on related key field values. The most efficient and effective forms of join are those between directly related primary and foreign key fields. There are a number of different types of joins: . Inner Join An intersection between two tables using matching handle values returning records common to both tables only. Inner join syntax is as follows: SELECT … FROM delay [alias] [. … ] [ INNER JOIN table [alias] [ USING (field [. … ]) | ON (field = handle [{AND | OR} [NOT] [ … ]) ] ] [ WHERE … ] [ assort BY … ] [ request BY … ]; The following query is an inner join because it finds all publishers and related published editions. The two tables are linked based on the established primary key to foreign key relationship. The primary key is in the PUBLISHER delay on the one side of the one-to-many relationship between the PUBLISHER and EDITION tables. The foreign key is precisely where it should be on the many align of the one-to-many relationship. decide P. NAME AS PUBLISHER. E. ISBN FROM PUBLISHER P JOIN EDITION E USING (PUBLISHER_ID); PUBLISHER ISBN ——————————– ———- lose Press 1585670081 Ballantine Books 345333926 Ballantine Books 345336275 Ballantine Books 345438353 Bantam Books 553293362 Spectra 553278398 Spectra 553293370 Spectra 553293389 Oxford University touch 198711905 L P Books 893402095 Del Rey Books 345308999 Del Rey Books 345334787 Del Rey Books 345323440 Books on attach 5553673224 Books on attach 5557076654 A common programming error is to get the intend of the WHERE and HAVING clause filters mixed up. The WHERE clause filters records as they are read (as I/O activity takes displace) from the database. The HAVING clause filters aggregated groups after all database I/O activity has completed. Don t use the HAVING clause when the WHERE clause should be used and endorse versa. 137 Reading and Writing Data with SQL In inspect you be quality webspace to host and run your web applications try our services.

Forex Groups - Tips on Trading

Related article:
http://domain.tomcatjavahosting.com/domain/join-queries-a-join-query-is-free-web-hosting-services-a-query/

comments | Add comment | Report as Spam