?
Solved

Select statement on a cross join select

Posted on 2010-08-19
4
Medium Priority
?
445 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
  • 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 2000 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
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.
Suggested Courses

612 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