Login Error

Hi, I had a problem with the login function. The login function issupposed to authenicate the user email and password and if the both ofthem tally with each other, they can then be let into the other pages.But for my codes, the error is that even if the password and email doesnot tally, it can still procceed. Can someone pls help? Thanks! :)
ALTER PROCEDURE spLogin(@.MemEmail nvarchar(80), @.MemPwd VARCHAR(15))
AS
BEGIN
IF NOT EXISTS
(SELECT MemEmail
FROM DasMember
WHERE MemPwd = @.MemPwd)
RETURN -600
IF NOT EXISTS
(SELECT MemPwd
FROM DasMember
WHERE MemEmail = @.MemEmail)
RETURN -601
END
SELECT MemEmail, MemPwd
FROM DasMember
WHERE (MemPwd = @.MemPwd) and (MemEmail = @.MemEmail)
IF @.@.ERROR <> 0
RETURN @.@.ERROR
RETURN
DECLARE @.status int
EXEC @.status = spLogin 'jane@.yahoo.com.sg', 'jane'
SELECT 'Status' = @.status
It's alright. I found the error.. Thanks and sorry to trouble you guys..

Logical XOR operator in T-SQL (SQL Server 2000)

Hi all....

I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it?

How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function :

CREATEFUNCTION XOR

(

-- Add the parameters for the function here

@.A BIT,

@.B BIT

)

RETURNS BIT

AS

BEGIN

RETURNNOT @.A AND @.B OR @.A +NOT @.B

END

But it didn't compile.

How can I pass boolean values as parameters? any other hint?

Thanks

Jaime

There is an XOR operator -- it is the caret (^). In the Books On-line look up operators(symbols) bitwise.|||

As you said, that is a bitwise operator, not a logical one.

For example, using the ^ I couldn't do something like :

SELECT *

FROM TABLE

WHERE (A = 10) ^ (B = 5)

which should return records when either A = 10 or B = 5, but if conditions are both satisfied or both not satisfied, I don't want records to be returned.

Another suggestion to accomplish that? Of course this is a simplified scenario. In case of 2 conditions I can do perfectly :

WHERE (A <> 10 AND B = 5) OR (A = 10 AND B <> 5)

The only solution I see is to implement a XOR function myself and to nest them, but I couldn't (or don't know how) pass a boolean value as a function parameter. If you know how, please tell me.

Thanks

Jaime

|||

There is no boolean data type in TSQL and there are only few system built-ins like CONTAINS that can be used directly in a WHERE clause. So you need to use the XOR operator and test for the return value of expression like:

WHERE (case A when 10 then 1 else 0 end)^(case B when 5 then 1 else 0 end) = 1

|||? The reason your UDF does not work is that BIT is not the same as BOOLEAN. There is no Boolean type in SQL Server, so you can't do: SELECT * FROM Table WHERE Column Instead, (assuming that Column is typed as BIT) you have to do: SELECT * FROM Table WHERE Column = 1 Anyway, it's quite easy to do XOR inline: SELECT * FROM Table WHERE (Column1 = 1 AND Column2 <> 1) OR (Column1 <> 1 AND Column2 = 1) -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jaime Stuardo@.discussions.microsoft.com> wrote in message news:b2649891-1305-4b27-a074-5b0a896807e8@.discussions.microsoft.com... Hi all.... I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it? How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function : CREATE FUNCTION XOR ( -- Add the parameters for the function here @.A BIT, @.B BIT ) RETURNS BIT AS BEGIN RETURN NOT @.A AND @.B OR @.A + NOT @.B END But it didn't compile. How can I pass boolean values as parameters? any other hint? Thanks Jaime

logic of sum() with joins and using query hint

hi everybody
I have a question about the sum() function. when I join two tabeles and one
of them is the main table which I used in the from statement, sum function I
used for the joined table is giving the sum incorrectly(it is governing time
s
the other joined tabele). how can i eleminate the problem? do I have to use
query hint or someting. if so how?
the query is the basis of a fifo report. I didnt want to use a cursor and so
I wrote such a query. I solved the problem with UDFs but i want to learn the
logic and how to use query hint for sum function
id is primary key for both tables
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
--sample data
insert into distribute (id,date_distribute,product,quantity) values
(51,'2005-01-01','aaa',10)
insert into distribute (id,date_distribute,product,quantity) values
(52,'2005-01-04','aaa',13)
insert into distribute (id,date_distribute,product,quantity) values
(53,'2005-01-05','aaa',3)
insert into distribute (id,date_distribute,product,quantity) values
(54,'2005-01-06','aaa',-2)
insert into distribute (id,date_distribute,product,quantity) values
(55,'2005-01-07','aaa',8)
insert into distribute (id,date_distribute,product,quantity) values
(56,'2005-01-08','aaa',45)
insert into distribute (id,date_distribute,product,quantity) values
(57,'2005-01-10','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(11,'2005-01-01','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(12,'2005-01-03','aaa',20)
insert into [order] (id,date_order,product,quantity) values
(13,'2005-01-05','aaa',30)
insert into [order] (id,date_order,product,quantity) values
(14,'2005-01-08','aaa',15)
insert into [order] (id,date_order,product,quantity) values
(15,'2005-01-09','aaa',10)
--query
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
isnull(sum(d2.quantity),0)as forobservingdist,
isnull(sum(o2.quantity),0)as forobservingord
from [order] o1
left join [order] o2 on o1.id>=o2.id and o1.product=o2.product
left join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
left join Distribute d2 on d2.id<=d1.id and d2.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
the query below has the same logic with the above query. I used UDFs for the
sum functions.
when you run the queries, forobservingord column must be the same as
forobservingord in the results of the query below
CREATE FUNCTION getorderdogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(o2.quantity) from [order] o2 where o2.id<=@.id and
o2.product=@.product
RETURN @.sum
END
go
CREATE FUNCTION getdistributedogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(d2.quantity) from Distribute d2 where d2.id<=@.id and
d2.product=@.product
RETURN isnull(@.sum,0)
END
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.productYou can achieve the same results (with better performance) by using
subqueries instead of UDF-s:
select o1.id, o1.date_order, o1.product,o1.quantity,
d1.id as Distribute_id, d1.date_distribute, (
select sum(d2.quantity) from Distribute d2
where d2.id<=d1.id and d2.product=d1.product
) as forobservingdist, (
select sum(o2.quantity) from [order] o2
where o2.id<=o1.id and o2.product=o1.product
) as forobservingord
from dbo.[order] o1
left outer join Distribute d1
on o1.date_order<=d1.date_distribute and o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
Razvan|||tnx Razvan sure I didnt think this:))
do you have any info about using query hint works like that?
also the last form of the query is like that
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
then
isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribute
dogan(d1.id,d1.product))
when
d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
then
(o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.
id,d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))>o1.quantity
then
o1.quantity
when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))<0
then
0
else
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))
end as remainingorder,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having
((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produc
t))+isnull(d1.quantity,0)>0
and
o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.i
d,d1.product)))
or isnull(d1.quantity,0)=0
I changed the UDFs with subqueries and its working. I looked at the query
execution plan and it looks more simple with the udfs. are you sure this wil
l
work with better performance? probably you are:))
also plan of the query with subqueries shows many hash matches. Can't I use
query hint making hash matches with left join?
thanks again
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
then
isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
when
d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
then
(o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))>o1.quantity
then
o1.quantity
when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))<0
then
0
else
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
end as remainingorder,
(select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
d2.product=d1.product)as forobservingdist,
(select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
and o1.quantity>((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
or isnull(d1.quantity,0)=0
"Razvan Socol" wrote:

> You can achieve the same results (with better performance) by using
> subqueries instead of UDF-s:
> select o1.id, o1.date_order, o1.product,o1.quantity,
> d1.id as Distribute_id, d1.date_distribute, (
> select sum(d2.quantity) from Distribute d2
> where d2.id<=d1.id and d2.product=d1.product
> ) as forobservingdist, (
> select sum(o2.quantity) from [order] o2
> where o2.id<=o1.id and o2.product=o1.product
> ) as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1
> on o1.date_order<=d1.date_distribute and o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
> Razvan
>|||> do you have any info about using query hint works like that?
There are no query hints that modify the results; the hints are used
only for optimizations. For informations about hints, see the "query
hints" topic in Books Online:
http://msdn.microsoft.com/library/e..._qd_03_8upf.asp

> I looked at the query execution plan and it looks more simple with the udfs.[/colo
r]
The execution plan for a query that calls multi-statement UDF-s (scalar
or table-valued) do not contain the cost of the statements contained in
the UDF. Only in-line table-valued UDF-s are expaded in the execution
plan of the calling query.
> are you sure this will work with better performance? probably you are:))
To be sure, test it yourself using Profiler or using something like
this:
DECLARE @.t datetime
SET @.t=GETDATE()
SELECT ...
PRINT CONVERT(varchar(10),DATEDIFF(ms,@.t,GETDA
TE()))+' ms'

> also plan of the query with subqueries shows many hash matches.
> Can't I use query hint making hash matches with left join?
SQL Server can execute joins in one of three ways: nested loops, merge
or hash. These ways can be used for inner joins, as well as for outer
joins (left joins, right joins or full outer joins). The query
optimizer automatically selects the best way to execute a join (nested
loops, merge or hash), based on the number of rows and the available
indexes on the joined columns. If the optimizer used a hash join,
that's because this is probably the best way to execute the query in
this particular case. Adding a join hint will force SQL Server to use
nested loops joins or merge joins, but in most cases that would have an
inferior performance. A better idea would be to add indexes to the
columns that are used in the join (in this case: product and id) and
let the query optimizer choose the way it executes the query (the query
optimizer may realize that it's better not to use the index on the id
column, and use only the index on the product column, for example).
For more informations about the ways a join can be executed, see
"Advanced Query Tuning Concepts" topic in Books Online:
http://msdn.microsoft.com/library/e..._tun_1_8pv7.asp
Razvan|||thank you very much for the easy performance test code:))
I've tried it both query gives sometimes 10ms sometimes 20ms result
I know I had to test it with much more data. thanks again.
I've tried to add index as you but results did not changed.
also I tried left hash join which returns a warning about changing the plan
and didnt change anything in result set.
then I tried some unconscious synthax but these instinctly tries didnt
change the result:)
so I give up:))
thanks
"Razvan Socol" wrote:

