Can I do this? Declare a table and then use it? 2

[Crystalguru]

MSSQL 2000

Can I declare a table variable, then use it for another table variable?

Here is my code:

Declare @Patients Table
		(ClientKey numeric(16,0) NULL,
		UnitKey numeric(16,0) NULL)

INSERT INTO @Patients
SELECT
PatientVisit.ClientKey as ClientKey
,Unit.Key as UnitKey

FROM Unit
INNER JOIN PatientVisit
ON PatientVisit.UnitKey = Unit.Key

/*********************************
Find Count of Patients by Discharge Date
**********************************/
Declare @Discharge Table
	(UnitKey Numeric(16,0) NULL,
	 Count_Patients Numeric(16,0) NULL)

Insert into @Discharge

SELECT
Unit.Key
,Count(Patients.ClientKey) as Count_Patients
FROM Unit
INNER JOIN @Patients
ON @Patients.UnitKey = Unit.Key

Group by Unit.Key

 

[gmmastros]

When you use table variables, often times you need to give them an alias (especially when joining to other tables).

Declare @Patients Table
        (ClientKey numeric(16,0) NULL,
        UnitKey numeric(16,0) NULL)

INSERT INTO @Patients
SELECT
PatientVisit.ClientKey as ClientKey
,Unit.Key as UnitKey

FROM Unit
INNER JOIN PatientVisit
ON PatientVisit.UnitKey = Unit.Key

/*********************************
Find Count of Patients by Discharge Date
**********************************/
Declare @Discharge Table
    (UnitKey Numeric(16,0) NULL,
     Count_Patients Numeric(16,0) NULL)

Insert into @Discharge

SELECT
Unit.Key
,Count([!]P[/!].ClientKey) as Count_Patients
FROM Unit
INNER JOIN @Patients [!]As P[/!]
ON [!]P[/!].UnitKey = Unit.Key
Group by Unit.Key

I may have missed some aliases, but hopefully you get the point.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

You must give it an alias:

Insert into @Discharge
SELECT Unit.Key,
       Count(Patients.ClientKey) as Count_Patients
FROM Unit
INNER JOIN @Patients Patients
ON Patients.UnitKey = Unit.Key
Group by Unit.Key

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Crystalguru]

Sweet! That was it! Thanks so much.