directly deleting sql view

i have created a view. And deleted it directly from the database. using Shift+delete. WIll it affect the tables, which has used in that view?No, data and tables are OK....
Dont worry.... :)

You have to be careful thou on what you do with a database it could be millions of dollars lost for the company or not accounted for.
Get yourself test database or play with Pubs first.

Dimension view

Hello,
I want to make a dimension, where dimension members are either of type 1 or type 2, (to put it in a simple way). I have two views of a table, each containing plus minus 200 records, wich contains strings, wich can be used to check whether a dimension member is of type 1 or 2.
Now I can make a view as dimension table with the occurences labelled these dimension as being of type 1 or 2, but, since there is a huge amount of data being generated, and I want the dimensional database to be updated regularly using a scheduler, I fear that the performance of this update will be extremely slow, since that process needs to search through 400 records for each record to be processed.
I'd prefer to make a view wich filters the dimension members when these are already generated, so I can make MDX queries like: SELECT [Type 1] ON COLUMNS

FYI I use a CLR function (to be able to use regex - I create natural keys with it) in these views, so I really need a similar functionality.
This is my data source

view.
I want to be able to filter the dimension members

generated from the UserAgents (dimension table) by the all_keys table.|||

Well, this is the data source view.
I want to be able to filter the dimension members

generated from the UserAgents (dimension table) by the all_keys table.

|||I made myself a dimension table with a view using a SELECT DISTINCT on the UserAgents (so, kinda like a dimension), and then I made a dimension wich uses an inner join with this view and All_Keys table. I hope this won't give me performance issues...

dimension design

I have a fact table and dimension. a view is taken as base table for Dimension which will Productkey,LevelId. One product may exist in multiple levels. so we have multiple records for a single product.

And in fact table we have productkey as foriegnKey. When i relate fact n dim to create a cube, cube considering one record for each product key in fact table. its automatically avoiding other records. can we any way stop this happening? OR is this default behavior?

Thanks in adv

Do you have a self-join in your product dimensions table between productid and parentproductid? This sounds to me as if you have a parent-child dimension/structure. In the dimension wizard you will see, in one of the final steps, a question/page where you can tell if you have a parent child structure.

You will also check if you are not having a many-to-many relation between the fact table and the product dimension table.

The third point is that you can add the level to the productid and make a unique technical key for each kombination of product and level. This will have to be matched with the records in the fact table.

HTH

Thomas Ivarsson

|||

Thanks for the response.

3rd point is not possible to implement..let me check with first point.

so does it ignore multiple records for same productKey?

|||

Hello again. In the AdventureWorks sample database you have table, Production.BillOfMaterials that you can have a look at. In the Adventure Works DW sample database you have the account dimension with a more simple parent child relation.

I am not sure what you mean by "ignore multiple records for the same productkey.

With parent-child dimensions you can have data/facts in the fact table that is not only from the leaf level but also from higher levels.

HTH

Thomas Ivarsson

|||

In this case its not possible to implement Parent-Child dimension. and there is also situation that one child product can have multiple products as parents. Its some thing like many-many in Parent-child.

|||

Looks like you have to use many-to-many dimensions then.