> There are no query hints that modify the results; the hints are used
> only for optimizations. For informations about hints, see the "query
> hints" topic in Books Online:
> http://msdn.microsoft.com/library/e..._qd_03_8upf.asp
>
> The execution plan for a query that calls multi-statement UDF-s (scalar
> or table-valued) do not contain the cost of the statements contained in
> the UDF. Only in-line table-valued UDF-s are expaded in the execution
> plan of the calling query.
>
> To be sure, test it yourself using Profiler or using something like
> this:
> DECLARE @.t datetime
> SET @.t=GETDATE()
> SELECT ...
> PRINT CONVERT(varchar(10),DATEDIFF(ms,@.t,GETDA
TE()))+' ms'
>
> SQL Server can execute joins in one of three ways: nested loops, merge
> or hash. These ways can be used for inner joins, as well as for outer
> joins (left joins, right joins or full outer joins). The query
> optimizer automatically selects the best way to execute a join (nested
> loops, merge or hash), based on the number of rows and the available
> indexes on the joined columns. If the optimizer used a hash join,
> that's because this is probably the best way to execute the query in
> this particular case. Adding a join hint will force SQL Server to use
> nested loops joins or merge joins, but in most cases that would have an
> inferior performance. A better idea would be to add indexes to the
> columns that are used in the join (in this case: product and id) and
> let the query optimizer choose the way it executes the query (the query
> optimizer may realize that it's better not to use the index on the id
> column, and use only the index on the product column, for example).
> For more informations about the ways a join can be executed, see
> "Advanced Query Tuning Concepts" topic in Books Online:
> http://msdn.microsoft.com/library/e..._tun_1_8pv7.asp
> Razvan
>

