Select statement on a cross join select

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

Larry Bristersr. DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
vdr1620Commented:
DID you try using a table valued function
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks...put me on the right track
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.