Here is a link to some help: (http://www.sqlbi.eu/Default.aspx?tabid=80)

HTH

Thomas Ivarsson

|||

Before coming to forum for the solution, i had tried m2m dimension. When i took the view as dimension where one product can exist in multiple lelvels. table will look some thing like this

leafPKey 5233 5233

L1PKey 9 10

L2PKey 31 15

L3PKey 632 632

L4PKey 2572 2572

L5PKey 5233 5233

When this table is related to fact table only one 5233 is considered. 5233 is only visible under l1pkey= 10.

is this default behavior, or can i get both the ids by changing some thing?

when a single record is only considered m2m relation is existing.

Thanks in adv

sql

Dimension creation manually issue

I have created a new dimension manually from a project.But when I rebuild,deploy and process and view browser for that cube, I couldnt see the new dimension from the list of dimensions. Under what condition should the new dimension 'relates' to the cube?

Thanks.

Regards

Alu

You have to add it to your cube and then define right relationship between dimension and measure group|||

You do this in the dimension usage tab in the cube editor in BIDS.

Right click on the dimensions and choose add cube dimension

HTH

Thomas Ivarsson

|||

Thanks.

Regards

Alu

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

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(DA
TETIME,
(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...

quote:

---

> 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(DA
TETIME,
> (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!!

quote:

---

>--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

quote:

---

>the values returned for [Dockdate & Time] and [Variance

---

REC-DOCK hours], or

quote:

---

>both?

---

**Both

quote:

---

>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

quote:

---

>3. Do you have the same collation in both installation?

---

**2000--SQL_Latin1_General_CP1_CI_AS
**7.0--'

quote:

---

>Quentin
>"Becky Bowen" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
Server[QUOTE]
the[QUOTE]
NULL.[QUOTE]
but[QUOTE]
1000[QUOTE]
12-[QUOTE]
>
>.
>

---

|||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...[QUOTE]
> Thanks in advance for your help!!
> records returned, or
> **Wrong number of records using AND
> **Wrong results (Null fields) using WHERE
>
> REC-DOCK hours], or
> **Both
> ** 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
>
> **2000--SQL_Latin1_General_CP1_CI_AS
> **7.0--'
> wrote in message
> Server
> the
> NULL.
> but
> 1000
> 12-|||The chain breaks at [Variance REC-DOCK hours]
The collation is the same throughout the 2000 Server as
well as 7.0
Thanks again.

quote:

---

>--Original Message--
>1. Depending on your null setting, changing from where

---

to and can indeed

quote:

---

>cause change of the query. I believe I saw other

---

situations, but don't

quote:

---

>remember which exactly. Anyway, AND can give different

---

query than WHERE.

quote:

---

>2. It is likely that you have the same collation between

---

the two

quote:

---

>servers/db/table column (remember that in SS2K you can

---

set collation down to

quote:

---

>column) since the one you give for 2K is the same as SS7

---

default. but a

quote:

---

>verification would help, also verify in SS2K there is no

---

change from

quote:

---

>collation default.
>3. [Variance REC-DOCK hours] is derived from ... which

---

is derived from ...

quote:

---

>Some where the chain broke in your DDL. I am not saying

---

that that was the

quote:

---

>cause since I can not explain why [Dockdate & Time] also

---

became null. But

quote:

---

>you may want to follow back with those ones that are

---

correct in SS7 but

quote:

---

>wrong in 2K step by step.
>Quentin
>"Becky" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:02ed01c3daea$de02be30$a501280a@.phx.gbl...
[Variance[QUOTE]
ON[QUOTE]
ON[QUOTE]
installation?[QUOTE]
Time][QUOTE]
clause,[QUOTE]
to[QUOTE]
&[QUOTE]
(8),CONVERT[QUOTE]
AND '01-[QUOTE]
>
>.
>

---

|||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:

quote:

---

> 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(DA
TETIME,
> (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:
[QUOTE]
>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:
>

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
>>

Different query plans for view and view definition statement

I compared view query plan with query plan if I run the same statement
from view definition and get different results. View plan is more
expensive and runs longer. View contains 4 inner joins, statistics
updated for all tables. Any ideas?which version?|||SQL Server 2000, Enterprise Edition with SP4|||ysfinks (ysfinks@.gmail.com) writes:
> I compared view query plan with query plan if I run the same statement
> from view definition and get different results. View plan is more
> expensive and runs longer. View contains 4 inner joins, statistics
> updated for all tables. Any ideas?

Since you didn't share anything close to a repro, I have little idea
of you what you are doing. Since a view essential is a macro, it should
not matter that much. Then again, I've been wrong before. Anyway, it
would help if you posted the view, and the two SELECT you run.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I narrowed down to one join. Same difference in query plans. Query cost
for 1 is 5.48%, for 2 is 94.52%
My view is:
create view dbo.sf_test as
SELECT
dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
CompanyAccounts.root_account_id AS ECCRootID
FROM dbo.ManagedNodes WITH (NOLOCK)
INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id

My queries are:
1.
SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
CompanyAccounts.root_account_id AS ECCRootID
FROM dbo.ManagedNodes WITH (NOLOCK)
INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
where ECCRootID=15427
2.
select NodeID, SubscriptionID, ECCRootID
from dbo.sf_test where eccrootid=15427|||ysfinks (ysfinks@.gmail.com) writes:
> I narrowed down to one join. Same difference in query plans. Query cost
> for 1 is 5.48%, for 2 is 94.52%
> My view is:
> create view dbo.sf_test as
> SELECT
> dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> My queries are:
> 1.
> SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> where ECCRootID=15427
> 2.
> select NodeID, SubscriptionID, ECCRootID
> from dbo.sf_test where eccrootid=15427

I will have to admit that I don't have any good answers at this
point. But I still like to ask some questions, just to check:

Exactly how do you create the view? From Query Analyzer or Enterprise
Manager? If the latter, what happens, if you run a script in QA
where you first create the view, and then run the queries?

What happens if you take out the NOLOCK hints?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||so if i am understanding, if you do a select against a view, it takes a
very long time.

but if copy that exact same code into query analyzer or a stored
procedure, it goes MUCH faster.

and If I am understanding correctly the issue, there will be an index
on eccrootid.

And, if I am understanding, the view won't use the index on ECCrootid,
but everything else will.
Do I have the issue correctly? If so, yup, it does that in SS2000. You
can try compiler hints in the view to FORCE it to sue the index, but
that only works sometimes.

Best workaround is to move all your views to stored procedures, and
pass the eccrootid parameter to the sproc.

I reported this 5 years ago, adn even discussed it with Erland at that
time.

Views suck.

Regards,
Doug|||A VIEW is handled two ways in SQL. The text of the VIEW is "pasted"
into the query that uses it and then the parser and optimizer handle it
as if the query had been written with a derived table. The parser can
do a lot stuff at this point, so the original view text is "spread out
all over the place".

The second way is materialize the VIEW as a temporary table. The good
news is that this materialized table can be shared by multiple users,
so the overall processing time goes down, even if each user's plan is
not optimal for their query. This is a feature of larger SQL products
like Ingres, DB2 or Oracle.

Trust in the optimizer, Luke.|||if you are going ot have a materialized view, why not just bite the
bullet and have a denormalized table hanging around that gets updated
all the time.

the optimizer is fine for 90 percent of the time.|||View was created from Query Analyzer. If I remove nolock - same result.
Another fact - if I change condition value in where clause, for some
values it gives for the view the good query plan using index for
eccrootid.
For the query simulating the view - always good plan.|||ysfinks (ysfinks@.gmail.com) writes:
> View was created from Query Analyzer. If I remove nolock - same result.
> Another fact - if I change condition value in where clause, for some
> values it gives for the view the good query plan using index for
> eccrootid.
> For the query simulating the view - always good plan.

I will have admit that I am fairly stumped at this point. For this reason
I have consulted some other people offline. No promises, but keep watching
this space.

Nevertheless, you run the two queries bracketed by

set statistics profile on
set statistics profile off

If you can put that in a file as a attachment ot on web site, to avoid
that the output is mashsed in news transport, that would be great, but
anything goes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Different no. of rows returned in SEM vs QA

Hi,
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
Thanks,
AlphCould you kindly post the query, so that we can help you better.|||Its a union query with 12 Selects. Here is the first select:

SELECT
dbo.tblWBS.PSWBS AS [PSWBS Code],
ConcatenatedWBS AS [CWBS Code],
case when CR is null then 'R' else 'C' end AS [Capital / Revenue],
NDACost AS [Cost Element],
SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) as Amount,
'01-04-' + ltrim(rtrim(str(FiscalYear))) AS [Start Date],
'30-04-' + ltrim(rtrim(str(FiscalYear))) AS [Finish Date],
left(dbo.tblWBS.wbs,6) + Right(dbo.tblWBS.wbs,5) AS [Charge Code]

FROM dbo.tblBCWSMonthly INNER JOIN
dbo.tblBCWSYearly ON dbo.tblBCWSMonthly.RecordId = dbo.tblBCWSYearly.RecordUid INNER JOIN
dbo.tblWBS ON dbo.tblBCWSYearly.WBSUId = dbo.tblWBS.WBSuid

WHERE (dbo.tblWBS.EPSLvl4 = N'1.1.5.17') and dbo.tblWBS.PSWBS = '1.1.5.17.10.01.17001.00000.30 '

GROUP BY dbo.tblWBS.PSWBS, ConcatenatedWBS, CR, NDACost,'01-04-' + ltrim(rtrim(str(FiscalYear))),'30-04-' + ltrim(rtrim(str(FiscalYear))),dbo.tblWBS.wbs

HAVING SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) <> 0

UNION ALL

>> Then another 11 select statements|||Check the "Set concat_null_yields_null" setting in your Query Analyzer Connection Properties dialog box. Try toggling it, as it may be set different than your Server default.

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.

Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.