Hi
How to assign a variable value from if exist ?
like
declare @.i int
if exits( select @.i = ID from table1 where ID = 100)
-- do sth
but I always get an error
Thanks a lot for helpingAnn
You cannot do in that way.
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||Ann,
Posting the actual error would help.
Does it have to be in a subquery?
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
is easy to read/follow.
Regards
AJ
"Ann" <Ann@.discussions.microsoft.com> wrote in message news:14A79DE3-EC8D-45D9-8554-F96AF32
956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||declare @.i int
SET @.i = ( select ID from table1 where ID = 100)
if @.i IS NOT NULL ......
Although I assume you can do whatever you want to do probably simpler with a
join instead of an IF, but for that you would have to post the rest of your
code.
Jacco Schalkwijk
SQL Server MVP
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||My problem is
I have two tables
Product
Product_ID Product_Name
100 Apple
101 Peach
102 Banana
Order
Product_ID Customer_ID Quantity
100 1 5
101 1 6
Now I need to generate a report with every product and every customer. The
problem is that if nobody purchases Banana(which is 102),I need to insert
null
so it will look like
Customer_ID Product_ID Quantity
1 100 5
1 101 6
1 102 NULL
IF EXISTS(
SELECT * FROM Order WHERE Customer_ID =1) INSERT INTO
#temp(Customer_ID ,Product_ID , Quantity) SELECT Customer_ID ,Product_ID,
Quantity FROM Order WHERE Customer_ID = 1
ELSE
INSERT INTO #temp(Customer_ID ,Product_ID ,
Quantity) VALUES(1,Product_ID,NULL) -- suppose only one product here
If I use
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
I won't get 102(banana) in here
If I user
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
I'd have to select twice,that's why I am asking if possible,I can assign a
value in if exists
Thanks everyone
"Jacco Schalkwijk" wrote:
> declare @.i int
> SET @.i = ( select ID from table1 where ID = 100)
> if @.i IS NOT NULL ......
> Although I assume you can do whatever you want to do probably simpler with
a
> join instead of an IF, but for that you would have to post the rest of you
r
> code.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
>
>|||I assume you have a Customers table as well? Try:
SELECT C.customer_id, P.product_id,
COALESCE(SUM(quantity),0) AS quantity
FROM Customers AS C
CROSS JOIN Products AS P
LEFT JOIN Orders AS O
ON C.customer_id = O.customer_id
AND P.product_id = O.product_id
AND O.orderid = 10249
GROUP BY C.customer_id, P.product_id
I would think that the Orders table is denormalized if it has both the
order number and the customer id. Doesn't Order determine Customer?
David Portas
SQL Server MVP
--|||Thanks a lot,that 's what I need
"David Portas" wrote:
> I assume you have a Customers table as well? Try:
> SELECT C.customer_id, P.product_id,
> COALESCE(SUM(quantity),0) AS quantity
> FROM Customers AS C
> CROSS JOIN Products AS P
> LEFT JOIN Orders AS O
> ON C.customer_id = O.customer_id
> AND P.product_id = O.product_id
> AND O.orderid = 10249
> GROUP BY C.customer_id, P.product_id
> I would think that the Orders table is denormalized if it has both the
> order number and the customer id. Doesn't Order determine Customer?
> --
> David Portas
> SQL Server MVP
> --
>