logic of sum() with joining the same table

hi everybody
I have a question about the sum() function. when I join two tabeles and one
of them is the main table which I used in the from statement, sum function I
used for the joined table is giving the sum incorrectly(it is governing time
s
the other joined tabele). how can i eleminate the problem? do I have to use
query hint or someting. if so how?
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
isnull(sum(d2.quantity),0)as forobservingdist,
isnull(sum(o2.quantity),0)as forobservingord
from [order] o1
left join [order] o2 on o1.id>=o2.id and o1.product=o2.product
left join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
left join Distribute d2 on d2.id<=d1.id and d2.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
the query below has the same logic with the above query. I used UDFs for the
sum functions.
when you run the queries, forobservingord column must be the same as
forobservingord in the results of the query below
CREATE FUNCTION getorderdogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(o2.quantity) from [order] o2 where o2.id<=@.id and
o2.product=@.product
RETURN @.sum
END
go
CREATE FUNCTION getdistributedogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(d2.quantity) from Distribute d2 where d2.id<=@.id and
d2.product=@.product
RETURN isnull(@.sum,0)
END
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.productYou're description of the problem is not very clear. Firstly, neither of you
r
tables contains a primary key. This is critical to understanding why your su
m
function may be producing unexpected results. I'm assuming that the sum func
tion
is coming out too high because there are values being included multiple time
s.
In addition, your query formatting leaves something to be desired and makes
it
difficult to read the query.
Lastly, show us some sample data and result. It might help us understand wha
t
you are trying to achieve and why you have both tables joined twice in the
query.
Thomas|||hi thomas
the query is the basis of a fifo report. I didnt want to use a cursor and so
Iwrote such a query. I solved the problem with UDFs but i want to learn the
logic and how to use query hint for sum function
id is primary key for both tables
and here are the sample data:)
"In addition, your query formatting leaves something to be desired and makes
it
difficult to read the query." didnt understand that part. I would like to
change my query formating if you explain what is wrong.
tnx
insert into distribute (id,date_distribute,product,quantity) values
(51,'2005-01-01','aaa',10)
insert into distribute (id,date_distribute,product,quantity) values
(52,'2005-01-04','aaa',13)
insert into distribute (id,date_distribute,product,quantity) values
(53,'2005-01-05','aaa',3)
insert into distribute (id,date_distribute,product,quantity) values
(54,'2005-01-06','aaa',-2)
insert into distribute (id,date_distribute,product,quantity) values
(55,'2005-01-07','aaa',8)
insert into distribute (id,date_distribute,product,quantity) values
(56,'2005-01-08','aaa',45)
insert into distribute (id,date_distribute,product,quantity) values
(57,'2005-01-10','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(11,'2005-01-01','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(12,'2005-01-03','aaa',20)
insert into [order] (id,date_order,product,quantity) values
(13,'2005-01-05','aaa',30)
insert into [order] (id,date_order,product,quantity) values
(14,'2005-01-08','aaa',15)
insert into [order] (id,date_order,product,quantity) values
(15,'2005-01-09','aaa',10)
"Thomas Coleman" wrote:

> You're description of the problem is not very clear. Firstly, neither of y
our
> tables contains a primary key. This is critical to understanding why your
sum
> function may be producing unexpected results. I'm assuming that the sum fu
nction
> is coming out too high because there are values being included multiple ti
mes.
> In addition, your query formatting leaves something to be desired and make
s it
> difficult to read the query.
> Lastly, show us some sample data and result. It might help us understand w
hat
> you are trying to achieve and why you have both tables joined twice in the
> query.
>
> Thomas
>
>|||> the query is the basis of a fifo report. I didnt want to use a cursor and sod">
> Iwrote such a query. I solved the problem with UDFs but i want to learn th
e
> logic and how to use query hint for sum function
> id is primary key for both tables
> and here are the sample data:)
Ok. The only thing missing now is exactly what is it that you want? What wou
ld
the results look like?
In addition, some clarity on the meaning of the two tables would be helpful.
Is
it the case that the Order table contains products that were ordered and the
Distribute table contains products that were actually distributed? What are
the
columns "ForObservingDist" and "ForObservingOrd" supposed to denote?

