asked on

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

Rajkumar Gs

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

vdr1620

DID you try using a table valued function

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Thanks...put me on the right track