jdbrown239 (8/26/2012)

---

Need to create view with no outer joins so I can index the view. Here is the query I use to create the view. Does anyone no of a better way?


SELECT c.cts_app_fkey AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER,
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey
WHERE (DATEDIFF(MINUTE, c.CTS_LAST_SAVED_DATE, GETDATE()) < 50) AND (DATEDIFF(MINUTE, c.CTS_LAST_SAVED_DATE, GETDATE()) > 30)

SELECT c.cts_app_fkey AS APP_NUMBER
	,a.CREDIT_AMOUNT
	,REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM
	,c.cts_contract_number AS APP_CONTRACT_NUMBER
	,CASE co.CO_UD_APP_STATUS_TBDESC
		WHEN 'PO Issued'
			THEN CASE a.APP_DECISION_CODE
					WHEN 7
						THEN 'Booked'
					ELSE 'PO Issued'
					END
		WHEN 'Approved'
			THEN CASE co.CO_LESSOR
					WHEN '421'
						THEN 'Approved - SFP'
					WHEN '423'
						THEN 'Approved - SFP'
					WHEN '424'
						THEN 'Approved - SFP'
					ELSE 'Approved'
					END
		ELSE co.CO_UD_APP_STATUS_TBDESC
		END AS APPLICATION_STATUS
	,CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE
	,CONVERT(XML, a.APP_DATA, 0).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER
	,CONVERT(XML, a.APP_DATA, 0).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA
	,CASE 
		WHEN LEN(CONVERT(XML, a.APP_DATA, 0).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8
			THEN CONVERT(XML, a.APP_DATA, 0).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')
		ELSE NULL
		END AS CCAN
	,CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA, 0).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED
	,CONVERT(XML, a.APP_DATA, 0).value('(//FED.ID/node())[1]', 'VARCHAR(50)') AS FEDERAL_ID
FROM dbo.APPLICATION AS a
LEFT JOIN dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY
LEFT JOIN dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY
LEFT JOIN dbo.Contract_Setup AS c ON a.app_key = c.cts_app_fkey
WHERE (DATEDIFF(MINUTE, c.CTS_LAST_SAVED_DATE, GETDATE()) < 50)
	AND (DATEDIFF(MINUTE, c.CTS_LAST_SAVED_DATE, GETDATE()) > 30)

jdbrown239 (8/26/2012)

---

Need to create view with no outer joins so I can index the view.

Luis Cazares (8/27/2012)

---

I can't find a restriction for indexed views that involves outer joins http://msdn.microsoft.com/en-us/library/ms191432.aspx. Are you sure that's the reason? and that's what you need?
OUTER JOINS have a very specific function and can't be really replaced by something else without changing the results (OUTER APPLY is similar but I don't believe it'll solve your problem).

Luis Cazares (8/27/2012)

---

I guess it was wrong for me to read the 2012 version as it is less explicit.
But I still believe the OP won't have an option on creating the indexed view with this query. At least it won't give the same output.

Sean Lange (8/27/2012)

---

I would agree. I think that properly indexing the base tables as Lowell suggested is the most likely path to a solid solution in this case. With no more details to work with though it is hard to say.

jdbrown239 (8/27/2012)

---

Sorry for the initial messy post.

I think you guys are right. I have tried writing the view without joins and the result set is never the same. I will look at indexing the tables.

Thanks!

WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())