August 10, 2006 at 11:36 am
The insert is inserting 0 rows, when it should be about 500.
I've narrowed it down to the issue to the following statement
--And isnull(Hedonic_Value, 0) <> 0
The column is all null and I'm trying to make it 0, but this is causing my query to return 0 rows.
Any help would be greatly appreciated.
Thanks
Susan
--Insert into HedonicSQFTCensusBlock
Select sum(cast(Hedonic_Value as bigint)) as SumHedonic, sum(cast(sa_sqft as bigint)) as sa_sqft,
sa_census_tract, sa_census_block_group, count(*) as totalHomes, 'B' as HouseType
From ReplyAVM
Where isnull(sa_sqft, 0) <> 0
--And isnull(Hedonic_Value, 0) <>0
And use_code_std in ('RDUP', 'RQUA', 'RTRI')
Group by sa_census_tract, sa_census_block_group
--Tables used
CREATE TABLE [dbo].[ReplyAVM](
[sa_property_id] [bigint] NOT NULL,
[SA_LOTSIZE] [int] NULL,
[SA_SQFT] [int] NULL,
[SA_DATE_TRANSFER] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_VAL_TRANSFER] [int] NULL,
[Replypct] [float] NULL,
[ReplyAvg] [float] NULL,
[ReplyPctWeight] [int] NULL,
[ReplyAvgWeight] [int] NULL,
[HEDONIC_VALUE] [int] NULL,
[ASSESSED_VALUE] [int] NULL,
[RSI_VALUE] [int] NULL,
[APPR_EMUL_VALUE] [int] NULL,
[AVM_VALUE] [int] NULL,
[ReplyFiserv] [int] NULL,
[ReplyAVMSQFT] [int] NULL,
[ReplyAVMLotSize] [int] NULL,
[ReplyRSISQFT] [int] NULL,
[ReplyRSILotSize] [int] NULL,
[ReplyHedonicLotSize] [int] NULL,
[ReplyHedonicSQFT] [int] NULL,
[SA_SITE_ZIP] [int] NULL,
[SA_SITE_City] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Longitude] [float] NULL,
[Latitude] [float] NULL,
[SA_CENSUS_TRACT] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_CENSUS_BLOCK_GROUP] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReplyAssessedSQFT] [int] NULL,
[ReplyAssessedLotSize] [int] NULL,
[USE_CODE_STD] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReplySoldSQFT] [int] NULL,
[ReplySoldLotSize] [int] NULL,
[ReplyAppr_EmulSQFT] [int] NULL,
[ReplyAppr_EmulLotSize] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--Data SAmple of rows in question
SumHedonic sa_sqft sa_census_tract sa_census_block_group totalHomes HouseType
-------------------- -------------------- --------------- --------------------- ----------- ---------
NULL 1320 113600 3 1 B
NULL 13882 110400 6 4 B
NULL 10503 113510 1 5 B
NULL 27391 101800 1 15 B
NULL 5523 110200 1 2 B
NULL 88891 104000 1 31 B
NULL 4000 113517 1 2 B
NULL 2032 110900 3 1 B
NULL 53566 002500 1 21 B
NULL 77224 111700 6 36 B
NULL 58689 102700 2 35 B
NULL 4480 000100 4 3 B
NULL 3235 000902 2 2 B
NULL 9529 104600 2 5 B
NULL 13712 000100 5 7 B
NULL 3876 001502 4 1 B
NULL 6948 113005 1 3 B
NULL 75097 101000 2 36 B
NULL 16523 100500 2 8 B
NULL 56957 112606 1 19 B
NULL 45609 102000 2 24 B
NULL 3766 113006 2 2 B
NULL 46312 111400 6 26 B
NULL 53176 000702 2 22 B
NULL 13810 003400 2 6 B
NULL 50668 112402 1 27 B
NULL 48796 000500 3 19 B
NULL 7321 101900 2 3 B
NULL 13226 003100 2 5 B
NULL 50297 112502 4 22 B
NULL 37998 100800 1 13 B
NULL 33765 003100 8 13 B
NULL 9170 110103 1 4 B
NULL 4660 112201 3 2 B
NULL 21272 112000 5 9 B
NULL 30740 110400 1 12 B
NULL 15916 000702 4 6 B
NULL 1890 111600 6 1 B
NULL 52418 112501 3 23 B
NULL 11261 112000 4 5 B
August 10, 2006 at 11:44 am
>>The column is all null and I'm trying to make it 0,
So all 500 rows contain a NULL in this column ? And you're converting the NULL to zero ? Fair enough.
>>And isnull(Hedonic_Value, 0) <> 0
But then you add a clause that only selects rows where not equal to zero ?
If they're all NULL. And you convert them ALL to zero. Then obviously you'll select zero rows if you're explicitly excluding all zeroes.
August 11, 2006 at 5:31 am
Are you maybe trying to do this?
Select sum(cast(ISNULL(Hedonic_Value,0) as bigint)) as SumHedonic, sum(cast(sa_sqft as bigint)) as sa_sqft,
sa_census_tract, sa_census_block_group, count(*) as totalHomes, 'B' as HouseType
From ReplyAVM
Where isnull(sa_sqft, 0) <> 0
I'm not really sure what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply