join or subselect: view

Showing posts with label view. Show all posts

Monday, March 19, 2012

Joining table to itself

In myTable I have
RequiredAmount
RequiredDate
GrantedAmount
GrantedDate
I wish to obtain one view showing the sum per year.
For the required part it would be:
SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
And for the Granted part:
SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)
Now, what I need is a presentation with three columns:
myYear, reqAmount, grantAmount
I'm fooling around with the table joined to itself, but can't seem to
get it right...maybe wrong approach?
Regards /SnedkerRead about Cross-tab reports in sql server help file
Madhivanan
Morten Snedker wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker|||use a full outer join (self)..
something like this..
untested..
SELECT COALESCE(A.myYear,B.myYear), COALESCE(A.reqAmount,0),
COALESCE(B.grantAmount,0)
FROM
(SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
) A FULL OUTER JOIN
(SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)) B
ON A.myYear= B.myYear
Hope this helps.
-Omni|||Can you see if this works for you
SELECT
YEAR(tDate) as TransYear,
SUM(CASE WHEN tType = 'R' then tAmt else 0 end) as SumReq,
SUM(CASE WHEN tType = 'G' then tAmt else 0 end) as GraReq
FROM
(
select 'R' as tType,reqAmount as tAmt,ReqDate as tDate FROM Mytable
UNION ALL
select 'G' as tType,GrantAmount as tAmt,GrantDate as tDate FROM Mytable
) x
GROUP BY YEAR(tDate)
- Sha Anand
"Morten Snedker" wrote:

> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker
>|||Hi,
Nice solution.. But Why do you need a case? Can't it be something simple
like this.
select TransYear, sum(GrantAmount) SumGrant, sum(reqAmount) SumReq
from
(select 0 as GrantAmount,reqAmount ,year(ReqDate) as TransYear FROM Mytable
UNION ALL
select GrantAmount,0 as reqAmount, year(GrantDate) as TransYear FROM
Mytable) x
group by TransYear
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If your a consultant you know your clients couldn't give
a whit about how you provide a solution just as long as you
give them one.Perhaps we can help.Check out RAC for easy
solutions to all kinds of data manipulation problems including
crosstabs on sql server.
www.rac4sql.net

Joining multiple tables in a view.

I have three tables

1^st table is Student

StudnetID (pk)

Other fields…

2^nd table is PhoneType

PhoneTypeID (pk)

PhoneType

3^rd table is StudentHasPhone

SHPID (pk)

StudnetID (fk)

PhoneTypeID (fk)

PhoneNumber

PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…

StudnetID: 1

Name: John

HomePhone: 123-456-7890

WorkPhone: 123-456-7890

CellPhone:

Pager: 123-456-7890

Fax:

Memo: This is one student record.

Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won't have.

Thanks in advanced,

Nathan Rover

What you need is a View with UNION ALL but your tables must be UNION compatible which means same data type facing the same direction. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

|||

You can join to the phone table multiple times, as follows:

SELECT StudentID, HP.PhoneNumber, WP.PhoneNumber, CP.PhoneNumber, Pg.PhoneNumber, Fx.PhoneNumber
FROM Student S
LEFT OUTER JOIN StudentHasPhone HP
ON S.StudentID = HP.StudentID
AND HP.PhoneTypeID = 1 --Home Phone
LEFT OUTER JOIN StudentHasPhone WP
ON S.StudentID = WP.StudentID
AND WP.PhoneTypeID = 2 --Work Phone
LEFT OUTER JOIN StudentHasPhone CP
ON S.StudentID = CP.StudentID
AND CP.PhoneTypeID = 3 --Cell Phone
LEFT OUTER JOIN StudentHasPhone Pg
ON S.StudentID = Pg.StudentID
AND Pg.PhoneTypeID = 4 --Pager
LEFT OUTER JOIN StudentHasPhone Fx
ON S.StudentID = Fx.StudentID
AND Fx.PhoneTypeID = 5 --Fax

BTW: StudentHasPhone is not a good table name. StudentPhone, or simply Phone, would be much better.

|||

Thanks, that was exactly what I was looking for… It worked perfect.

--Nathan

Joining multiple tables in a view.

I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell
phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make
a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have
one or two. If possible I would like to do a setup like this in my database
to keep from having to have null fields for 4 phone numbers that the majorit
y
of records won’t have.
Thanks in advanced,
Nathan RoverHi
You can self join the tables, but that would reduce the flexibility of your
design e.g.
SELECT s.StudnetID. s.Name, a.PhoneNumber As HomePhone,
b.PhoneNumber As WorkPhone,
c.PhoneNumber As CellPhone
d.PhoneNumber As Pager
e.PhoneNumber As Fax
FROM Student s
LEFT JOIN StudentHasPhone a ON a.StudnetID = s.StudnetID AND a.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Home phone' )
LEFT JOIN StudentHasPhone b ON b.StudnetID = s.StudnetID AND b.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Work phone' )
LEFT JOIN StudentHasPhone c ON c.StudnetID = s.StudnetID AND c.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Cell phone' )
LEFT JOIN StudentHasPhone d ON d.StudnetID = s.StudnetID AND d.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Pager' )
LEFT JOIN StudentHasPhone e ON e.StudnetID = s.StudnetID AND e.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Fax' )
The best solution is to process this at the client.
John
"NateDawg" wrote:

> I have three tables
> 1st table is Student
> StudnetID (pk)
> Other fields…
> 2nd table is PhoneType
> PhoneTypeID (pk)
> PhoneType
> 3rd table is StudentHasPhone
> SHPID (pk)
> StudnetID (fk)
> PhoneTypeID (fk)
> PhoneNumber
> PhoneType is an auxiliary table that has 5 records in it Home phone, Cell
> phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe ma
ke
> a view of a view that would allow me to ultimately end up with…
> StudnetID: 1
> Name: John
> HomePhone: 123-456-7890
> WorkPhone: 123-456-7890
> CellPhone:
> Pager: 123-456-7890
> Fax:
> Memo: This is one student record.
> Some students will have no phone number, some will have all 5 most will ha
ve
> one or two. If possible I would like to do a setup like this in my databas
e
> to keep from having to have null fields for 4 phone numbers that the major
ity
> of records won’t have.
> Thanks in advanced,
> Nathan Rover
>

Friday, March 9, 2012

Joined view does not contain all colums

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHA\SQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

It looks ok to me. Does your simplified query (below) work?

Joe G wrote:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

|||

first thing, try running ALTER VIEW with their current defenitions. A view is built at runtime like a table. It has records in syscolumns etc. Thus this thread brings up the old "what does SELECT * return in a view" questions. I got this wrong not too long ago when IView with MSFT...I could have killed myself lol.

I am telling you this info. because whenever someone says "a column that I recently added is not showing up in view", this is usually the problem. rerun THE ALTERVIEW statement.

HTH,

Derek

if this does not solve your problem, let me know and I will dig into it w/you further.

|||

Derek, Thank you very much! That indeed was the solution to my problem. Thanks to Skippy also for responding.

Joe

Join using like?

I would like to create a view that shows each employee's id and there vp's information, if the vp's id is a part of the dbo.HIERARCHY.Hierarchy field (which is a string containing the employee's entire hierarchy).
In enterprise manager it accepts this as a parsable query, but it isn't getting me the data I need. I suspect it has something to do with me not knowing how to add the wildcard characters when searching the dbo.HIERARCHY.Hierarchy field. Any idea how I can fix this?

SELECT dbo.CCINFORMATION.*, dbo.HIERARCHY.AWID AS Employee
FROM dbo.CCINFORMATION INNER JOIN
dbo.HIERARCHY ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy
WHERE (dbo.CCINFORMATION.Title LIKE '%VP,%') OR
(dbo.CCINFORMATION.Title LIKE '%CEO%') OR
(dbo.CCINFORMATION.Title LIKE '%VICE PR%')The problem is in your join clause:

ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy

Since you are not using wildcards, your criteria is equivalent to

ON dbo.CCINFORMATION.AWID = dbo.HIERARCHY.Hierarchy

...except for ignoring trailing spaces in AWID.

Try:

ON dbo.CCINFORMATION.AWID LIKE '%' + dbo.HIERARCHY.Hierarchy + '%'

...but I suspect there are more problems in your table design that need to be addressed.

blindman|||Originally posted by blindman
...but I suspect there are more problems in your table design that need to be addressed.

blindman

Blindman, what makes you thinking that?

dyingjoy: if hierarchy contains your employee ID, you have to reverse your join condition:

ON dbo.HIERARCHY.Hierarchy LIKE '%' + dbo.CCINFORMATION.AWID + '%'|||Blindman, what makes you thinking that?

I'm no expert, but I think that for a one time search this query would work fine. As part of a process however, it would be much more efficient to definitavely identify employess as a 'CEO' or 'VP' in a seperate column.

From the query it appears the Title values are something like 'VP of Marketing', 'VP of Finance', etc. For frequent searches on specific portions of this column, it would meake sense to me to standardize the portion you are searching on in its own column.

ie.

CCINFORMATION.EmpType CHAR(3)
CCINFORMATION.Title VARCHAR(20)|||Yup.

Hard-coding strings like 'VP', 'CEO', 'VICE PR' just invites problems when someone enters a title like 'V.P.', 'C.E.O.', or 'Big Kahuna', or when some mid-level manager or department head requests to be included.

blindman

Wednesday, March 7, 2012

JOIN Tables in 2 Databases

It is possible make a View that Join 2 tables in different databases?
Thanks
Bonato Pierantonio
Yes, just define your tables in the form of <database name>.<owner
name>.<table name>
Jacco Schalkwijk
SQL Server MVP
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:ufz5N6YzEHA.748@.TK2MSFTNGP14.phx.gbl...
> It is possible make a View that Join 2 tables in different databases?
> Thanks
> Bonato Pierantonio
>

JOIN Tables in 2 Databases

It is possible make a View that Join 2 tables in different databases?
Thanks
Bonato PierantonioYes, just define your tables in the form of <database name>.<owner
name>.<table name>
--
Jacco Schalkwijk
SQL Server MVP
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:ufz5N6YzEHA.748@.TK2MSFTNGP14.phx.gbl...
> It is possible make a View that Join 2 tables in different databases?
> Thanks
> Bonato Pierantonio
>

JOIN Tables in 2 Databases

It is possible make a View that Join 2 tables in different databases?
Thanks
Bonato PierantonioYes, just define your tables in the form of <database name>.<owner
name>.<table name>
Jacco Schalkwijk
SQL Server MVP
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:ufz5N6YzEHA.748@.TK2MSFTNGP14.phx.gbl...
> It is possible make a View that Join 2 tables in different databases?
> Thanks
> Bonato Pierantonio
>

join stored procedure and view

Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()

while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)

--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END

select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO

-which returns all the date for the past three month
and this is my view
CREATE VIEW dbo.rpt_Kids
AS
SELECT TOP 100 PERCENT
KF_ID,dbo.just_date_formal(KF_DATE) as KF_DATE,KF_STATUS FROM dbo.KIDS
order by Year(KF_date) DESC,Month(KF_date) DESC,Day(KF_date) DESC

just_date_formal function
CREATE FUNCTION [dbo].[just_date_formal](@.dtvalue datetime)
RETURNS nvarchar(40)
AS
BEGIN
DECLARE @.display nvarchar(40)
SET @.display = CAST(DATEPART(dd, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(mm, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @.dtvalue) AS nvarchar) + ', ' + CAST(DATENAME(dw, @.dtvalue) AS nvarchar)
RETURN @.display
END

this returns all the date on which error is generated.on other dates on which no error message is generated.
now i want to join both of them so that i get dates of error message and those dates also on which error message is not generated
like

23 june 2005 error
22 june 2005
21 june 2005 error

it should return all dates regardless errror is there or not

Hi,

You directly cannot JOIN the output of a stored procedure in a FROM clause. Stored procedures cannot be used in contexts that require relational expressions. I realize this seems odd at first, since you *can* return a rowset from a stored procedure. However, the reasons we disallow it in TSQL are

(a) The shape of the rowset cannot be determined ahead of time (there is no metadata anywhere that describes the table returned by a stored proc, and besides, the shape returned might depend on the run-time execution path though the proc ... if (condition) select * from somewhere else select * from somewherelese)

and (b) a stored procedure can return > 1 result set.

You have a few options available

1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:

create table #tempcal(KF_DATE datetime, KF_STATUS int)
go

insert into #tempcal exec FlyDate
go

now you can JOIN on #tempcal.

2) You can refactor your proc to make it into a table-valued function, which you *can* use in a JOIN. You will have to factor-out the non-deterministic getdata() and pass them in as parameters. The code would look something like this

create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin
while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end

return
end
go

select * from FlyDateFunc(getdate() , getdate())
go

Does this answer your question?
Thanks

|||

Hi,

I have an extra complication to this problem. I have SP's that create dynamic columns based on the parameters they get. So i don't know in advance what my #table should look like. Is there any way to do this without knowing the columns of the #table? (like the select * into #tmp from table1 but then using EXEC? )

I have developed SP's that create dyn columns. Now i want to use these same SP's in SSRS, but that want's to know the column names in advance. So i want to store the results of the SP in a #table and then unpivot that to send it to RS. Think that'll work?

[edit] the SP's use dynamic SQL to get the results

Regards Gert-Jan

join stored procedure and view

Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()

while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)

--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END

select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO

23 june 2005 error
22 june 2005
21 june 2005 error

it should return all dates regardless errror is there or not

Hi,

and (b) a stored procedure can return > 1 result set.

You have a few options available

1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:

create table #tempcal(KF_DATE datetime, KF_STATUS int)
go

insert into #tempcal exec FlyDate
go

now you can JOIN on #tempcal.

create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin

while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end

