Seven price
asked on
using Coalesce
the problem i am having with this procedure is the item number (i.item) for each user store shows up for all userid. BAsically it takes the first userid item number and use it for all userid.
It not suppose to be the same for each row. It works correctly with a regular join.
DECLARE @userid VARCHAR(MAX)
DECLARE @orderid int
select orderid, ( select COALESCE(i.Item + '', '') + CASE when i.Quantity < 10 THEN '0' ELSE '' END + cast(i.Quantity AS varchar(3)) + 'A'
from OrderDetail i
where t.DateCreated > t.Submitted OR t.Submitted is NULL for xml path ('') )Item
, t.userid
from Orders t
order by t.orderid desc
example.
orderid item userid
1 1234567 tony
2 1234567 james
3 1234567 jessy
what i need is.
orderid item userid
1 1234567 tony
2 6545654 james
3 5643345 jessy
It not suppose to be the same for each row. It works correctly with a regular join.
DECLARE @userid VARCHAR(MAX)
DECLARE @orderid int
select orderid, ( select COALESCE(i.Item + '', '') + CASE when i.Quantity < 10 THEN '0' ELSE '' END + cast(i.Quantity AS varchar(3)) + 'A'
from OrderDetail i
where t.DateCreated > t.Submitted OR t.Submitted is NULL for xml path ('') )Item
, t.userid
from Orders t
order by t.orderid desc
example.
orderid item userid
1 1234567 tony
2 1234567 james
3 1234567 jessy
what i need is.
orderid item userid
1 1234567 tony
2 6545654 james
3 5643345 jessy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks again my friend
DECLARE @orderid int
select orderid, ( select COALESCE(i.Item + '', '') + CASE when i.Quantity < 10 THEN '0' ELSE '' END + cast(i.Quantity AS varchar(3)) + 'A'
from OrderDetail i
where t.orderid=i.orderid -- missing this
and t.DateCreated > t.Submitted OR t.Submitted is NULL for xml path ('') )Item
, t.userid
from Orders t
order by t.orderid desc