Creating 3rd Tab from LOJ Results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating 3rd Tab from LOJ Results

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-31 : 16:51:39

I am attempting to create a third table (tab_COBMembr_NoMatch) resultant of a left outer join (LOJ) performed on two other tables (_Orig & _New). Here's the code....

SET NOCOUNT OFF

--- process file 1
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_COBMembr_Orig]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tab_COBMembr_Orig

CREATE TABLE tab_COBMembr_Orig
(
[COBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[CountName] int NULL,
[MemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL
)
;
INSERT INTO tab_COBMembr_Orig (COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, CountName)
SELECT COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, Count(Name) AS CountName
FROM COBMEMBR
GROUP BY COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
ORDER BY CountName DESC, COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
;
SELECT * FROM tab_COBMembr_Orig
;
--- process file 2
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_COBMembr_New]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tab_COBMembr_New

CREATE TABLE tab_COBMembr_New
(
[COBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[CountName] int NULL,
[MemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL
)
;
INSERT INTO tab_COBMembr_New (COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, CountName)
SELECT COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, Count(Name) AS CountName
FROM tab_COBMEMBR_Work
GROUP BY COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
ORDER BY CountName DESC, COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
;
SELECT * FROM tab_COBMembr_New
;
---OK create the output file
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_COBMembr_NoMatch]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE tab_COBMembr_NoMatch

CREATE TABLE tab_COBMembr_NoMatch
(
[COBID] varchar (10) NULL,
[NCOBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[NName] varchar (30) NULL,
[CountName] int NULL,
[NCountName] int NULL,
[MemberNum] varchar (16) NULL,
[NMemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[NMedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL,
[NStartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL,
[NTermDate] varchar (10) NULL
)
;
INSERT INTO tab_COBMembr_NoMatch (
COBID,Name,MemberNum, MedPrimary, STARTDATE, TERMDATE, CountName,
NCOBID,NName,NMemberNum, NMedPrimary, NSTARTDATE, NTERMDATE, NCountName
)
SELECT
o.COBID, o.Name, o.MemberNum, o.MedPrimary, o.STARTDATE, o.TERMDATE, o.CountName,
n.COBID, n.Name, n.MemberNum, n.MedPrimary, n.STARTDATE, n.TERMDATE, n.CountName
FROM tab_COBMEMBR_Orig o
LEFT OUTER JOIN tab_COBMEMBR_New n
on (o.Name = n.Name) and (o.CountName = n.CountName)
WHERE n.NAME is NULL
ORDER BY o.Name, n.Name
;
ALTER TABLE tab_COBMEMBR_NoMatch
DROP COLUMN NCOBID,NName,NMemberNum, NMedPrimary, NSTARTDATE, NTERMDATE, NCountName
;
But I keep getting this error:

Server: Msg 207, Level 16, State 1, Line 80
Invalid column name 'NCOBID'.

I know it's something small, but I cannot for the life of me get past it....any suggestions?

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-31 : 17:21:53

You probably need some GOs in your script. If this script is for Query Analyzer, then add GO after any DDL change. So:

CREATE TABLE...
GO

ALTER TABLE...
GO

Tara

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-31 : 17:31:07

I didn't study your code but just looking quickly at what you're doing and the error you have it could be that:

tab_COBMEMBR_NoMatch exists currently without the NCOBID column. If your code drops and recreates the table with the NCOBID column included, then attempts to drop the column you could get that error because the at the time the plan is generated, the table exists without the column.

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-31 : 17:34:07

That's why you need the GOs.

Tara

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-31 : 17:36:48

I've got to learn to hit refresh before I post...That dang Tara always beats me to the punch ( and with a better answer) :)

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-31 : 17:38:00

Thanks....

I knew it was something small

....you hit the nail on the head, Tara!

Thanks for your input, too, TG.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources