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. 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" <anonym...@discussions.microsoft.com> wrote in message
> 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
--- 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?
>"Becky Bowen" <anonym...@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" <anonym...@discussions.microsoft.com> wrote in message
> 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...
> --- > 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" <anonym...@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.
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
>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" <anonym...@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...
>> --- >> 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" <anonym...@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.
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)".
> 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
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
