| Author |
 Topic  |
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
 Posted - 05/02/2002 : 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
USA
525 Posts |
Posted - 05/03/2002 : 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
USA
55 Posts |
Posted - 05/03/2002 : 16:24:51
|
shouldn't the last query be
select * from dbo.tALL_CHARGE_ALLOW where empcode = 'ROBR'
|
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 05/08/2002 : 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
Australia
1478 Posts |
Posted - 05/08/2002 : 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
United Arab Emirates
1408 Posts |
Posted - 05/09/2002 : 01:40:14
|
select distinct namecode from dbo.tALL_CHARGE_ALLOW
does this has ROBR in it?
--------------------------------------------------------------
|
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 05/09/2002 : 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
United Arab Emirates
1408 Posts |
Posted - 05/09/2002 : 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
Australia
1478 Posts |
Posted - 05/09/2002 : 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
United Arab Emirates
1408 Posts |
Posted - 05/09/2002 : 09:35:39
|
Rob can you post me the ddl and some sample data .
Am curious about this.
--------------------------------------------------------------
|
|
|
| |
Topic |
|
Huh? What's going on
