SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Huh? What's going on
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/02/2002 :  21:47:29  Show Profile  Reply with Quote
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

USA
525 Posts

Posted - 05/03/2002 :  15:33:44  Show Profile  Send aiken an ICQ Message  Reply with Quote
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

Go to Top of Page

skond
Yak Posting Veteran

USA
55 Posts

Posted - 05/03/2002 :  16:24:51  Show Profile  Reply with Quote
shouldn't the last query be

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


Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/08/2002 :  03:01:02  Show Profile  Reply with Quote
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"
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/08/2002 :  03:03:10  Show Profile  Reply with Quote
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"
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 05/09/2002 :  01:40:14  Show Profile  Reply with Quote
select distinct namecode from dbo.tALL_CHARGE_ALLOW

does this has ROBR in it?



--------------------------------------------------------------
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/09/2002 :  01:48:29  Show Profile  Reply with Quote
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"
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 05/09/2002 :  04:06:22  Show Profile  Reply with Quote
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...

--------------------------------------------------------------
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 05/09/2002 :  04:15:22  Show Profile  Reply with Quote
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"
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 05/09/2002 :  09:35:39  Show Profile  Reply with Quote
Rob can you post me the ddl and some sample data .

Am curious about this.



--------------------------------------------------------------
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000