Different Query Results in SQL Server 7.0 and 2000

I recently migrated my databases to a box with SQL Server
2000. This query (which is actually a view) returns the
right results in 7, but in 2000, the [Dockdate & Time]
and [Variance REC-DOCK hours] fields always return NULL.
I have narrowed the problem down to the WHERE clause, but
can't figure out how to resolve it. I have tried
replacing WHERE with AND, which *allows* the fields to
return non-NULL values, but the query retrieves over 1000
records instead of the 42 that it is supposed to.
Please Help!
SELECT DISTINCT
DDRD.REC_ID,
DDRD.TYPE,
DDRD.STATUS,
DDRD.BRANCH,
DDRD.CREATE_DATE,
DDRD.DOCK_DATE,
DD.[Dockdate & Time],
MIN(T.TRANSACTION_DATE) AS [Manifest Open],
DDRD.RECEIVE_DATE, --CONVERT(varchar,
ddrd.RECEIVE_DATE, 110),
CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
(DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
[Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
DDRD.SumOfRECEIVED_QTY,
DDRD.ER_NO
FROM DockDates.dbo.[Dock Dates Table] DD
RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
ON (DD.REC_ID = DDRD.REC_ID)
LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
(DDRD.REC_ID = T.REC_ID)
WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
2004')
GROUP BY DDRD.REC_ID,
DDRD.TYPE,
DDRD.STATUS,
DDRD.BRANCH,
DDRD.CREATE_DATE,
DDRD.DOCK_DATE,
DD.[Dockdate & Time],
DDRD.RECEIVE_DATE,
DDRD.[VAR_REC-DOCKhrs],
DDRD.SumOfRECEIVED_QTY,
DDRD.ER_NO
ORDER BY DDRD.REC_IDBecky,
1. what is wrong in your results? Is it the number of records returned, or
the values returned for [Dockdate & Time] and [Variance REC-DOCK hours], or
both?
2. can you provide DDL for the tables?
3. Do you have the same collation in both installation?
Quentin
"Becky Bowen" <anonymous@.discussions.microsoft.com> wrote in message
news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
> I recently migrated my databases to a box with SQL Server
> 2000. This query (which is actually a view) returns the
> right results in 7, but in 2000, the [Dockdate & Time]
> and [Variance REC-DOCK hours] fields always return NULL.
> I have narrowed the problem down to the WHERE clause, but
> can't figure out how to resolve it. I have tried
> replacing WHERE with AND, which *allows* the fields to
> return non-NULL values, but the query retrieves over 1000
> records instead of the 42 that it is supposed to.
> Please Help!
>
> SELECT DISTINCT
> DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> ddrd.RECEIVE_DATE, 110),
> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> FROM DockDates.dbo.[Dock Dates Table] DD
> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> ON (DD.REC_ID = DDRD.REC_ID)
> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> (DDRD.REC_ID = T.REC_ID)
> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
> 2004')
> GROUP BY DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> DDRD.RECEIVE_DATE,
> DDRD.[VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> ORDER BY DDRD.REC_ID|||Thanks in advance for your help!!
>--Original Message--
>Becky,
>1. what is wrong in your results? Is it the number of
records returned, or
**Wrong number of records using AND
**Wrong results (Null fields) using WHERE
>the values returned for [Dockdate & Time] and [Variance
REC-DOCK hours], or
>both?
**Both
>2. can you provide DDL for the tables?
** Alright...you asked for it...
CREATE TABLE [dbo].[TRANSACTION] (
[TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
[TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
[TRANSACTION_DATE] [datetime] NULL ,
[SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
[DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
[PRODUCT_ID] [varchar] (40) NULL ,
[PERFORMED_BY] [varchar] (30) NULL ,
[ORDER_ID] [varchar] (30) NULL ,
[ORDER_TYPE] [varchar] (2) NULL ,
[ORDER_LINE_NO] [decimal](9, 0) NULL ,
[EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
[EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
[ERD_LINE_NO] [decimal](9, 0) NULL ,
[REC_ID] [decimal](9, 0) NULL ,
[RECEIVER_TYPE] [varchar] (3) NULL ,
[TASK_ID] [decimal](9, 0) NULL ,
[SOURCE_LOCATION_NO] [varchar] (20) NULL ,
[DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
[DROP_LOCATION_NO] [varchar] (20) NULL ,
[EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
[ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
[EXPECTED_UOM] [decimal](2, 0) NULL ,
[ACTUAL_UOM] [decimal](2, 0) NULL ,
[UOM_FAMILY] [decimal](1, 0) NULL ,
[OLD_MSC] [varchar] (3) NULL ,
[NEW_MSC] [varchar] (3) NULL ,
[OLD_MKR] [varchar] (20) NULL ,
[NEW_MKR] [varchar] (20) NULL ,
[INVENTORY_ID] [decimal](9, 0) NULL ,
[PRODUCT_KEY] [varchar] (250) NULL ,
[OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
[NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
[HOLD_IND] [varchar] (1) NULL ,
[REASON_CODE] [varchar] (20) NULL ,
[ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
[RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
[DD_INSTANCE_ID] [decimal](9, 0) NULL ,
[UPLOAD_FILE_NAME] [varchar] (30) NULL ,
[UPLOAD_IND] [varchar] (1) NULL ,
[LOGGING_SOURCE] [varchar] (80) NULL ,
[REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
[REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
[SUCCESS_IND] [varchar] (1) NULL ,
[HOST_REFERENCE] [varchar] (40) NULL ,
[EXPIRY_DATE] [datetime] NULL ,
[LOT_ID] [varchar] (20) NULL ,
[BRANCH] [varchar] (20) NULL ,
[COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
[VENDOR_ID] [varchar] (20) NULL ,
[MANUFACTURING_DATE] [datetime] NULL ,
[ATTRIBUTE1] [varchar] (20) NULL ,
[ATTRIBUTE2] [varchar] (20) NULL ,
[ATTRIBUTE3] [varchar] (20) NULL ,
[ATTRIBUTE4] [varchar] (20) NULL ,
[ATTRIBUTE5] [varchar] (20) NULL ,
[ATTRIBUTE6] [varchar] (20) NULL ,
[ATTRIBUTE7] [varchar] (20) NULL ,
[ATTRIBUTE8] [varchar] (20) NULL ,
[ATTRIBUTE9] [varchar] (20) NULL ,
[ATTRIBUTE10] [varchar] (20) NULL ,
[ATTRIBUTE11] [varchar] (20) NULL ,
[ATTRIBUTE12] [varchar] (20) NULL ,
[ATTRIBUTE13] [varchar] (20) NULL ,
[ATTRIBUTE14] [varchar] (20) NULL ,
[ATTRIBUTE15] [varchar] (20) NULL ,
[ATTRIBUTE16] [varchar] (20) NULL ,
[ATTRIBUTE17] [varchar] (20) NULL ,
[ATTRIBUTE18] [varchar] (20) NULL ,
[ATTRIBUTE19] [varchar] (20) NULL ,
[ATTRIBUTE20] [varchar] (20) NULL ,
[REC_LINE_NO] [decimal](9, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dock Dates Table] (
[REC_ID] [int] NOT NULL ,
[Dockdate & Time] [smalldatetime] NULL ,
[Comments] [varchar] (255) NULL ,
[UsrID] [varchar] (10) NULL ,
[LastModUsrID] [varchar] (10) NULL ,
[LastModDate] [datetime] NULL ,
[InputDate] [datetime] NULL
) ON [PRIMARY]
GO
--
SELECT DISTINCT
R.REC_ID, R.TYPE, R.STATUS,
RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
R.RECEIVE_DATE,
R.RECEIVE_DATE - ER.ERHE_GD_01 AS
[VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
SumOfRECEIVED_QTY, ER.ER_NO
FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
MOVEPROD.dbo.RECEIVER_DETAIL RD ON
R.REC_ID = RD.REC_ID LEFT OUTER JOIN
MOVEPROD.dbo.EXPECTED_RECEIPT ER ON
R.ER_ID = ER.ER_ID
WHERE (R.STATUS = 'CLO')
GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
R.RECEIVE_DATE - ER.ERHE_GD_01
>3. Do you have the same collation in both installation?
**2000--SQL_Latin1_General_CP1_CI_AS
**7.0--'
>Quentin
>"Becky Bowen" <anonymous@.discussions.microsoft.com>
wrote in message
>news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
>> I recently migrated my databases to a box with SQL
Server
>> 2000. This query (which is actually a view) returns
the
>> right results in 7, but in 2000, the [Dockdate & Time]
>> and [Variance REC-DOCK hours] fields always return
NULL.
>> I have narrowed the problem down to the WHERE clause,
but
>> can't figure out how to resolve it. I have tried
>> replacing WHERE with AND, which *allows* the fields to
>> return non-NULL values, but the query retrieves over
1000
>> records instead of the 42 that it is supposed to.
>> Please Help!
>>
>> SELECT DISTINCT
>> DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
>> DDRD.RECEIVE_DATE, --CONVERT(varchar,
>> ddrd.RECEIVE_DATE, 110),
>> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
>> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
>> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
>> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
>> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>> FROM DockDates.dbo.[Dock Dates Table] DD
>> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
>> ON (DD.REC_ID = DDRD.REC_ID)
>> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
>> (DDRD.REC_ID = T.REC_ID)
>> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-
12-
>> 2004')
>> GROUP BY DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> DDRD.RECEIVE_DATE,
>> DDRD.[VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>> ORDER BY DDRD.REC_ID
>
>.
>|||1. Depending on your null setting, changing from where to and can indeed
cause change of the query. I believe I saw other situations, but don't
remember which exactly. Anyway, AND can give different query than WHERE.
2. It is likely that you have the same collation between the two
servers/db/table column (remember that in SS2K you can set collation down to
column) since the one you give for 2K is the same as SS7 default. but a
verification would help, also verify in SS2K there is no change from
collation default.
3. [Variance REC-DOCK hours] is derived from ... which is derived from ...
Some where the chain broke in your DDL. I am not saying that that was the
cause since I can not explain why [Dockdate & Time] also became null. But
you may want to follow back with those ones that are correct in SS7 but
wrong in 2K step by step.
Quentin
"Becky" <anonymous@.discussions.microsoft.com> wrote in message
news:02ed01c3daea$de02be30$a501280a@.phx.gbl...
> Thanks in advance for your help!!
> >--Original Message--
> >Becky,
> >
> >1. what is wrong in your results? Is it the number of
> records returned, or
> **Wrong number of records using AND
> **Wrong results (Null fields) using WHERE
> >the values returned for [Dockdate & Time] and [Variance
> REC-DOCK hours], or
> >both?
> **Both
> >2. can you provide DDL for the tables?
> ** Alright...you asked for it...
> CREATE TABLE [dbo].[TRANSACTION] (
> [TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
> [TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
> [TRANSACTION_DATE] [datetime] NULL ,
> [SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
> [DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
> [PRODUCT_ID] [varchar] (40) NULL ,
> [PERFORMED_BY] [varchar] (30) NULL ,
> [ORDER_ID] [varchar] (30) NULL ,
> [ORDER_TYPE] [varchar] (2) NULL ,
> [ORDER_LINE_NO] [decimal](9, 0) NULL ,
> [EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
> [EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
> [ERD_LINE_NO] [decimal](9, 0) NULL ,
> [REC_ID] [decimal](9, 0) NULL ,
> [RECEIVER_TYPE] [varchar] (3) NULL ,
> [TASK_ID] [decimal](9, 0) NULL ,
> [SOURCE_LOCATION_NO] [varchar] (20) NULL ,
> [DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
> [DROP_LOCATION_NO] [varchar] (20) NULL ,
> [EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
> [ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
> [EXPECTED_UOM] [decimal](2, 0) NULL ,
> [ACTUAL_UOM] [decimal](2, 0) NULL ,
> [UOM_FAMILY] [decimal](1, 0) NULL ,
> [OLD_MSC] [varchar] (3) NULL ,
> [NEW_MSC] [varchar] (3) NULL ,
> [OLD_MKR] [varchar] (20) NULL ,
> [NEW_MKR] [varchar] (20) NULL ,
> [INVENTORY_ID] [decimal](9, 0) NULL ,
> [PRODUCT_KEY] [varchar] (250) NULL ,
> [OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
> [NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
> [HOLD_IND] [varchar] (1) NULL ,
> [REASON_CODE] [varchar] (20) NULL ,
> [ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
> [RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
> [DD_INSTANCE_ID] [decimal](9, 0) NULL ,
> [UPLOAD_FILE_NAME] [varchar] (30) NULL ,
> [UPLOAD_IND] [varchar] (1) NULL ,
> [LOGGING_SOURCE] [varchar] (80) NULL ,
> [REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
> [REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
> [SUCCESS_IND] [varchar] (1) NULL ,
> [HOST_REFERENCE] [varchar] (40) NULL ,
> [EXPIRY_DATE] [datetime] NULL ,
> [LOT_ID] [varchar] (20) NULL ,
> [BRANCH] [varchar] (20) NULL ,
> [COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
> [VENDOR_ID] [varchar] (20) NULL ,
> [MANUFACTURING_DATE] [datetime] NULL ,
> [ATTRIBUTE1] [varchar] (20) NULL ,
> [ATTRIBUTE2] [varchar] (20) NULL ,
> [ATTRIBUTE3] [varchar] (20) NULL ,
> [ATTRIBUTE4] [varchar] (20) NULL ,
> [ATTRIBUTE5] [varchar] (20) NULL ,
> [ATTRIBUTE6] [varchar] (20) NULL ,
> [ATTRIBUTE7] [varchar] (20) NULL ,
> [ATTRIBUTE8] [varchar] (20) NULL ,
> [ATTRIBUTE9] [varchar] (20) NULL ,
> [ATTRIBUTE10] [varchar] (20) NULL ,
> [ATTRIBUTE11] [varchar] (20) NULL ,
> [ATTRIBUTE12] [varchar] (20) NULL ,
> [ATTRIBUTE13] [varchar] (20) NULL ,
> [ATTRIBUTE14] [varchar] (20) NULL ,
> [ATTRIBUTE15] [varchar] (20) NULL ,
> [ATTRIBUTE16] [varchar] (20) NULL ,
> [ATTRIBUTE17] [varchar] (20) NULL ,
> [ATTRIBUTE18] [varchar] (20) NULL ,
> [ATTRIBUTE19] [varchar] (20) NULL ,
> [ATTRIBUTE20] [varchar] (20) NULL ,
> [REC_LINE_NO] [decimal](9, 0) NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Dock Dates Table] (
> [REC_ID] [int] NOT NULL ,
> [Dockdate & Time] [smalldatetime] NULL ,
> [Comments] [varchar] (255) NULL ,
> [UsrID] [varchar] (10) NULL ,
> [LastModUsrID] [varchar] (10) NULL ,
> [LastModDate] [datetime] NULL ,
> [InputDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> --
> SELECT DISTINCT
> R.REC_ID, R.TYPE, R.STATUS,
> RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
> R.RECEIVE_DATE,
> R.RECEIVE_DATE - ER.ERHE_GD_01 AS
> [VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
> SumOfRECEIVED_QTY, ER.ER_NO
> FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
> MOVEPROD.dbo.RECEIVER_DETAIL RD ON
> R.REC_ID = RD.REC_ID LEFT OUTER JOIN
> MOVEPROD.dbo.EXPECTED_RECEIPT ER ON
> R.ER_ID = ER.ER_ID
> WHERE (R.STATUS = 'CLO')
> GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
> R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
> R.RECEIVE_DATE - ER.ERHE_GD_01
>
> >3. Do you have the same collation in both installation?
> **2000--SQL_Latin1_General_CP1_CI_AS
> **7.0--'
> >
> >Quentin
> >
> >"Becky Bowen" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
> >> I recently migrated my databases to a box with SQL
> Server
> >> 2000. This query (which is actually a view) returns
> the
> >> right results in 7, but in 2000, the [Dockdate & Time]
> >> and [Variance REC-DOCK hours] fields always return
> NULL.
> >> I have narrowed the problem down to the WHERE clause,
> but
> >> can't figure out how to resolve it. I have tried
> >> replacing WHERE with AND, which *allows* the fields to
> >> return non-NULL values, but the query retrieves over
> 1000
> >> records instead of the 42 that it is supposed to.
> >>
> >> Please Help!
> >>
> >>
> >> SELECT DISTINCT
> >> DDRD.REC_ID,
> >> DDRD.TYPE,
> >> DDRD.STATUS,
> >> DDRD.BRANCH,
> >> DDRD.CREATE_DATE,
> >> DDRD.DOCK_DATE,
> >> DD.[Dockdate & Time],
> >> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> >> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> >> ddrd.RECEIVE_DATE, 110),
> >> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
> >> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> >> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> >> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> >> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> >> DDRD.SumOfRECEIVED_QTY,
> >> DDRD.ER_NO
> >> FROM DockDates.dbo.[Dock Dates Table] DD
> >> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> >> ON (DD.REC_ID = DDRD.REC_ID)
> >> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> >> (DDRD.REC_ID = T.REC_ID)
> >> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-
> 12-
> >> 2004')
> >> GROUP BY DDRD.REC_ID,
> >> DDRD.TYPE,
> >> DDRD.STATUS,
> >> DDRD.BRANCH,
> >> DDRD.CREATE_DATE,
> >> DDRD.DOCK_DATE,
> >> DD.[Dockdate & Time],
> >> DDRD.RECEIVE_DATE,
> >> DDRD.[VAR_REC-DOCKhrs],
> >> DDRD.SumOfRECEIVED_QTY,
> >> DDRD.ER_NO
> >> ORDER BY DDRD.REC_ID
> >
> >
> >.
> >|||The chain breaks at [Variance REC-DOCK hours]
The collation is the same throughout the 2000 Server as
well as 7.0
Thanks again.
>--Original Message--
>1. Depending on your null setting, changing from where
to and can indeed
>cause change of the query. I believe I saw other
situations, but don't
>remember which exactly. Anyway, AND can give different
query than WHERE.
>2. It is likely that you have the same collation between
the two
>servers/db/table column (remember that in SS2K you can
set collation down to
>column) since the one you give for 2K is the same as SS7
default. but a
>verification would help, also verify in SS2K there is no
change from
>collation default.
>3. [Variance REC-DOCK hours] is derived from ... which
is derived from ...
>Some where the chain broke in your DDL. I am not saying
that that was the
>cause since I can not explain why [Dockdate & Time] also
became null. But
>you may want to follow back with those ones that are
correct in SS7 but
>wrong in 2K step by step.
>Quentin
>"Becky" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02ed01c3daea$de02be30$a501280a@.phx.gbl...
>> Thanks in advance for your help!!
>> >--Original Message--
>> >Becky,
>> >
>> >1. what is wrong in your results? Is it the number of
>> records returned, or
>> **Wrong number of records using AND
>> **Wrong results (Null fields) using WHERE
>> >the values returned for [Dockdate & Time] and
[Variance
>> REC-DOCK hours], or
>> >both?
>> **Both
>> >2. can you provide DDL for the tables?
>> ** Alright...you asked for it...
>> CREATE TABLE [dbo].[TRANSACTION] (
>> [TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
>> [TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
>> [TRANSACTION_DATE] [datetime] NULL ,
>> [SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
>> [DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
>> [PRODUCT_ID] [varchar] (40) NULL ,
>> [PERFORMED_BY] [varchar] (30) NULL ,
>> [ORDER_ID] [varchar] (30) NULL ,
>> [ORDER_TYPE] [varchar] (2) NULL ,
>> [ORDER_LINE_NO] [decimal](9, 0) NULL ,
>> [EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
>> [EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
>> [ERD_LINE_NO] [decimal](9, 0) NULL ,
>> [REC_ID] [decimal](9, 0) NULL ,
>> [RECEIVER_TYPE] [varchar] (3) NULL ,
>> [TASK_ID] [decimal](9, 0) NULL ,
>> [SOURCE_LOCATION_NO] [varchar] (20) NULL ,
>> [DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
>> [DROP_LOCATION_NO] [varchar] (20) NULL ,
>> [EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
>> [ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
>> [EXPECTED_UOM] [decimal](2, 0) NULL ,
>> [ACTUAL_UOM] [decimal](2, 0) NULL ,
>> [UOM_FAMILY] [decimal](1, 0) NULL ,
>> [OLD_MSC] [varchar] (3) NULL ,
>> [NEW_MSC] [varchar] (3) NULL ,
>> [OLD_MKR] [varchar] (20) NULL ,
>> [NEW_MKR] [varchar] (20) NULL ,
>> [INVENTORY_ID] [decimal](9, 0) NULL ,
>> [PRODUCT_KEY] [varchar] (250) NULL ,
>> [OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
>> [NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
>> [HOLD_IND] [varchar] (1) NULL ,
>> [REASON_CODE] [varchar] (20) NULL ,
>> [ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
>> [RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
>> [DD_INSTANCE_ID] [decimal](9, 0) NULL ,
>> [UPLOAD_FILE_NAME] [varchar] (30) NULL ,
>> [UPLOAD_IND] [varchar] (1) NULL ,
>> [LOGGING_SOURCE] [varchar] (80) NULL ,
>> [REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
>> [REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
>> [SUCCESS_IND] [varchar] (1) NULL ,
>> [HOST_REFERENCE] [varchar] (40) NULL ,
>> [EXPIRY_DATE] [datetime] NULL ,
>> [LOT_ID] [varchar] (20) NULL ,
>> [BRANCH] [varchar] (20) NULL ,
>> [COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
>> [VENDOR_ID] [varchar] (20) NULL ,
>> [MANUFACTURING_DATE] [datetime] NULL ,
>> [ATTRIBUTE1] [varchar] (20) NULL ,
>> [ATTRIBUTE2] [varchar] (20) NULL ,
>> [ATTRIBUTE3] [varchar] (20) NULL ,
>> [ATTRIBUTE4] [varchar] (20) NULL ,
>> [ATTRIBUTE5] [varchar] (20) NULL ,
>> [ATTRIBUTE6] [varchar] (20) NULL ,
>> [ATTRIBUTE7] [varchar] (20) NULL ,
>> [ATTRIBUTE8] [varchar] (20) NULL ,
>> [ATTRIBUTE9] [varchar] (20) NULL ,
>> [ATTRIBUTE10] [varchar] (20) NULL ,
>> [ATTRIBUTE11] [varchar] (20) NULL ,
>> [ATTRIBUTE12] [varchar] (20) NULL ,
>> [ATTRIBUTE13] [varchar] (20) NULL ,
>> [ATTRIBUTE14] [varchar] (20) NULL ,
>> [ATTRIBUTE15] [varchar] (20) NULL ,
>> [ATTRIBUTE16] [varchar] (20) NULL ,
>> [ATTRIBUTE17] [varchar] (20) NULL ,
>> [ATTRIBUTE18] [varchar] (20) NULL ,
>> [ATTRIBUTE19] [varchar] (20) NULL ,
>> [ATTRIBUTE20] [varchar] (20) NULL ,
>> [REC_LINE_NO] [decimal](9, 0) NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[Dock Dates Table] (
>> [REC_ID] [int] NOT NULL ,
>> [Dockdate & Time] [smalldatetime] NULL ,
>> [Comments] [varchar] (255) NULL ,
>> [UsrID] [varchar] (10) NULL ,
>> [LastModUsrID] [varchar] (10) NULL ,
>> [LastModDate] [datetime] NULL ,
>> [InputDate] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> --
>> SELECT DISTINCT
>> R.REC_ID, R.TYPE, R.STATUS,
>> RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
>> R.RECEIVE_DATE,
>> R.RECEIVE_DATE - ER.ERHE_GD_01 AS
>> [VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
>> SumOfRECEIVED_QTY, ER.ER_NO
>> FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
>> MOVEPROD.dbo.RECEIVER_DETAIL RD
ON
>> R.REC_ID = RD.REC_ID LEFT OUTER JOIN
>> MOVEPROD.dbo.EXPECTED_RECEIPT ER
ON
>> R.ER_ID = ER.ER_ID
>> WHERE (R.STATUS = 'CLO')
>> GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
>> R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
>> R.RECEIVE_DATE - ER.ERHE_GD_01
>>
>> >3. Do you have the same collation in both
installation?
>> **2000--SQL_Latin1_General_CP1_CI_AS
>> **7.0--'
>> >
>> >Quentin
>> >
>> >"Becky Bowen" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
>> >> I recently migrated my databases to a box with SQL
>> Server
>> >> 2000. This query (which is actually a view) returns
>> the
>> >> right results in 7, but in 2000, the [Dockdate &
Time]
>> >> and [Variance REC-DOCK hours] fields always return
>> NULL.
>> >> I have narrowed the problem down to the WHERE
clause,
>> but
>> >> can't figure out how to resolve it. I have tried
>> >> replacing WHERE with AND, which *allows* the fields
to
>> >> return non-NULL values, but the query retrieves over
>> 1000
>> >> records instead of the 42 that it is supposed to.
>> >>
>> >> Please Help!
>> >>
>> >>
>> >> SELECT DISTINCT
>> >> DDRD.REC_ID,
>> >> DDRD.TYPE,
>> >> DDRD.STATUS,
>> >> DDRD.BRANCH,
>> >> DDRD.CREATE_DATE,
>> >> DDRD.DOCK_DATE,
>> >> DD.[Dockdate & Time],
>> >> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
>> >> DDRD.RECEIVE_DATE, --CONVERT(varchar,
>> >> ddrd.RECEIVE_DATE, 110),
>> >> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
>> >> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate
&
>> >> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR
(8),CONVERT
>> >> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
>> >> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
>> >> DDRD.SumOfRECEIVED_QTY,
>> >> DDRD.ER_NO
>> >> FROM DockDates.dbo.[Dock Dates Table] DD
>> >> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
>> >> ON (DD.REC_ID = DDRD.REC_ID)
>> >> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
>> >> (DDRD.REC_ID = T.REC_ID)
>> >> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004'
AND '01-
>> 12-
>> >> 2004')
>> >> GROUP BY DDRD.REC_ID,
>> >> DDRD.TYPE,
>> >> DDRD.STATUS,
>> >> DDRD.BRANCH,
>> >> DDRD.CREATE_DATE,
>> >> DDRD.DOCK_DATE,
>> >> DD.[Dockdate & Time],
>> >> DDRD.RECEIVE_DATE,
>> >> DDRD.[VAR_REC-DOCKhrs],
>> >> DDRD.SumOfRECEIVED_QTY,
>> >> DDRD.ER_NO
>> >> ORDER BY DDRD.REC_ID
>> >
>> >
>> >.
>> >
>
>.
>|||What datatype is ddrd.RECEIVE_DATE? If it is smalldatetime, then you
could try changing "BETWEEN '01-09-2004' AND '01-12-2004'" to "BETWEEN
CAST('01-09-2004' AS smalldatetime) AND CAST('01-12-2004' AS
smalldatetime)".
Hope this helps,
Gert-Jan
Becky Bowen wrote:
> I recently migrated my databases to a box with SQL Server
> 2000. This query (which is actually a view) returns the
> right results in 7, but in 2000, the [Dockdate & Time]
> and [Variance REC-DOCK hours] fields always return NULL.
> I have narrowed the problem down to the WHERE clause, but
> can't figure out how to resolve it. I have tried
> replacing WHERE with AND, which *allows* the fields to
> return non-NULL values, but the query retrieves over 1000
> records instead of the 42 that it is supposed to.
> Please Help!
> SELECT DISTINCT
> DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> ddrd.RECEIVE_DATE, 110),
> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> FROM DockDates.dbo.[Dock Dates Table] DD
> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> ON (DD.REC_ID = DDRD.REC_ID)
> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> (DDRD.REC_ID = T.REC_ID)
> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
> 2004')
> GROUP BY DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> DDRD.RECEIVE_DATE,
> DDRD.[VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> ORDER BY DDRD.REC_ID|||Along these same lines, you might go one step further and replace
'01-09-2004' with
convert(smalldatetime, '01-09-2004', 110)
or
convert(smalldatetime, '01-09-2004', 105)
depending on whether this date is supposed to be January 9, 2004 or
September 1, 2004, respectively.
SK
Gert-Jan Strik wrote:
>What datatype is ddrd.RECEIVE_DATE? If it is smalldatetime, then you
>could try changing "BETWEEN '01-09-2004' AND '01-12-2004'" to "BETWEEN
>CAST('01-09-2004' AS smalldatetime) AND CAST('01-12-2004' AS
>smalldatetime)".
>Hope this helps,
>Gert-Jan
>
>Becky Bowen wrote:
>
>>I recently migrated my databases to a box with SQL Server
>>2000. This query (which is actually a view) returns the
>>right results in 7, but in 2000, the [Dockdate & Time]
>>and [Variance REC-DOCK hours] fields always return NULL.
>>I have narrowed the problem down to the WHERE clause, but
>>can't figure out how to resolve it. I have tried
>>replacing WHERE with AND, which *allows* the fields to
>>return non-NULL values, but the query retrieves over 1000
>>records instead of the 42 that it is supposed to.
>>Please Help!
>>SELECT DISTINCT
>> DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
>> DDRD.RECEIVE_DATE, --CONVERT(varchar,
>>ddrd.RECEIVE_DATE, 110),
>> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
>>(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
>>Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
>>(DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
>>[Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>>FROM DockDates.dbo.[Dock Dates Table] DD
>> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
>>ON (DD.REC_ID = DDRD.REC_ID)
>> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
>>(DDRD.REC_ID = T.REC_ID)
>>WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
>>2004')
>>GROUP BY DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> DDRD.RECEIVE_DATE,
>> DDRD.[VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>>ORDER BY DDRD.REC_ID
>>