Inconsistent query result

Hi, I have one view and three tables. My query is like this
select * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
It wil return only 6 records.
If I do this
select * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
or this
select * from VW_I_FACT_SERVICE
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
or this
select * from VW_I_FACT_SERVICE
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
They will return 248407 records for each query.
----
And If I do this
select top 100000 * from VW_I_FACT_SERVICE
inner join DIM_CUSTOMER
on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
inner join DIM_PERIOD
on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
inner join DIM_AREA
on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
It will return exactly 100000 records.
----
I really do not have any idea why my first query only returns 6 records?
I'm using a Pentium 4 2.8GHz PC with 1GB of RAM. Windows 2000 Advanced
Server SP4, SQL Server 2000 Enterprise Edition SP3a.
Thank You in Advance...
Regards,
Jono Indrawijaya
PT eBiz Cipta Solusi - Indonesia
Microsoft Certified PartnerJono,
Certainly adding TOP 100000 should not increase the size of
the result set from 6 to 100000. First, can you run
SELECT @.@.version
to identify which build of SQL Server you are using? Also, could you
post the CREATE TABLE and CREATE VIEW statements for these
tables and view?
Steve Kass
Drew University
Jono Indrawijaya wrote:

>Hi, I have one view and three tables. My query is like this
>select * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>It wil return only 6 records.
>--
>If I do this
>select * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>or this
>select * from VW_I_FACT_SERVICE
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>or this
>select * from VW_I_FACT_SERVICE
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>
>They will return 248407 records for each query.
>----
>And If I do this
>select top 100000 * from VW_I_FACT_SERVICE
>inner join DIM_CUSTOMER
>on VW_I_FACT_SERVICE.Customer_ID = DIM_CUSTOMER.Customer_ID
>inner join DIM_PERIOD
>on VW_I_FACT_SERVICE.Time_Period_ID = DIM_PERIOD.Time_ID
>inner join DIM_AREA
>on VW_I_FACT_SERVICE.Area_ID = DIM_AREA.Area_ID
>It will return exactly 100000 records.
>----
>I really do not have any idea why my first query only returns 6 records?
>I'm using a Pentium 4 2.8GHz PC with 1GB of RAM. Windows 2000 Advanced
>Server SP4, SQL Server 2000 Enterprise Edition SP3a.
>Thank You in Advance...
>
>|||Dear Steve,
I found that the Customer_ID field in FACT_CSUTOMER is bigint, but in the
I_FACT_SERVICE is int. Now it is returning correct recordset after I change
those data type. Thx for u're response, it leads me to this finding.. =)
Regards,
Jono
"Steve Kass" wrote:

> Jono,
> Certainly adding TOP 100000 should not increase the size of
> the result set from 6 to 100000. First, can you run
> SELECT @.@.version
> to identify which build of SQL Server you are using? Also, could you
> post the CREATE TABLE and CREATE VIEW statements for these
> tables and view?
> Steve Kass
> Drew University
> Jono Indrawijaya wrote:
>
>