> "In addition, your query formatting leaves something to be desired and mak
es
> it
> difficult to read the query." didnt understand that part. I would like to
> change my query formating if you explain what is wrong.
1. You might consider at least using Pascal casing. Pascal casing means that
the
first letter of each word in a name is capitalized. e.g. Instead of:
forobservingdist you would have: ForObservingDist. This makes it much easier
to
read.
2. Include a space on either side of an operator. So instead of: o1.id>=o2.i
d
Do this: o1.id >= o2.id
3. Include a space after a comman. So instead of o1.id,o1.date_order,o1.prod
uct.
Do this: o1.id, o1.date_order, o1.product.
4. Include some indenting in your query. I generally indent each Join clause
and
each On clause. So re-written, your query would look like:
Select o1.id, o1.date_order, o1.product, o1.quantity
, d1.id As Distribute_id
, IsNull(Sum(d2.quantity), 0) As ForObservingDist
, IsNull(Sum(o2.quantity), 0) As ForObservingOrd
From [order] o1
Left Join [order] o2
On o1.id >= o2.id
And o1.product = o2.product
Left Join Distribute d1
On o1.date_order <= d1.date_distribute
And o1.product = d1.product
Left Join Distribute d2
On d2.id <= d1.id
And d2.product = d1.product
Group By o1.id, o1.date_order, o1.product, o1.quantity
, d1.id, d1.date_distribute, d1.quantity, d1.product
Placing commas at the beginning of a line is my personal preference because
I
make fewer "missing comma" mistakes. SQL purists would want keywords like
Select, Left, Join etc. in all caps so they might write this query like so (
with
commas at the end):
SELECT o1.id, o1.date_order, o1.product, o1.quantity,
d1.id As Distribute_id,
IsNull(Sum(d2.quantity), 0) As ForObservingDist,
IsNull(Sum(o2.quantity), 0) As ForObservingOrd
FROM [order] o1
LEFT JOIN [order] o2
ON o1.id >= o2.id
AND o1.product = o2.product
LEFT JOIN Distribute d1
ON o1.date_order <= d1.date_distribute
AND o1.product = d1.product
LEFT JOIN Distribute d2
ON d2.id <= d1.id
AND d2.product = d1.product
GROUP BY o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
HTH
Thomas|||>> [query formatting] .. didnt understand that part. I would like to
change my query formating if you explain what is wrong. <<
Get a copy of my SQL PROGRAMMING STYLE. I go into painful details and
explain why you use certain formatting techniques, based on readabilty
and eye movement. Years ago when I worked for AIRMIC, I did 6 months
of full time research on this and about a year of part-time follow up.|||Hi Thomas
first of all thanks for your advices about the query style. I will do my
best:))
simply what I want is the ForObservingOrd column must be the same as in the
query with UDFs. the Query with UDFs returns the results I want. I'm sing
the solution with query hint like hash match while left joining [Orders] o2
table if possible.
I've rewrite the question and Razvan suggested to use subqueries instead of
UDFs. it is working but ly it is not what I want.
tnx again
here is the suggestion of Razvan and my reply
You can achieve the same results (with better performance) by using
subqueries instead of UDF-s:
select o1.id, o1.date_order, o1.product,o1.quantity,
d1.id as Distribute_id, d1.date_distribute, (
select sum(d2.quantity) from Distribute d2
where d2.id<=d1.id and d2.product=d1.product
) as forobservingdist, (
select sum(o2.quantity) from [order] o2
where o2.id<=o1.id and o2.product=o1.product
) as forobservingord
from dbo.[order] o1
left outer join Distribute d1
on o1.date_order<=d1.date_distribute and o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
Razvan
--
tnx Razvan sure I didnt think this:))
do you have any info about using query hint works like that?
I changed the UDFs with subqueries and its working. I looked at the query
execution plan and it looks more simple with the udfs. are you sure this wil
l
work with better performance? probably you are:))
also plan of the query with subqueries shows many hash matches. Can't I use
query hint making hash matches with left join?
thanks again
also the last form of the query is like that
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
then
isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribute
dogan(d1.id,d1.product))
when
d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
then
(o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.
id,d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))>o1.quantity
then
o1.quantity
when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))<0
then
0
else
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))
end as remainingorder,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having
((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produc
t))+isnull(d1.quantity,0)>0
and
o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.i
d,d1.product)))
or isnull(d1.quantity,0)=0
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
then
isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
when
d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
then
(o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))>o1.quantity
then
o1.quantity
when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))<0
then
0
else
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
end as remainingorder,
(select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
d2.product=d1.product)as forobservingdist,
(select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
and o1.quantity>((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
or isnull(d1.quantity,0)=0
"Thomas Coleman" wrote:

> Ok. The only thing missing now is exactly what is it that you want? What w
ould
> the results look like?
> In addition, some clarity on the meaning of the two tables would be helpfu
l. Is
> it the case that the Order table contains products that were ordered and t
he
> Distribute table contains products that were actually distributed? What ar
e the
> columns "ForObservingDist" and "ForObservingOrd" supposed to denote?
>
> 1. You might consider at least using Pascal casing. Pascal casing means th
at the
> first letter of each word in a name is capitalized. e.g. Instead of:
> forobservingdist you would have: ForObservingDist. This makes it much easi
er to
> read.
> 2. Include a space on either side of an operator. So instead of: o1.id>=o2
.id
> Do this: o1.id >= o2.id
> 3. Include a space after a comman. So instead of o1.id,o1.date_order,o1.pr
oduct.
> Do this: o1.id, o1.date_order, o1.product.
> 4. Include some indenting in your query. I generally indent each Join clau
se and
> each On clause. So re-written, your query would look like:
> Select o1.id, o1.date_order, o1.product, o1.quantity
> , d1.id As Distribute_id
> , IsNull(Sum(d2.quantity), 0) As ForObservingDist
> , IsNull(Sum(o2.quantity), 0) As ForObservingOrd
> From [order] o1
> Left Join [order] o2
> On o1.id >= o2.id
> And o1.product = o2.product
> Left Join Distribute d1
> On o1.date_order <= d1.date_distribute
> And o1.product = d1.product
> Left Join Distribute d2
> On d2.id <= d1.id
> And d2.product = d1.product
> Group By o1.id, o1.date_order, o1.product, o1.quantity
> , d1.id, d1.date_distribute, d1.quantity, d1.product
> Placing commas at the beginning of a line is my personal preference becaus
e I
> make fewer "missing comma" mistakes. SQL purists would want keywords like
> Select, Left, Join etc. in all caps so they might write this query like so
(with
> commas at the end):
> SELECT o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id As Distribute_id,
> IsNull(Sum(d2.quantity), 0) As ForObservingDist,
> IsNull(Sum(o2.quantity), 0) As ForObservingOrd
> FROM [order] o1
> LEFT JOIN [order] o2
> ON o1.id >= o2.id
> AND o1.product = o2.product
> LEFT JOIN Distribute d1
> ON o1.date_order <= d1.date_distribute
> AND o1.product = d1.product
> LEFT JOIN Distribute d2
> ON d2.id <= d1.id
> AND d2.product = d1.product
> GROUP BY o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
>
> HTH
>
> Thomas
>
>|||Can you provide a sample of the input data and the expected results? Include
enough data to illustrated why Razvan's solution does not work for you.
Thomas
"POKEMON" <POKEMON@.discussions.microsoft.com> wrote in message
news:B648F5AB-2BE0-4CE8-A5E2-D8B6C43FD60B@.microsoft.com...
> Hi Thomas
> first of all thanks for your advices about the query style. I will do my
> best:))
> simply what I want is the ForObservingOrd column must be the same as in th
e
> query with UDFs. the Query with UDFs returns the results I want. I'm si
ng
> the solution with query hint like hash match while left joining [Orders] o2
> table if possible.
> I've rewrite the question and Razvan suggested to use subqueries instead o
f
> UDFs. it is working but ly it is not what I want.
> tnx again
> here is the suggestion of Razvan and my reply
> You can achieve the same results (with better performance) by using
> subqueries instead of UDF-s:
> select o1.id, o1.date_order, o1.product,o1.quantity,
> d1.id as Distribute_id, d1.date_distribute, (
> select sum(d2.quantity) from Distribute d2
> where d2.id<=d1.id and d2.product=d1.product
> ) as forobservingdist, (
> select sum(o2.quantity) from [order] o2
> where o2.id<=o1.id and o2.product=o1.product
> ) as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1
> on o1.date_order<=d1.date_distribute and o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
> Razvan
> --
> tnx Razvan sure I didnt think this:))
> do you have any info about using query hint works like that?
> I changed the UDFs with subqueries and its working. I looked at the query
> execution plan and it looks more simple with the udfs. are you sure this w
ill
> work with better performance? probably you are:))
> also plan of the query with subqueries shows many hash matches. Can't I us
e
> query hint making hash matches with left join?
> thanks again
> also the last form of the query is like that
> select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_
id
> , d1.date_distribute,
> case when
> 0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.pro
duct))
> then
> isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
> when
> d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistri
butedogan(d1.id,d1.product))
> then
> (o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d
1.id,d1.product)))
> else
> isnull(d1.quantity,0)
> end as distributed_quantity,
> case when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))>o1.quantity
> then
> o1.quantity
> when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))<0
> then
> 0
> else
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
> end as remainingorder,
> dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
> dbo.getorderdogan(o1.id,o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
> d1.date_distribute, d1.quantity, d1.product
> having
> ((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.prod
uct))+isnull(d1.quantity,0)>0
> and
> o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1
.id,d1.product)))
> or isnull(d1.quantity,0)=0
>
> --
> select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_
id
> , d1.date_distribute,
> case when
> 0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))
> then
> isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
> when
> d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
> then
> (o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
> and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 whe
re
> d2.id<=d1.id and d2.product=d1.product)))
> else
> isnull(d1.quantity,0)
> end as distributed_quantity,
> case when
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))>o1.quantity
> then
> o1.quantity
> when
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))<0
> then
> 0
> else
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))
> end as remainingorder,
> (select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
> d2.product=d1.product)as forobservingdist,
> (select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
> d1.date_distribute, d1.quantity, d1.product
> having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
> and o1.quantity>((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
> or isnull(d1.quantity,0)=0
>
> "Thomas Coleman" wrote:
>|||I RESPECT
"--CELKO--" wrote:

> change my query formating if you explain what is wrong. <<
> Get a copy of my SQL PROGRAMMING STYLE. I go into painful details and
> explain why you use certain formatting techniques, based on readabilty
> and eye movement. Years ago when I worked for AIRMIC, I did 6 months
> of full time research on this and about a year of part-time follow up.
>|||sorry i think it is my mistake.
the UDF solution and subquery solution is workin as I wanted.
I just want a solution with query hint. Thats all.
"Thomas Coleman" wrote:

> Can you provide a sample of the input data and the expected results? Inclu
de
> enough data to illustrated why Razvan's solution does not work for you.
>
> Thomas
>
> "POKEMON" <POKEMON@.discussions.microsoft.com> wrote in message
> news:B648F5AB-2BE0-4CE8-A5E2-D8B6C43FD60B@.microsoft.com...
>
>