Can I use table vars or temp tables in a VIEW ? 1

[KCcasey]

Hi Folks,

I've got a query that I'd like to create as a view... its necessary for it to perform some aggregations in some temp tables - - how can I go about this??

I've tried create tables, temp tables and table vars with no joy; I guess I want to know ifs its possible, and if so how?

[COLOR=red yellow]Thanks in advance for any help you can offer !!! - - Casey[/color]

heres my code (using CREATE TABLE statements):

CREATE proc v_SAP_CONTACTS
AS

if object_id('[mk extract].dbo.tmptbl_UniqueContactId') is not null
    drop table [mk extract].dbo.tmptbl_UniqueContactId

if object_id('[mk extract].dbo.tmptbl_AllContacts') is not null
    drop table [mk extract].dbo.tmptbl_AllContacts

CREATE TABLE [mk extract].dbo.tmptbl_UniqueContactId 
	(mkcustno varchar(15), uniqueID int identity(1,1))

CREATE TABLE [mk extract].dbo.tmptbl_AllContacts
	(status varchar(20), contactname varchar(60), surname varchar(60), salutation varchar(30), sterm1 varchar(60), sterm2 varchar(60), country varchar(10), mkcustno varchar(15))

INSERT INTO [mk extract].dbo.tmptbl_UniqueContactId
	SELECT 
	'M_' + LTRIM(RTRIM(K.use_cuno)) AS MKCUSTNO
	FROM         dbo.MK_CM_Key AS K LEFT OUTER JOIN
						  dbo.ttccom010010 AS C ON K.t_cuno = C.t_cuno LEFT OUTER JOIN
						  dbo.xref_salut AS xS ON K.use_cuno = xS.t_cuno
	WHERE     (PATINDEX('% %', RTRIM(LTRIM(C.t_dsca2))) > 0)
	UNION
	SELECT     
	cast(MKCUSTNO as varchar) as MKCUSTNO
	FROM         Data_Laundering.dbo.v_Contacts AS VDC
	WHERE     (MKCUSTNO IS NOT NULL)

INSERT INTO [mk extract].dbo.tmptbl_AllContacts
	SELECT
	CASE WHEN (len(c.t_dsca2) - patindex('% %', rtrim(ltrim(C.t_dsca2)))) > 0 THEN xS.salut ELSE NULL END AS STATUS, 
	CASE WHEN patindex('% %', rtrim(ltrim(C.t_dsca2))) > 0 THEN rtrim(LEFT(C.t_dsca2, patindex('% %', rtrim(ltrim(C.t_dsca2))))) ELSE CASE WHEN patindex('% %', 
	rtrim(ltrim(C.t_dsca2))) >= 0 THEN C.t_dsca2 ELSE NULL END END AS CONTACTNAME, 
	CASE WHEN patindex('% %', rtrim(ltrim(C.t_dsca2))) > 0 THEN ltrim(RIGHT(rtrim(C.t_dsca2), (len(c.t_dsca2) - patindex('% %', rtrim(ltrim(C.t_dsca2)))))) ELSE NULL END AS SURNAME, 
	'' AS SALUTATION, 
	CASE WHEN patindex('% %', rtrim(ltrim(C.t_dsca2))) > 0 THEN ltrim(RIGHT(rtrim(C.t_dsca2), (len(c.t_dsca2) - patindex('% %', rtrim(ltrim(C.t_dsca2)))))) ELSE NULL END AS STERM1, 
	LEFT(C.t_nama, 20) AS STERM2, 
	C.t_ccty AS COUNTRY, 
	'M_' + LTRIM(RTRIM(K.use_cuno)) AS MKCUSTNO
	FROM dbo.MK_CM_Key AS K LEFT OUTER JOIN dbo.ttccom010010 AS C 
		ON K.t_cuno = C.t_cuno 
	LEFT OUTER JOIN	dbo.xref_salut AS xS 
		ON K.use_cuno = xS.t_cuno
	WHERE     (PATINDEX('% %', RTRIM(LTRIM(C.t_dsca2))) > 0)
	UNION
	SELECT     
	CASE VDC.STATUS WHEN '0001' THEN 'Mr' WHEN '0002' THEN 'MS' ELSE 'Neuter Gender' END AS STATUS, 
	ContactName AS CONTACTNAME, 
	Surname AS SURNAME, 
	Salutation AS SALUTATION, 
	STERM1, 
	STERM2, 
	'AU' AS Expr1, 
	cast(MKCUSTNO as varchar) as MKCUSTNO
	FROM         Data_Laundering.dbo.v_Contacts AS VDC
	WHERE     (MKCUSTNO IS NOT NULL)

select 
AllContacts.status, 
AllContacts.contactname,
AllContacts.surname,
AllContacts.salutation,
AllContacts.sterm1,
AllContacts.sterm2,
AllContacts.country,
UniqueContactId.mkcustno + '_' + cast(UniqueContactId.uniqueID as varchar) as MKCUSTNO
from [mk extract].dbo.tmptbl_AllContacts AllContacts inner join [mk extract].dbo.tmptbl_UniqueContactId UniqueContactId
	on ltrim(rtrim(AllContacts.mkcustno)) = ltrim(rtrim(UniqueContactId.mkcustno))

if object_id('[mk extract].dbo.tmptbl_UniqueContactId') is not null
    drop table [mk extract].dbo.tmptbl_UniqueContactId

if object_id('[mk extract].dbo.tmptbl_AllContacts') is not null
    drop table [mk extract].dbo.tmptbl_AllContacts

 

[Crowley16]

no, you can't do it in a view...

views are pretty much selects only...

--------------------
Procrastinate Now!