Solved

Select statement on a cross join select

Posted on 2010-08-19
4
440 Views
Last Modified: 2012-05-10
I have the cross join select below working fine...and actually created a view from it...which I've been instructed to get rid of.  Our company has decided "no views"

So...
I need to nest this in a much larger stored procedure so I can do a join on it or a select

Example Select pev.AppReceived from (My code below...)
SELECT	*
FROM	(SELECT	CRMPROD_01.dbo.P_Deals.dealId,
				CRMPROD_01.dbo.P_Events_Table_Types.eventType, 
				CRMPROD_01.dbo.P_Events_Table.eventData
		 FROM	CRMPROD_01.dbo.P_Deals CROSS JOIN
				CRMPROD_01.dbo.P_Events_Table_Types LEFT JOIN
				CRMPROD_01.dbo.P_Events_Table ON CRMPROD_01.dbo.P_Events_Table.dealId = CRMPROD_01.dbo.P_Deals.dealId AND 
				CRMPROD_01.dbo.P_Events_Table.eventTypeID = CRMPROD_01.dbo.P_Events_Table_Types.eventTypeID) Pv PIVOT (MAX(eventData) FOR
				[eventType] IN 
					([AppReceived], [AppToClosingAgent], [DisclSent], [DisclSigned], [DisclToClosingAgent], [DisclToCF], [PATargetDate], [PASent], 
                      [PASigned], [PAComplete], [PAtoCF], [ToCounsel], [PetitionFiled], [HearingDate], [PendingCO], [PendingCOExpl], [CourtOrder], [CourtOrderRcd], 
                      [InsuranceApproved], [InsuranceDenied], [InsPolicyIssued], [Hold], [Dropped], [Redocumented], [AutoProjFunding], [ManProjFunding], 
                      [FundsRequested], [AmtRequested], [PreFunded], [FundsRcd], [Funded], [Closed], [InvBookCreated], [InvBookSent], [AP_BL_Requested], [AP_BL_In], 
                      [SetAgrIn], [DriverLicIn], [SocSecIn], [MarCertIn], [DivDecreeIn], [DCIn], [OtherDocDesc1], [OtherDocDate1], [OtherDocDesc2], [OtherDocDate2], 
                      [OtherDocDesc3], [OtherDocDate3], [NASPSearch], [NASPClearance], [NASPDealEntered], [NASPNotes])) AS pev

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33475489
If you want to avoid use of view, create a temporary table in your database
Create a stored procedure that will insert this code into that temporary table.
Before you execute the stored procedures that need this data, execute the Stored Procedure that will first clear the temporary table and inserts the data (this will get the latest data)
Then use this temporary table (instead of view) whereever you needed.

Raj
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33475491
DID you try using a table valued function
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 33475538
Did I understand your requirment right ?

Stored Procedure that updates the temporary table may look like attached.

Raj
CREATE PROCEDURE usp_UpdateData
AS
BEGIN

-- First clear previous records
TRUNCATE TABLE YourTempTable

-- Insert data into temporary table
INSERT INTO YourTempTable
	SELECT	*
	FROM	(SELECT	CRMPROD_01.dbo.P_Deals.dealId,
				CRMPROD_01.dbo.P_Events_Table_Types.eventType, 
				CRMPROD_01.dbo.P_Events_Table.eventData
		 FROM	CRMPROD_01.dbo.P_Deals CROSS JOIN
				CRMPROD_01.dbo.P_Events_Table_Types LEFT JOIN
				CRMPROD_01.dbo.P_Events_Table ON CRMPROD_01.dbo.P_Events_Table.dealId = CRMPROD_01.dbo.P_Deals.dealId AND 
				CRMPROD_01.dbo.P_Events_Table.eventTypeID = CRMPROD_01.dbo.P_Events_Table_Types.eventTypeID) Pv PIVOT (MAX(eventData) FOR
				[eventType] IN 
					([AppReceived], [AppToClosingAgent], [DisclSent], [DisclSigned], [DisclToClosingAgent], [DisclToCF], [PATargetDate], [PASent], 
                      [PASigned], [PAComplete], [PAtoCF], [ToCounsel], [PetitionFiled], [HearingDate], [PendingCO], [PendingCOExpl], [CourtOrder], [CourtOrderRcd], 
                      [InsuranceApproved], [InsuranceDenied], [InsPolicyIssued], [Hold], [Dropped], [Redocumented], [AutoProjFunding], [ManProjFunding], 
                      [FundsRequested], [AmtRequested], [PreFunded], [FundsRcd], [Funded], [Closed], [InvBookCreated], [InvBookSent], [AP_BL_Requested], [AP_BL_In], 
                      [SetAgrIn], [DriverLicIn], [SocSecIn], [MarCertIn], [DivDecreeIn], [DCIn], [OtherDocDesc1], [OtherDocDate1], [OtherDocDesc2], [OtherDocDate2], 
                      [OtherDocDesc3], [OtherDocDate3], [NASPSearch], [NASPClearance], [NASPDealEntered], [NASPNotes])) AS pev
END

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 33479560
Thanks...put me on the right track
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question