return
end
go

select * from FlyDateFunc(getdate() , getdate())
go

Does this answer your question?
Thanks

|||

Hi,

[edit] the SP's use dynamic SQL to get the results

Regards Gert-Jan

Friday, February 24, 2012

Join query with view and inline view produced a different result

Hi:
I've a problem with the following querys, These two query is suppose to
produce a same result
but it is not, i don't know why.
The first query is using view, it procuce a correct result (2 rows),
the second query is using inline view (the inline view defination is
exactly the same as the view) but the result is wrong (4 rows).
>From the execution plan, the second query perform the join with the
inline view twist which is not correct.
Please help.
JCVoon
-- Join with view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
SELECT * From view_PutHold
WHERE CompanyCode='HQ' And BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
-- Join with inline view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
--Here is the DDL
CREATE TABLE [dbo].[WmsPutawayDet] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[Qty] [numeric](18, 0) NOT NULL ,
[Completed] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsPutawayHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsTallyInHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[wmsStockLedger] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL ,
[TxnNo] [varchar] (10) NOT NULL ,
[BaseQuantity] [numeric](18, 0) NOT NULL ,
[PhysicalFlag] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayDet] ADD
CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo],
[ProductCode],
[LocationCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayHed] ADD
CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsTallyInHed] ADD
CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[TallyInNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[wmsStockLedger] ADD
CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[ProductCode],
[LocationCode],
[TallyInNo],
[PrincipalCode],
[TxnNo]
) ON [PRIMARY]
GO
create view view_PutHold as
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
GO
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)I am looking into this issue.
Looks like a problem with SQL server itself.
The query works as expected in SQL 2005 (returns only 2 rows in both cases)
--
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||The problem appears to be in the section
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
If you just comment that and run the query, the result is correct.
Also if you comment the
SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
line, the query gives the correct result.
I am still not sure whether its a known bug. I will update you once I have
more info.
BTW thankls for posting the DDL.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag)
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
--And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
--IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
--
Roji. P. Thomas
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I am looking into this issue.
> Looks like a problem with SQL server itself.
> The query works as expected in SQL 2005 (returns only 2 rows in both
> cases)
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>> Hi:
>> I've a problem with the following querys, These two query is suppose to
>> produce a same result
>> but it is not, i don't know why.
>> The first query is using view, it procuce a correct result (2 rows),
>> the second query is using inline view (the inline view defination is
>> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
>> inline view twist which is not correct.
>> Please help.
>> JCVoon
>>
>>
>> -- Join with view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> SELECT * From view_PutHold
>> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> -- Join with inline view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> WHERE Hd.CompanyCode = 'HQ'
>> And Hd.BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>>
>> --Here is the DDL
>> CREATE TABLE [dbo].[WmsPutawayDet] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [Qty] [numeric](18, 0) NOT NULL ,
>> [Completed] [bit] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsPutawayHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsTallyInHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[wmsStockLedger] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL ,
>> [TxnNo] [varchar] (10) NOT NULL ,
>> [BaseQuantity] [numeric](18, 0) NOT NULL ,
>> [PhysicalFlag] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayDet] ADD
>> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo],
>> [ProductCode],
>> [LocationCode]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayHed] ADD
>> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsTallyInHed] ADD
>> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [TallyInNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[wmsStockLedger] ADD
>> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [ProductCode],
>> [LocationCode],
>> [TallyInNo],
>> [PrincipalCode],
>> [TxnNo]
>> ) ON [PRIMARY]
>> GO
>> create view view_PutHold as
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> GO
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
Could be re-written
AND ( LEFT(Trx.TXNNo,3) = 'OP-'
OR PutHold.Completed = 1 )
John
"Roji. P. Thomas" wrote:
> The problem appears to be in the section
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> If you just comment that and run the query, the result is correct.
> Also if you comment the
> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
> line, the query gives the correct result.
> I am still not sure whether its a known bug. I will update you once I have
> more info.
> BTW thankls for posting the DDL.
>
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag)
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
> >I am looking into this issue.
> >
> > Looks like a problem with SQL server itself.
> >
> > The query works as expected in SQL 2005 (returns only 2 rows in both
> > cases)
> >
> > --
> > Roji. P. Thomas
> > http://toponewithties.blogspot.com
> >
> >
> > "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> > news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> >> Hi:
> >>
> >> I've a problem with the following querys, These two query is suppose to
> >> produce a same result
> >> but it is not, i don't know why.
> >>
> >> The first query is using view, it procuce a correct result (2 rows),
> >> the second query is using inline view (the inline view defination is
> >> exactly the same as the view) but the result is wrong (4 rows).
> >>
> >>From the execution plan, the second query perform the join with the
> >> inline view twist which is not correct.
> >>
> >> Please help.
> >> JCVoon
> >>
> >>
> >>
> >>
> >> -- Join with view
> >> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> >> Trx.PhysicalFlag) AS BaseQty
> >> FROM WmsStockLedger Trx
> >>
> >> LEFT JOIN
> >> (
> >> SELECT * From view_PutHold
> >> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> >> ) PutHold
> >> ON PutHold.CompanyCode = Trx.CompanyCode
> >> And PutHold.BranchCode = Trx.BranchCode
> >> And PutHold.WONo = Trx.TxnNo
> >> And PutHold.ProductCode = Trx.ProductCode
> >> And PutHold.TallyInNo = Trx.TallyInNo
> >> WHERE
> >> Trx.CompanyCode='HQ'
> >> And Trx.BranchCode='HQ'
> >> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> >> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> >> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> >> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> >>
> >> -- Join with inline view
> >> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> >> Trx.PhysicalFlag) AS BaseQty
> >> FROM WmsStockLedger Trx
> >> LEFT JOIN
> >> (
> >> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> >> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> >> FROM WmsPutawayHed Hd
> >> INNER JOIN WmsPutawayDet Dt
> >> ON Dt.CompanyCode = Hd.CompanyCode
> >> And Dt.BranchCode = Hd.BranchCode
> >> And Dt.WoNo = Hd.WoNo
> >> And Dt.Completed = 1
> >> INNER JOIN WmsTallyInHed Ti
> >> ON Ti.CompanyCode = Hd.CompanyCode
> >> And Ti.BranchCode = Hd.BranchCode
> >> And Ti.TallyInNo = Hd.TallyInNo
> >> WHERE Hd.CompanyCode = 'HQ'
> >> And Hd.BranchCode = 'HQ'
> >> ) PutHold
> >>
> >> ON PutHold.CompanyCode = Trx.CompanyCode
> >> And PutHold.BranchCode = Trx.BranchCode
> >> And PutHold.WONo = Trx.TxnNo
> >> And PutHold.ProductCode = Trx.ProductCode
> >> And PutHold.TallyInNo = Trx.TallyInNo
> >> WHERE
> >> Trx.CompanyCode='HQ'
> >> And Trx.BranchCode='HQ'
> >> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> >> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> >> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> >> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> >>
> >>
> >> --Here is the DDL
> >>
> >> CREATE TABLE [dbo].[WmsPutawayDet] (
> >> [CompanyCode] [varchar] (2) NOT NULL ,
> >> [BranchCode] [varchar] (2) NOT NULL ,
> >> [WoNo] [varchar] (10) NOT NULL ,
> >> [ProductCode] [varchar] (10) NOT NULL ,
> >> [LocationCode] [varchar] (10) NOT NULL ,
> >> [Qty] [numeric](18, 0) NOT NULL ,
> >> [Completed] [bit] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE TABLE [dbo].[WmsPutawayHed] (
> >> [CompanyCode] [varchar] (2) NOT NULL ,
> >> [BranchCode] [varchar] (2) NOT NULL ,
> >> [WoNo] [varchar] (10) NOT NULL ,
> >> [TallyInNo] [varchar] (10) NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE TABLE [dbo].[WmsTallyInHed] (
> >> [CompanyCode] [varchar] (2) NOT NULL ,
> >> [BranchCode] [varchar] (2) NOT NULL ,
> >> [TallyInNo] [varchar] (10) NOT NULL ,
> >> [PrincipalCode] [varchar] (10) NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> CREATE TABLE [dbo].[wmsStockLedger] (
> >> [CompanyCode] [varchar] (2) NOT NULL ,
> >> [BranchCode] [varchar] (2) NOT NULL ,
> >> [ProductCode] [varchar] (10) NOT NULL ,
> >> [LocationCode] [varchar] (10) NOT NULL ,
> >> [TallyInNo] [varchar] (10) NOT NULL ,
> >> [PrincipalCode] [varchar] (10) NOT NULL ,
> >> [TxnNo] [varchar] (10) NOT NULL ,
> >> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> >> [PhysicalFlag] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> >> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> >> (
> >> [CompanyCode],
> >> [BranchCode],
> >> [WoNo],
> >> [ProductCode],
> >> [LocationCode]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> >> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> >> (
> >> [CompanyCode],
> >> [BranchCode],
> >> [WoNo]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> >> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> >> (
> >> [CompanyCode],
> >> [BranchCode],
> >> [TallyInNo]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[wmsStockLedger] ADD
> >> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> >> (
> >> [CompanyCode],
> >> [BranchCode],
> >> [ProductCode],
> >> [LocationCode],
> >> [TallyInNo],
> >> [PrincipalCode],
> >> [TxnNo]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> create view view_PutHold as
> >> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> >> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> >> FROM WmsPutawayHed Hd
> >> INNER JOIN WmsPutawayDet Dt
> >> ON Dt.CompanyCode = Hd.CompanyCode
> >> And Dt.BranchCode = Hd.BranchCode
> >> And Dt.WoNo = Hd.WoNo
> >> And Dt.Completed = 1
> >> INNER JOIN WmsTallyInHed Ti
> >> ON Ti.CompanyCode = Hd.CompanyCode
> >> And Ti.BranchCode = Hd.BranchCode
> >> And Ti.TallyInNo = Hd.TallyInNo
> >> GO
> >>
> >> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> >> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> >>
> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> >>
> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> >>
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> >> INSERT INTO [WmsStockLedger]
> >> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
> >>
> >
> >
>
>|||Right. But that doesn't seems to solve the original problem.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5440A06F-56F1-4A6A-8817-7EAF7A5E8413@.microsoft.com...
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> Could be re-written
> AND ( LEFT(Trx.TXNNo,3) = 'OP-'
> OR PutHold.Completed = 1 )
> John
> "Roji. P. Thomas" wrote:
>> The problem appears to be in the section
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> If you just comment that and run the query, the result is correct.
>> Also if you comment the
>> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
>> line, the query gives the correct result.
>> I am still not sure whether its a known bug. I will update you once I
>> have
>> more info.
>> BTW thankls for posting the DDL.
>>
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag)
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> WHERE Hd.CompanyCode = 'HQ'
>> And Hd.BranchCode = 'HQ') PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> --
>> Roji. P. Thomas
>> http://toponewithties.blogspot.com
>>
>> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
>> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> >I am looking into this issue.
>> >
>> > Looks like a problem with SQL server itself.
>> >
>> > The query works as expected in SQL 2005 (returns only 2 rows in both
>> > cases)
>> >
>> > --
>> > Roji. P. Thomas
>> > http://toponewithties.blogspot.com
>> >
>> >
>> > "jcvoon" <jcvoon@.maximas.com.my> wrote in message
>> > news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>> >> Hi:
>> >>
>> >> I've a problem with the following querys, These two query is suppose
>> >> to
>> >> produce a same result
>> >> but it is not, i don't know why.
>> >>
>> >> The first query is using view, it procuce a correct result (2 rows),
>> >> the second query is using inline view (the inline view defination is
>> >> exactly the same as the view) but the result is wrong (4 rows).
>> >>
>> >>From the execution plan, the second query perform the join with the
>> >> inline view twist which is not correct.
>> >>
>> >> Please help.
>> >> JCVoon
>> >>
>> >>
>> >>
>> >>
>> >> -- Join with view
>> >> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> >> Trx.PhysicalFlag) AS BaseQty
>> >> FROM WmsStockLedger Trx
>> >>
>> >> LEFT JOIN
>> >> (
>> >> SELECT * From view_PutHold
>> >> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
>> >> ) PutHold
>> >> ON PutHold.CompanyCode = Trx.CompanyCode
>> >> And PutHold.BranchCode = Trx.BranchCode
>> >> And PutHold.WONo = Trx.TxnNo
>> >> And PutHold.ProductCode = Trx.ProductCode
>> >> And PutHold.TallyInNo = Trx.TallyInNo
>> >> WHERE
>> >> Trx.CompanyCode='HQ'
>> >> And Trx.BranchCode='HQ'
>> >> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> >> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> >> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> >> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> >>
>> >> -- Join with inline view
>> >> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> >> Trx.PhysicalFlag) AS BaseQty
>> >> FROM WmsStockLedger Trx
>> >> LEFT JOIN
>> >> (
>> >> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> >> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> >> FROM WmsPutawayHed Hd
>> >> INNER JOIN WmsPutawayDet Dt
>> >> ON Dt.CompanyCode = Hd.CompanyCode
>> >> And Dt.BranchCode = Hd.BranchCode
>> >> And Dt.WoNo = Hd.WoNo
>> >> And Dt.Completed = 1
>> >> INNER JOIN WmsTallyInHed Ti
>> >> ON Ti.CompanyCode = Hd.CompanyCode
>> >> And Ti.BranchCode = Hd.BranchCode
>> >> And Ti.TallyInNo = Hd.TallyInNo
>> >> WHERE Hd.CompanyCode = 'HQ'
>> >> And Hd.BranchCode = 'HQ'
>> >> ) PutHold
>> >>
>> >> ON PutHold.CompanyCode = Trx.CompanyCode
>> >> And PutHold.BranchCode = Trx.BranchCode
>> >> And PutHold.WONo = Trx.TxnNo
>> >> And PutHold.ProductCode = Trx.ProductCode
>> >> And PutHold.TallyInNo = Trx.TallyInNo
>> >> WHERE
>> >> Trx.CompanyCode='HQ'
>> >> And Trx.BranchCode='HQ'
>> >> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> >> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> >> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> >> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> >>
>> >>
>> >> --Here is the DDL
>> >>
>> >> CREATE TABLE [dbo].[WmsPutawayDet] (
>> >> [CompanyCode] [varchar] (2) NOT NULL ,
>> >> [BranchCode] [varchar] (2) NOT NULL ,
>> >> [WoNo] [varchar] (10) NOT NULL ,
>> >> [ProductCode] [varchar] (10) NOT NULL ,
>> >> [LocationCode] [varchar] (10) NOT NULL ,
>> >> [Qty] [numeric](18, 0) NOT NULL ,
>> >> [Completed] [bit] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[WmsPutawayHed] (
>> >> [CompanyCode] [varchar] (2) NOT NULL ,
>> >> [BranchCode] [varchar] (2) NOT NULL ,
>> >> [WoNo] [varchar] (10) NOT NULL ,
>> >> [TallyInNo] [varchar] (10) NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[WmsTallyInHed] (
>> >> [CompanyCode] [varchar] (2) NOT NULL ,
>> >> [BranchCode] [varchar] (2) NOT NULL ,
>> >> [TallyInNo] [varchar] (10) NOT NULL ,
>> >> [PrincipalCode] [varchar] (10) NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[wmsStockLedger] (
>> >> [CompanyCode] [varchar] (2) NOT NULL ,
>> >> [BranchCode] [varchar] (2) NOT NULL ,
>> >> [ProductCode] [varchar] (10) NOT NULL ,
>> >> [LocationCode] [varchar] (10) NOT NULL ,
>> >> [TallyInNo] [varchar] (10) NOT NULL ,
>> >> [PrincipalCode] [varchar] (10) NOT NULL ,
>> >> [TxnNo] [varchar] (10) NOT NULL ,
>> >> [BaseQuantity] [numeric](18, 0) NOT NULL ,
>> >> [PhysicalFlag] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[WmsPutawayDet] ADD
>> >> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
>> >> (
>> >> [CompanyCode],
>> >> [BranchCode],
>> >> [WoNo],
>> >> [ProductCode],
>> >> [LocationCode]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[WmsPutawayHed] ADD
>> >> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
>> >> (
>> >> [CompanyCode],
>> >> [BranchCode],
>> >> [WoNo]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[WmsTallyInHed] ADD
>> >> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
>> >> (
>> >> [CompanyCode],
>> >> [BranchCode],
>> >> [TallyInNo]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[wmsStockLedger] ADD
>> >> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
>> >> (
>> >> [CompanyCode],
>> >> [BranchCode],
>> >> [ProductCode],
>> >> [LocationCode],
>> >> [TallyInNo],
>> >> [PrincipalCode],
>> >> [TxnNo]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> create view view_PutHold as
>> >> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> >> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
>> >> FROM WmsPutawayHed Hd
>> >> INNER JOIN WmsPutawayDet Dt
>> >> ON Dt.CompanyCode = Hd.CompanyCode
>> >> And Dt.BranchCode = Hd.BranchCode
>> >> And Dt.WoNo = Hd.WoNo
>> >> And Dt.Completed = 1
>> >> INNER JOIN WmsTallyInHed Ti
>> >> ON Ti.CompanyCode = Hd.CompanyCode
>> >> And Ti.BranchCode = Hd.BranchCode
>> >> And Ti.TallyInNo = Hd.TallyInNo
>> >> GO
>> >>
>> >> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
>> >> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
>> >>
>> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
>> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
>> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
>> >> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
>> >>
>> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
>> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
>> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
>> >> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
>> >>
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
>> >> INSERT INTO [WmsStockLedger]
>> >> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>> >>
>> >
>> >
>>|||Roji. P. Thomas:
Thanks for your help.
Comment the HAVING clause will also return 2 rows.
Please update me if u found any thing.
Regards
JCVoon|||I observed that commenting the line
will solve the problem.
So here is a workaround, other than using the view.
SELECT PrincipalCode, ProductCode, BaseQty
FROM
(
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
AND (LEFT(Trx.TXNNo,3) = 'OP-'
OR PutHold.Completed = 1)
GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
WHERE BaseQty > 0
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> The problem appears to be in the section
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> If you just comment that and run the query, the result is correct.
> Also if you comment the
> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
> line, the query gives the correct result.
> I am still not sure whether its a known bug. I will update you once I have
> more info.
> BTW thankls for posting the DDL.
>
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag)
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>>I am looking into this issue.
>> Looks like a problem with SQL server itself.
>> The query works as expected in SQL 2005 (returns only 2 rows in both
>> cases)
>> --
>> Roji. P. Thomas
>> http://toponewithties.blogspot.com
>>
>> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
>> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>> Hi:
>> I've a problem with the following querys, These two query is suppose to
>> produce a same result
>> but it is not, i don't know why.
>> The first query is using view, it procuce a correct result (2 rows),
>> the second query is using inline view (the inline view defination is
>> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
>> inline view twist which is not correct.
>> Please help.
>> JCVoon
>>
>>
>> -- Join with view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> SELECT * From view_PutHold
>> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> -- Join with inline view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> WHERE Hd.CompanyCode = 'HQ'
>> And Hd.BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>>
>> --Here is the DDL
>> CREATE TABLE [dbo].[WmsPutawayDet] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [Qty] [numeric](18, 0) NOT NULL ,
>> [Completed] [bit] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsPutawayHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsTallyInHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[wmsStockLedger] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL ,
>> [TxnNo] [varchar] (10) NOT NULL ,
>> [BaseQuantity] [numeric](18, 0) NOT NULL ,
>> [PhysicalFlag] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayDet] ADD
>> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo],
>> [ProductCode],
>> [LocationCode]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayHed] ADD
>> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsTallyInHed] ADD
>> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [TallyInNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[wmsStockLedger] ADD
>> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [ProductCode],
>> [LocationCode],
>> [TallyInNo],
>> [PrincipalCode],
>> [TxnNo]
>> ) ON [PRIMARY]
>> GO
>> create view view_PutHold as
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> GO
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>>
>|||John
Thanks for the reply.
I'll rewrite my original query to the way u shown me.
Regards
JCVoon|||>I observed that commenting the line
>will solve the problem
Read
I observed that commenting the line
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
will solve the problem
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uqT$jHqFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I observed that commenting the line
> will solve the problem.
> So here is a workaround, other than using the view.
> SELECT PrincipalCode, ProductCode, BaseQty
> FROM
> (
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> AND (LEFT(Trx.TXNNo,3) = 'OP-'
> OR PutHold.Completed = 1)
> GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
> WHERE BaseQty > 0
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
>> The problem appears to be in the section
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> If you just comment that and run the query, the result is correct.
>> Also if you comment the
>> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
>> line, the query gives the correct result.
>> I am still not sure whether its a known bug. I will update you once I
>> have more info.
>> BTW thankls for posting the DDL.
>>
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag)
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> WHERE Hd.CompanyCode = 'HQ'
>> And Hd.BranchCode = 'HQ') PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> --
>> Roji. P. Thomas
>> http://toponewithties.blogspot.com
>>
>> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
>> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>>I am looking into this issue.
>> Looks like a problem with SQL server itself.
>> The query works as expected in SQL 2005 (returns only 2 rows in both
>> cases)
>> --
>> Roji. P. Thomas
>> http://toponewithties.blogspot.com
>>
>> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
>> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>> Hi:
>> I've a problem with the following querys, These two query is suppose to
>> produce a same result
>> but it is not, i don't know why.
>> The first query is using view, it procuce a correct result (2 rows),
>> the second query is using inline view (the inline view defination is
>> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
>> inline view twist which is not correct.
>> Please help.
>> JCVoon
>>
>>
>> -- Join with view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> SELECT * From view_PutHold
>> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> -- Join with inline view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> WHERE Hd.CompanyCode = 'HQ'
>> And Hd.BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>>
>> --Here is the DDL
>> CREATE TABLE [dbo].[WmsPutawayDet] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [Qty] [numeric](18, 0) NOT NULL ,
>> [Completed] [bit] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsPutawayHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsTallyInHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[wmsStockLedger] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL ,
>> [TxnNo] [varchar] (10) NOT NULL ,
>> [BaseQuantity] [numeric](18, 0) NOT NULL ,
>> [PhysicalFlag] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayDet] ADD
>> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo],
>> [ProductCode],
>> [LocationCode]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayHed] ADD
>> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsTallyInHed] ADD
>> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [TallyInNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[wmsStockLedger] ADD
>> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [ProductCode],
>> [LocationCode],
>> [TallyInNo],
>> [PrincipalCode],
>> [TxnNo]
>> ) ON [PRIMARY]
>> GO
>> create view view_PutHold as
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> GO
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>>
>>
>|||Here is a repro for others looking into the problem.
(SQL Server 2000 SP4)
The query without the last line (HAVING ) returns 3 rows, which is correct.
With HAVING it returns 6 rows and the result is incorrect
Use Pubs
GO
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Yes. this is a known bug.
http://support.microsoft.com/kb/308458/en-us
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ezkvteqFGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Here is a repro for others looking into the problem.
> (SQL Server 2000 SP4)
> The query without the last line (HAVING ) returns 3 rows, which is
> correct.
> With HAVING it returns 6 rows and the result is incorrect
>
> Use Pubs
> GO
> SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
> FROM Titles T
> LEFT JOIN
> (Select NULL) X (pub_id)
> ON X.pub_id = T.pub_id
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.pub_id, T.type
> HAVING SUM(T.price * 1) > 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>> Hi:
>> I've a problem with the following querys, These two query is suppose to
>> produce a same result
>> but it is not, i don't know why.
>> The first query is using view, it procuce a correct result (2 rows),
>> the second query is using inline view (the inline view defination is
>> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
>> inline view twist which is not correct.
>> Please help.
>> JCVoon
>>
>>
>> -- Join with view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> SELECT * From view_PutHold
>> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>> -- Join with inline view
>> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
>> Trx.PhysicalFlag) AS BaseQty
>> FROM WmsStockLedger Trx
>> LEFT JOIN
>> (
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> WHERE Hd.CompanyCode = 'HQ'
>> And Hd.BranchCode = 'HQ'
>> ) PutHold
>> ON PutHold.CompanyCode = Trx.CompanyCode
>> And PutHold.BranchCode = Trx.BranchCode
>> And PutHold.WONo = Trx.TxnNo
>> And PutHold.ProductCode = Trx.ProductCode
>> And PutHold.TallyInNo = Trx.TallyInNo
>> WHERE
>> Trx.CompanyCode='HQ'
>> And Trx.BranchCode='HQ'
>> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
>> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
>> GROUP BY Trx.PrincipalCode, Trx.ProductCode
>> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>>
>> --Here is the DDL
>> CREATE TABLE [dbo].[WmsPutawayDet] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [Qty] [numeric](18, 0) NOT NULL ,
>> [Completed] [bit] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsPutawayHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [WoNo] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[WmsTallyInHed] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[wmsStockLedger] (
>> [CompanyCode] [varchar] (2) NOT NULL ,
>> [BranchCode] [varchar] (2) NOT NULL ,
>> [ProductCode] [varchar] (10) NOT NULL ,
>> [LocationCode] [varchar] (10) NOT NULL ,
>> [TallyInNo] [varchar] (10) NOT NULL ,
>> [PrincipalCode] [varchar] (10) NOT NULL ,
>> [TxnNo] [varchar] (10) NOT NULL ,
>> [BaseQuantity] [numeric](18, 0) NOT NULL ,
>> [PhysicalFlag] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayDet] ADD
>> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo],
>> [ProductCode],
>> [LocationCode]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsPutawayHed] ADD
>> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [WoNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[WmsTallyInHed] ADD
>> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [TallyInNo]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[wmsStockLedger] ADD
>> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
>> (
>> [CompanyCode],
>> [BranchCode],
>> [ProductCode],
>> [LocationCode],
>> [TallyInNo],
>> [PrincipalCode],
>> [TxnNo]
>> ) ON [PRIMARY]
>> GO
>> create view view_PutHold as
>> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
>> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
>> FROM WmsPutawayHed Hd
>> INNER JOIN WmsPutawayDet Dt
>> ON Dt.CompanyCode = Hd.CompanyCode
>> And Dt.BranchCode = Hd.BranchCode
>> And Dt.WoNo = Hd.WoNo
>> And Dt.Completed = 1
>> INNER JOIN WmsTallyInHed Ti
>> ON Ti.CompanyCode = Hd.CompanyCode
>> And Ti.BranchCode = Hd.BranchCode
>> And Ti.TallyInNo = Hd.TallyInNo
>> GO
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
>> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
>> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
>> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
>> INSERT INTO [WmsStockLedger]
>> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Here is the best fix so far.
Just change LEFT(Trx.TXNNo,3) with SUBSTRING(Trx.TXNNo,1,3)
That seems to prevent the otimizer from doing the incorrect cross join.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity*
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (SUBSTRING(Trx.TXNNo,1,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>>From the execution plan, the second query perform the join with the
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Roji. P. Thomas:
Thank you so much.
I've tested my original query on SQL2000PE with SP4, the problem still
exist.
With your work around my query working fine now.
Just wonder how do u know change LEFT(Trx.TXNNo,3) with
SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
Thanks
JCVoon|||JCVoon,
I just played around with it and found that neither RIGHT or SUBSTRING
function causes this problem.
Only the usage of LEFT function alongwith an OUTER JOIN, DERIVED TABLE,
GROUP BY , HAVING and an AGGREGATE function causes the query optimizer to go
for an incorrect plan that causes a CROSS JOIN.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1137040431.065223.265280@.g43g2000cwa.googlegroups.com...
> Roji. P. Thomas:
> Thank you so much.
> I've tested my original query on SQL2000PE with SP4, the problem still
> exist.
> With your work around my query working fine now.
> Just wonder how do u know change LEFT(Trx.TXNNo,3) with
> SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
> Thanks
> JCVoon
>|||Hi,
I believe the problem was not caused by CROSS JOIN. We can simulate the
CROSS JOIN situation by converting the LEFT JOIN to CROSS JOIN right?
The result will be different, the GROUP BY will still work.
So, I believe it was actually fix in SP2 as stated in the article.
I believe the problem is much similar to the situation in the below
article:
http://www.support.microsoft.com/kb/884864
The article above was fixed in SP3 or 3a. Both problems are the same I
believe whereby both are caused by "Table / Lazy Spool" selected by the
query optimizer, but the problem JCVoon posted was slightly different
in which where the table spool happened for the whole query, and
returns duplicates records, and finally INNER JOIN without condition to
the main result... or something like that, you can see it in the
"Execution Plan".
I notice that when I put the whole query as a sub-query, the optimizer
does not use "Table / Lazy Spool"?
E.g.
Use Pubs
GO
Select * FROM(
SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
BasePrice
FROM Titles T
LEFT JOIN
(Select pub_id FROM publishers) X
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.Title, T.pub_id, T.type
HAVING SUM(IsNull(T.price, 0) * 1) > 0
) X
I also notice a workaround the problem to prevent the "Table / Lazy
Spool" from happening, which in a way, can be said as FORCING the query
optimizer to use a different plan.
E.g.
Use Pubs
GO
SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
BasePrice
FROM Titles T
LEFT JOIN
(Select pub_id FROM publishers) X
ON X.pub_id = T.pub_id
LEFT JOIN
(Select TOP 0 pub_id FROM titleview) M
ON 1=1
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.Title, T.pub_id, T.type
HAVING SUM(IsNull(T.price, 0) * 1) > 0
Notice I added this line:
LEFT JOIN
(Select TOP 0 pub_id FROM titleview) M
ON 1=1
I notice that when we use a VIEW as the sub-query/derived table, the
query optimizer does not use the "Table / Lazy Spool", thus I included
this line so it will choose so called the best plan.
Anyway, is this a new bug dicovered in SQL Server 2000? Similar to Kb
884864? But I think there won't be any Service Pack release for SQL
Server 2000 anymore right?
Roji. P. Thomas wrote:
> JCVoon,
> I just played around with it and found that neither RIGHT or SUBSTRING
> function causes this problem.
> Only the usage of LEFT function alongwith an OUTER JOIN, DERIVED TABLE,
> GROUP BY , HAVING and an AGGREGATE function causes the query optimizer to go
> for an incorrect plan that causes a CROSS JOIN.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1137040431.065223.265280@.g43g2000cwa.googlegroups.com...
> > Roji. P. Thomas:
> >
> > Thank you so much.
> >
> > I've tested my original query on SQL2000PE with SP4, the problem still
> > exist.
> > With your work around my query working fine now.
> >
> > Just wonder how do u know change LEFT(Trx.TXNNo,3) with
> > SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
> >
> > Thanks
> > JCVoon
> >|||Andy,
See my comments inline.
> I believe the problem was not caused by CROSS JOIN. We can simulate the
> CROSS JOIN situation by converting the LEFT JOIN to CROSS JOIN right?
> The result will be different, the GROUP BY will still work.
I am almost sure that the problem caused because of the incorrect CROSS
JOIN. If you apply
a CROSS JOIN with one set without the HAVING clause and one set with the
HAVING clause,
you will get the exact same results and execution plan. (See below)
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.Title,3)= 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
SELECT T.pub_id, T.type,X.BasePrice FROM
(
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T --CROSS JOIN Titles R
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE SUBSTRING(T.Title,1,3)= 'The'
--AND LEFT(R.Title,3)= 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
)T CROSS JOIN (
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T --CROSS JOIN Titles R
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE SUBSTRING(T.Title,1,3)= 'The'
--AND LEFT(R.Title,3)= 'The'
GROUP BY T.pub_id, T.type
--HAVING SUM(T.price * 1) > 0
)X
--Execution Plan
[First Query]
(1 row(s) affected)
StmtText
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1018]=0) then NULL else
[Expr1019]))
| |--Stream Aggregate(GROUP BY:([T].[type], [T].[pub_id])
DEFINE:([Expr1018]=COUNT_BIG([T].[price]*1.00),
[Expr1019]=SUM([T].[price]*1.00)))
| |--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id] ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
|--Table Spool
|--Filter(WHERE:([Expr1002]>0.00))
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1020]=0) then
NULL else [Expr1021]))
|--Stream Aggregate(GROUP BY:([T].[type],
[T].[pub_id]) DEFINE:([Expr1020]=COUNT_BIG([T].[price]*1.00),
[Expr1021]=SUM([T].[price]*1.00)))
|--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id]
ASC))
|--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
(11 row(s) affected)
[Second Query]
(1 row(s) affected)
StmtText
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1018]=0) then NULL else
[Expr1019]))
| |--Stream Aggregate(GROUP BY:([T].[type], [T].[pub_id])
DEFINE:([Expr1018]=COUNT_BIG([T].[price]*1.00),
[Expr1019]=SUM([T].[price]*1.00)))
| |--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id] ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
|--Table Spool
|--Filter(WHERE:([Expr1002]>0.00))
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1020]=0) then
NULL else [Expr1021]))
|--Stream Aggregate(GROUP BY:([T].[type],
[T].[pub_id]) DEFINE:([Expr1020]=COUNT_BIG([T].[price]*1.00),
[Expr1021]=SUM([T].[price]*1.00)))
|--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id]
ASC))
|--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
(11 row(s) affected)
>I believe the problem is much similar to the situation in the below
>article:
>http://www.support.microsoft.com/kb/884864
The title of the KB article says, "You may receive incorrect results when
you run a query in SQL Server 2000".
Well, I have nothing to say about that. But the article describes the cause
of the problem as "the execution plan that
SQL Server 2000 chooses uses a multi-parent spool that has the primary and
the secondary spools on the inner side of a nested loop join."
But for the problem at hand, we have only one TableSpool/Lazy Spool on the
inner side of the join.
> I notice that when I put the whole query as a sub-query, the optimizer
> does not use "Table / Lazy Spool"?
Yes. That was one of the workaround suggested before.
>Anyway, is this a new bug discovered in SQL Server 2000?
I think So.
> But I think there won't be any Service Pack release for SQL
>Server 2000 anymore right?
I don't know. But I already reported this in a private NG.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Andy214" <Andy214@.gmail.com> wrote in message
news:1137137578.286943.77370@.z14g2000cwz.googlegroups.com...
> Hi,
> I believe the problem was not caused by CROSS JOIN. We can simulate the
> CROSS JOIN situation by converting the LEFT JOIN to CROSS JOIN right?
> The result will be different, the GROUP BY will still work.
> So, I believe it was actually fix in SP2 as stated in the article.
> I believe the problem is much similar to the situation in the below
> article:
> http://www.support.microsoft.com/kb/884864
> The article above was fixed in SP3 or 3a. Both problems are the same I
> believe whereby both are caused by "Table / Lazy Spool" selected by the
> query optimizer, but the problem JCVoon posted was slightly different
> in which where the table spool happened for the whole query, and
> returns duplicates records, and finally INNER JOIN without condition to
> the main result... or something like that, you can see it in the
> "Execution Plan".
> I notice that when I put the whole query as a sub-query, the optimizer
> does not use "Table / Lazy Spool"?
> E.g.
> Use Pubs
> GO
> Select * FROM(
> SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
> BasePrice
> FROM Titles T
> LEFT JOIN
> (Select pub_id FROM publishers) X
> ON X.pub_id = T.pub_id
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.Title, T.pub_id, T.type
> HAVING SUM(IsNull(T.price, 0) * 1) > 0
> ) X
> I also notice a workaround the problem to prevent the "Table / Lazy
> Spool" from happening, which in a way, can be said as FORCING the query
> optimizer to use a different plan.
> E.g.
> Use Pubs
> GO
> SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
> BasePrice
> FROM Titles T
> LEFT JOIN
> (Select pub_id FROM publishers) X
> ON X.pub_id = T.pub_id
> LEFT JOIN
> (Select TOP 0 pub_id FROM titleview) M
> ON 1=1
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.Title, T.pub_id, T.type
> HAVING SUM(IsNull(T.price, 0) * 1) > 0
> Notice I added this line:
> LEFT JOIN
> (Select TOP 0 pub_id FROM titleview) M
> ON 1=1
> I notice that when we use a VIEW as the sub-query/derived table, the
> query optimizer does not use the "Table / Lazy Spool", thus I included
> this line so it will choose so called the best plan.
> Anyway, is this a new bug dicovered in SQL Server 2000? Similar to Kb
> 884864? But I think there won't be any Service Pack release for SQL
> Server 2000 anymore right?
> Roji. P. Thomas wrote:
>> JCVoon,
>> I just played around with it and found that neither RIGHT or SUBSTRING
>> function causes this problem.
>> Only the usage of LEFT function alongwith an OUTER JOIN, DERIVED TABLE,
>> GROUP BY , HAVING and an AGGREGATE function causes the query optimizer to
>> go
>> for an incorrect plan that causes a CROSS JOIN.
>> --
>> Regards
>> Roji. P. Thomas
>> http://toponewithties.blogspot.com
>> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
>> news:1137040431.065223.265280@.g43g2000cwa.googlegroups.com...
>> > Roji. P. Thomas:
>> >
>> > Thank you so much.
>> >
>> > I've tested my original query on SQL2000PE with SP4, the problem still
>> > exist.
>> > With your work around my query working fine now.
>> >
>> > Just wonder how do u know change LEFT(Trx.TXNNo,3) with
>> > SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
>> >
>> > Thanks
>> > JCVoon
>> >
>|||Roji. P. Thomas,
For your CROSS JOIN, you can replace it with LEFT JOIN (giving criteria
ON 1=1), it will produce the same result...
>The title of the KB article says, "You may receive incorrect results when
>you run a query in SQL Server 2000".
>Well, I have nothing to say about that. But the article describes the cause
>of the problem as "the execution plan that
>SQL Server 2000 chooses uses a multi-parent spool that has the primary and
>the secondary spools on the inner side of a nested loop join."
>But for the problem at hand, we have only one TableSpool/Lazy Spool on the
>inner side of the join.
Well, that's what I mean by similar but slightly different. Um... what
I mean was actually the problem caused by "Table / Lazy Spool".
Somehow, somewhere, the query optimizer chooses the "Table / Lazy
Spool"...
Well, like another example I posted, where I used another JOIN to
forced the query optimizer to use a different plan by:
LEFT JOIN
(Select TOP 0 title from titleview)
ON 1=1
It changes it query plan...
>Yes. That was one of the workaround suggested before.
But how come when put under sub-query, the optimizer uses a different
plan? Hm...
One more thing, I notice that in the execution plan, the LEFT()
function was actually interprested as SUBSTRING()... So... how come
when used LEFT and SUBSTRING() have differences for the above case?
Hm...|||Andy,
> For your CROSS JOIN, you can replace it with LEFT JOIN (giving criteria
> ON 1=1), it will produce the same result...
An INNER or OUTER JOIN based on a dummy condition like 1=1 is equivalent to
CROSS JOIN,
because every row in the left set matches to every row in the right set.
All three of the following query will produce the same result.
SELECT *
FROM
(SELECT 1 AS col1
UNION ALL SELECT 2) A
INNER JOIN
(SELECT 'A' as col2
UNION ALL SELECT 'B') B
ON 1 =1
SELECT *
FROM
(SELECT 1 AS col1
UNION ALL SELECT 2) A
LEFT JOIN
(SELECT 'A' as col2
UNION ALL SELECT 'B') B
ON 1 =1
SELECT *
FROM
(SELECT 1 AS col1
UNION ALL SELECT 2) A
CROSS JOIN
(SELECT 'A' as col2
UNION ALL SELECT 'B') B
> I mean was actually the problem caused by "Table / Lazy Spool".
> Somehow, somewhere, the query optimizer chooses the "Table / Lazy
> Spool"...
That's correct. Once we get rid of the Table Spool, the query return
correct results.
> One more thing, I notice that in the execution plan, the LEFT()
> function was actually interpreted as SUBSTRING()... So... how come
> when used LEFT and SUBSTRING() have differences for the above case?
You are right. But that happens after the optimizer devices the wrong plan.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com|||Raji. P. Thomas,
>That's correct. Once we get rid of the Table Spool, the query return
correct results.
Is there a way to prevent the query optimizer to use the "Table / Lazy
Spool"? Instead of forcing it with LEFT JOIN To A Derived View? I mean
as an option or something. If it can do it by force, it should be able
to do without the derived view. Or perhaps need a fix from MS?
Actually, do you have any idea why is there the need for "Table / Lazy
Spool" when it produces wrong results?

Monday, March 19, 2012

Joining table to itself

Joining multiple tables in a view.

Joining multiple tables in a view.

Friday, March 9, 2012

Joined view does not contain all colums

Join using like?

Wednesday, March 7, 2012

JOIN Tables in 2 Databases

JOIN Tables in 2 Databases

JOIN Tables in 2 Databases

join stored procedure and view

join stored procedure and view

Friday, February 24, 2012

Join query with view and inline view produced a different result

Join query with view and inline view produced a different result

join or subselect

Blog Archive

About Me