logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Login


ilive4fri
#1 Posted : Wednesday, April 23, 2014 10:35:00 AM(UTC)
ilive4fri
Joined: 4/8/2014(UTC)
Posts: 1
Location: Ohio

Does anyone have a batch of SQL scripts I can look at?  I'm looking into using PowerPiviot for Excel instead of Crystal Reports and I'm not all that familiar with SQL Scripts when joining databases etc.  So I'm just looking for examples of things I might be able to learn syntax from.

 

Thanks!

Stephanie

Dan Van Fleet
#2 Posted : Wednesday, April 23, 2014 1:15:38 PM(UTC)
Dan Van Fleet
Joined: 9/27/2013(UTC)
Posts: 34

Was thanked: 1 time(s) in 1 post(s)

Hey Stephanie,

I learned a ton by looking at views or stored procedures other people have made.  I Focused on views first since stored procedures can be way too complex.  I'll run through a couple views in Enterprise that use Joins. 

In SQL Server Management Studio (SSMS) opening up the design view of the ProForm Classic Database view "dbo.DataCollector_LastModDate" shows the following statement.

SELECT     S.FirmFile, S.FilePath, L.LastModDate
FROM         dbo.Search AS S INNER JOIN
                     dbo.LastModification AS L ON S.FirmFile = L.FirmFile

Reading the statement starting on the FROM line, we are using Search and renaming it "S", further it Inner joins to LastModification calling that "L".  Once defined  those tables must be called by their alias as is done  in the Select section.

The columns that should match are both named FirmFile from tables "L" and "S".  They could be named anything.  In the next example the Table.ID column is joined to another table by column name TableID this is a common practice.

One reason to use these shortcuts or aliases, is to shorten the select statement. We can't just ask for FirmFile, so we ask for either Search.FirmFile, or by alias S.FirmFile

A more complex example is in "OrderHistoryIncludingIncompleteNodes"

SELECT DISTINCT
                     TOP (100) PERCENT dbo.WF_WorkflowNode.ID, dbo.WF_WorkFlowNodeProperties.Description, dbo.WF_Order.FirmFile, dbo.WF_WorkflowHistory.StartDate,
                     dbo.WF_WorkflowHistory.CompleteDate, User_3.Name AS CompletedBy, User_1.Name AS ReassignedBy, User_2.Name AS DeletedBy,
                     User_4.Name AS CancelledBy
FROM         dbo.WF_WorkflowNode INNER JOIN
                     dbo.WF_WorkflowHistory ON dbo.WF_WorkflowNode.ID = dbo.WF_WorkflowHistory.WorkflowNodeID INNER JOIN
                     dbo.WF_WorkFlowNodeProperties ON dbo.WF_WorkflowNode.WorkflowNodePropertiesID = dbo.WF_WorkFlowNodeProperties.ID INNER JOIN
                     dbo.WF_Order ON dbo.WF_WorkflowHistory.OrderID = dbo.WF_Order.ID LEFT OUTER JOIN
                     dbo.WF_User AS User_4 ON dbo.WF_WorkflowHistory.OrderCancelByUserID = User_4.ID LEFT OUTER JOIN
                     dbo.WF_User AS User_2 ON dbo.WF_WorkflowHistory.OrderDeleteByUserID = User_2.ID LEFT OUTER JOIN
                     dbo.WF_User AS User_1 ON dbo.WF_WorkflowHistory.ReassignedByUserID = User_1.ID LEFT OUTER JOIN
                     dbo.WF_User AS User_3 ON dbo.WF_WorkflowHistory.CompletedByUserID = User_3.ID
ORDER BY dbo.WF_WorkFlowNodeProperties.Description, dbo.WF_WorkflowHistory.StartDate, dbo.WF_WorkflowHistory.CompleteDate

Driven by dbo.WF_WorkflowNode it Inner Joins on the column WorkflowNode.ID to WorkflowHistory,
it also Inner Joins on a different column WorkFlowNodePropertiesID to dbo.WF_WorkFlowNodeProperties.
The final Inner Join connect dbo.WF_WorkFlowHistory to db.WF_Order on the Order.ID (or OrderID) columns.

Next it adds in some Aliased tables that all point to the same base table using different join columns, providing different results columns which also get aliased in an excellent way that ties the whole view together into a story. But I'm getting ahead of myself 

It is using a left outer join meaning we don't care if there is a match to the right table, still include the base record.

First it defines an alias User_4, which we can think of as a table seperate from WF_User. In the end we need to match a name with an ID column.  So it collects a record(s) in WF_User that match the user ID who canceled the order documented in WF_WorkflowHistory.  The end result columns of the view will include that User's Name.

The next line gets the user who Deletes and temporarily calls it User_2

Followed by the ReassignedBy User, and the CompletedBy User referred to as User_1 and User_3 respectively.

Finally the results are ordered by a few things and spit out as a table for all to enjoy.

Be aware, like Stored Procedures, Views can be crazy complex too. They are a great educational tool.

hope this is helpful,

Dan

Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF | YAF © 2003-2019, Yet Another Forum.NET
This page was generated in 0.150 seconds.
SolidRock