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 GOcreate view dbo.vALL_CHARGE_ALLOW asSELECT 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 statusFROM [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_KEYwhere EMP_CODE = 'ROBR'GOdrop table dbo.tALL_CHARGE_ALLOWGOselect * into dbo.tALL_CHARGE_ALLOW from dbo.vALL_CHARGE_ALLOWGOselect * from dbo.tALL_CHARGE_ALLOW I get 3 records, but when I dodrop view dbo.vALL_CHARGE_ALLOW GOcreate view dbo.vALL_CHARGE_ALLOW asSELECT 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 statusFROM [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_KEYGOdrop table dbo.tALL_CHARGE_ALLOWGOselect * into dbo.tALL_CHARGE_ALLOW from dbo.vALL_CHARGE_ALLOWGOselect * 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 beselect * 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 beselect * from dbo.tALL_CHARGE_ALLOW where empcode = 'ROBR'
no - check the creation of the viewselect ... 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 2What 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.-------------------------------------------------------------- |
|
|
|