Another Set of Eyes, Please...

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)

Another Set of Eyes, Please...

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 14:45:05

Here's a piece I'm working on to test a process. I'm getting errors,
but I can't see the forest for the trees after having stared at this for so long.

---WINNOWING
---
---Remove set Q from P leaving R.
---Select all K from R leaving Z
---
-----------------------------------------Big Table 'P'

use Fowler
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Fowler].[dbo].[xtab_P]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Fowler].[dbo].[xtab_P]
GO
CREATE TABLE xtab_P
(
MemHue varchar(6) NULL,
MemID varchar(4) NULL,
MemLoc varchar(2) NULL,
MemStat varchar(1) NULL
)
INSERT xtab_P VALUES('Orange','1505','OH','Y')
INSERT xtab_P VALUES('Silver','1918','IN','N')
INSERT xtab_P VALUES('Bleach','0208','WV','N')
INSERT xtab_P VALUES('Purple','1604','MO','Y')
INSERT xtab_P VALUES('Yellow','2325','WI','N')
INSERT xtab_P VALUES('Salmon','1914','WY','N')
INSERT xtab_P VALUES('Indigo','0915','MT','Y')
INSERT xtab_P VALUES('Maroon','1314','TX','N')
INSERT xtab_P VALUES('Sienna','1901','TN','Y')
INSERT xtab_P VALUES('Copper','0318','SD','N')
INSERT xtab_P VALUES('Auburn','0114','AZ','Y')
INSERT xtab_P VALUES('Bronze','0205','OK','Y')
INSERT xtab_P VALUES('Orchid','1504','MO','Y')
INSERT xtab_P VALUES('Chrome','0305','IN','Y')
INSERT xtab_P VALUES('Celery','0325','WV','N')
INSERT xtab_P VALUES('Cobalt','0320','MT','N')
INSERT xtab_P VALUES('Mauve ','1305','MT','N')
INSERT xtab_P VALUES('Taupe ','2005','NE','N')
GO

------------------------------Subset of 'P' called 'Q' (Y)
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Fowler].[dbo].[xtab_Q]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Fowler].[dbo].[xtab_Q]

CREATE TABLE xtab_Q
(
MemHue varchar(6) NULL,
MemID varchar(4) NULL,
MemLoc varchar(2) NULL,
MemStat varchar(1) NULL
)
GO
INSERT xtab_Q VALUES('Orange','1505','OH','Y')
INSERT xtab_Q VALUES('Purple','1604','MO','Y')
INSERT xtab_Q VALUES('Indigo','0915','MT','Y')
INSERT xtab_Q VALUES('Sienna','1901','TN','Y')
INSERT xtab_Q VALUES('Auburn','0114','AZ','Y')
INSERT xtab_Q VALUES('Bronze','0205','OK','Y')
INSERT xtab_Q VALUES('Orchid','1504','MO','Y')
INSERT xtab_Q VALUES('Chrome','0305','IN','Y')
GO

--------------------Subset of 'P' minus 'Q' called 'R' (N)
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Fowler].[dbo].[xtab_R]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Fowler].[dbo].[xtab_R]

------------------------------Independent table 'K'
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Fowler].[dbo].[xtab_K]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Fowler].[dbo].[xtab_K]

CREATE TABLE xtab_K
(
MemHue varchar(6) NULL,
MemID varchar(4) NULL,
MemLoc varchar(2) NULL,
MemStat varchar(1) NULL
)
GO

INSERT xtab_K VALUES('Silver','1918','IN','N')
INSERT xtab_K VALUES('Bleach','0208','WV','N')
INSERT xtab_K VALUES('Yellow','2325','WI','N')
INSERT xtab_K VALUES('Salmon','1914','WY','N')
INSERT xtab_K VALUES('Maroon','1314','TX','N')
INSERT xtab_K VALUES('Copper','0318','SD','N')
INSERT xtab_K VALUES('Celery','0325','WV','N')
INSERT xtab_K VALUES('Cobalt','0320','MT','N')
GO

------------------------------'R' minus 'K' called 'Z'
if exists
(select * from Fowler.dbo.sysobjects
where id = object_id(N'[Fowler].[dbo].[xtab_Z') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Fowler].[dbo].[xtab_Z]

SELECT
xtab_P=(SELECT Count(*) FROM xtab_P),
xtab_Q=(SELECT Count(*) FROM xtab_Q),
xtab_K=(SELECT Count(*) FROM xtab_K),

SELECT p.MemHue as pMemHue, p.MemID as pMemID, p.MemLoc as pMemLoc, p.MemStat as pMemStat,
q.MemHue as qMemHue, q.MemID as qMemID, q.MemLoc as qMemLoc, q.MemStat as qMemStat
INTO xtab_R
FROM xtab_P p
LEFT OUTER JOIN xtab_Q q on
(
(p.MemHue = q.MemHue) and
(p.MemID = q.MemID ) and
(p.MemLoc = q.MemLoc) and
(p.MemStat = q.MemStat)
)
where q.MemHue is NULL

SELECT xtab_R=(SELECT Count(*) FROM xtab_R)

ALTER TABLE xtab_R
DROP COLUMN qMemHue, qMemID, qMemLoc, qMemStat

SELECT * FROM xtab_R

SELECT pMemHue, pMemID, pMemLoc, pMemStat,
k.MemHue as kMemHue, k.MemID as kMemID, k.MemLoc as kMemLoc, k.MemStat as kMemStat

INTO xtab_Z
FROM xtab_R r
LEFT OUTER JOIN xtab_K k on
(
(pMemHue = k.MemHue) and
(pMemID = k.MemID ) and
(pMemLoc = k.MemLoc) and
(pMemStat = k.MemStat)
)

WHERE k.MemHue IS NOT NULL

SELECT xtab_Z=(SELECT Count(*) FROM xtab_Z)

.....sometimes I get stuck

and I end up digging a deeper hole. Please let me know what you find. Thanks...

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 14:53:58

It was a stupid comma:

xtab_K=(SELECT Count(*) FROM xtab_K),

--once removed, it worked. Sorry.

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 15:06:02

quote:
Originally posted by Xerxes

It was a stupid comma:

xtab_K=(SELECT Count(*) FROM xtab_K),

--once removed, it worked. Sorry.

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

if you remove the comma in red it should work:
xtab_K=(SELECT Count(*) FROM xtab_K),

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 15:08:44

Corey! ---

I know that already

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 15:10:34

Thanks!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 15:15:11

quote:
Originally posted by Seventhnight

quote:
Originally posted by Xerxes

It was a stupid comma:

xtab_K=(SELECT Count(*) FROM xtab_K),

--once removed, it worked. Sorry.

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

if you remove the comma in red it should work:
xtab_K=(SELECT Count(*) FROM xtab_K),

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Actually, Corey, I'm not getting the result I want

: Z is supposed to be the results of what didn't match between R & K. Do you know what I'm doing wrong here?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 15:30:30

if its what didn't match then:

WHERE k.MemHue IS NOT NULL
should be
WHERE k.MemHue IS ~~NOT~~ NULL

EDIT: Oh and I wasn't wrong earlier, the query did work

. It just didn't do what you expected it to do

.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 16:56:45

No kidding! Thanks for the laugh!

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-18 : 16:58:40

There ARE no stupid commas...only ex-marines. ;)

sorry Xerxes, couldn't resist.

BTW, looks like I overtook you in post counts. You better get busy, man.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-18 : 17:05:39

Wow, TG, 205 posts! You'll probably a SQL Warrior QUEEN

in No time! (ROTFL)

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-18 : 17:15:28

hahahahhaha, good one!
Well, at least I got the legs for it.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources