Whats wrong with my sql statement

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

Transact-SQL (2000)

Whats wrong with my sql statement

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2002-10-09 : 00:41:58

can anyone tell me whats wrong with this. It will not work. When i change fac_no# in the provider_info table it will not give it back to me with this statement. However if i reverse
the statement the center_stage will give me the correct info. What gives

select fac_no#,active from provider_info
where fac_no# not in (select fac_no# from center_stage)

heres the table

CREATE TABLE [center_stage] (
[STATE_NUM] [int] NULL ,
[REGION_NUM] [int] NULL ,
[FAC_No#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CNTY_NUM] [int] NULL ,
[TYPE_CD] [int] NULL ,
[FAC_ADRSS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_CTY] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ST] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ZIP1] [int] NULL ,
[FAC_LIC_NM] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_LIC_TYPE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ADMIN_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAX_CAP] [int] NULL ,
[CLIENT_TYPE] [int] NULL ,
[FAC_TELE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [provider_info] (
[LID] [int] IDENTITY (1, 1) NOT NULL ,
[ACTIVE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_LIC_NM] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAX_CAP] [int] NULL ,
[FAC_NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ADRSS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_CTY] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ST] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ZIP1] [int] NULL ,
[STATE_NUM] [int] NULL ,
[REGION_NUM] [int] NULL ,
[FAC_No#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CNTY_NUM] [int] NULL ,
[TYPE_CD] [int] NULL ,
[FAC_LIC_TYPE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAC_ADMIN_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLIENT_TYPE] [int] NULL ,
[FAC_TELE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[R&R_NO] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Disable] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dis_Date] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__STUFF__3B75D760] PRIMARY KEY CLUSTERED
(
[LID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-09 : 04:31:10

Not sure what your question is but

Do you have a null in fac_no# in center_stage? If so you won't get anything returned (maybe depending on version).

Try
select fac_no#,active from provider_info
where fac_no# not in (select fac_no# from center_stage where fac_no# is not null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

csphard
Posting Yak Master

113 Posts

Posted - 2002-10-09 : 11:45:06

quote:

thank you. You were right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources