Huh? What's going on - SQL Server Forums

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)

Huh? What's going on

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-02 : 21:47:29

OK gurus (it has to be something obvious)...

When I do


drop view dbo.vALL_CHARGE_ALLOW 
GO

create view dbo.vALL_CHARGE_ALLOW as
SELECT     PRJ_NAME AS project, EMP_CODE AS namecode, FTD_VALUE1 AS WorkPackage, CHR_DESC AS description, CHR_FLD1 AS manager, 
                      startdate = CASE WHEN CHR_SDATE IS NULL OR
                      CHR_SDATE = '' THEN NULL ELSE CONVERT(datetime, CHR_SDATE, 113) END, enddate = CASE WHEN CHR_FDATE IS NULL OR
                      CHR_FDATE = '' THEN NULL ELSE CONVERT(datetime, CHR_FDATE, 113) END, CHR_STATUS AS status
FROM         [COUNT].timectrl.dbo.FTHEADER INNER JOIN
                      [COUNT].timectrl.dbo.FTDETAIL ON FTH_KEY = FTD_FTH INNER JOIN
                      [COUNT].timectrl.dbo.EMPLOYEE ON FTH_DESC LIKE EMP_CODE + '%' INNER JOIN
                      [COUNT].timectrl.dbo.CHARGE ON CHR_CODE = FTD_VALUE1 INNER JOIN
                      [COUNT].timectrl.dbo.TCPROJ ON CHR_PRJ = PRJ_KEY
where EMP_CODE = 'ROBR'
GO

drop table dbo.tALL_CHARGE_ALLOW
GO

select * 
into dbo.tALL_CHARGE_ALLOW 
from dbo.vALL_CHARGE_ALLOW
GO

select * from dbo.tALL_CHARGE_ALLOW

I get 3 records, but when I do


drop view dbo.vALL_CHARGE_ALLOW 
GO

create view dbo.vALL_CHARGE_ALLOW as
SELECT     PRJ_NAME AS project, EMP_CODE AS namecode, FTD_VALUE1 AS WorkPackage, CHR_DESC AS description, CHR_FLD1 AS manager, 
                      startdate = CASE WHEN CHR_SDATE IS NULL OR
                      CHR_SDATE = '' THEN NULL ELSE CONVERT(datetime, CHR_SDATE, 113) END, enddate = CASE WHEN CHR_FDATE IS NULL OR
                      CHR_FDATE = '' THEN NULL ELSE CONVERT(datetime, CHR_FDATE, 113) END, CHR_STATUS AS status
FROM         [COUNT].timectrl.dbo.FTHEADER INNER JOIN
                      [COUNT].timectrl.dbo.FTDETAIL ON FTH_KEY = FTD_FTH INNER JOIN
                      [COUNT].timectrl.dbo.EMPLOYEE ON FTH_DESC LIKE EMP_CODE + '%' INNER JOIN
                      [COUNT].timectrl.dbo.CHARGE ON CHR_CODE = FTD_VALUE1 INNER JOIN
                      [COUNT].timectrl.dbo.TCPROJ ON CHR_PRJ = PRJ_KEY
GO

drop table dbo.tALL_CHARGE_ALLOW
GO

select * 
into dbo.tALL_CHARGE_ALLOW 
from dbo.vALL_CHARGE_ALLOW
GO

select * from dbo.tALL_CHARGE_ALLOW where namecode = 'ROBR'

I get none.

I know I'm dumb - but I thought they would return the same thing!

Edited by - rrb on 05/02/2002 21:54:12

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-03 : 15:33:44

You got me. But here are some questions that might help troubleshoot it:

In the second example, after doing the select into, are there *any* records in tALL_CHARGE_ALLOW? Can you take a quick look and see if there's some pattern to what's missing?

What is the columntype of EMP_CODE?

Cheers
-b

skond
Yak Posting Veteran

55 Posts

Posted - 2002-05-03 : 16:24:51

shouldn't the last query be

select * from dbo.tALL_CHARGE_ALLOW where empcode = 'ROBR'

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-08 : 03:01:02

quote:

shouldn't the last query be

select * from dbo.tALL_CHARGE_ALLOW where empcode = 'ROBR'

no - check the creation of the view

select ... EMP_CODE as namecode ...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-08 : 03:03:10

quote:

In the second example, after doing the select into, are there *any* records in tALL_CHARGE_ALLOW? Can you take a quick look and see if there's some pattern to what's missing?

Yes, 572 rows.

and the type is varchar (10)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-05-09 : 01:40:14

select distinct namecode from dbo.tALL_CHARGE_ALLOW

does this has ROBR in it?

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-09 : 01:48:29

Yes - I get 3 records in case 1

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-05-09 : 04:06:22

No , i meant in Case 2

What happens if you run both the query's directly in Query Analyser ? .

Just trying to pick some threads...

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-09 : 04:15:22

Sorry Nazim,

I've given up and reorganised things another way which works.

Thanks for your help - sorry for wasting your time.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-05-09 : 09:35:39

Rob can you post me the ddl and some sample data .

Am curious about this.

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources