Select query using variable not running in mssql

Question

Select query is not working when use variable in MSSQL2014 My Schema is :-

    CREATE TABLE product 
    (idproduct int, name varchar(50), description varchar(50), tax decimal(18,0))


INSERT INTO product
    (idproduct, name, description,tax)
VALUES
    (1, 'abc', 'This is abc',10),
    (2, 'xyz', 'This is xyz',20),
    (3, 'pqr', 'This is pqr',15)


CREATE TABLE product_storage 
    (idstorage int,idproduct int,added datetime, quantity int, price decimal(18,0))


INSERT INTO product_storage 
    (idstorage,idproduct, added, quantity,price)
VALUES
    (1, 1, 2010-01-01,0,10.0),
    (2, 1, 2010-01-02,0,11.0),
    (3, 1, 2010-01-03,10,12.0),
    (4, 2, 2010-01-04,0,12.0),
    (5, 2, 2010-01-05,10,11.0),
(6, 2, 2010-01-06,10,13.0),
(7, 3, 2010-01-07,10,14.0),
(8, 3, 2010-01-07,10,16.0),
(9, 3, 2010-01-09,10,13.0)

and i am executing below command:-

declare @price1 varchar(10)


SELECT p.idproduct, p.name, p.tax,
[@price1]=(SELECT top 1 s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC),
 (@price1 * (1 + tax/100)) AS [price_with_tax]
FROM product p

;

This is not working in MSSQL, Please Help me out. for detail check http://sqlfiddle.com/#!6/91ec2/296

And My query is working in MYSQL Check for detail :- http://sqlfiddle.com/#!9/a71b8/1

A good example for a good question, thanks for the Fiddles

Gottfried Lesigang
– Gottfried Lesigang

2015-12-16 11:53:59 +00:00
Commented Dec 16, 2015 at 11:53 — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 16, 2015 at 11:53

Thiago · Accepted Answer · 2015-12-16 11:39:25Z

3

Try this query

SELECT 
    p.idproduct
    , p.name
    , p.tax
    , (t1.price * (1 + tax/100)) AS [price_with_tax]
FROM product p
inner join 
(
    SELECT ROW_NUMBER() over (PARTITION by s.idproduct order by s.added ASC) as linha, s.idproduct, s.price 
    FROM product_storage s
    WHERE s.quantity > 0    
) as t1
    on t1.idproduct = p.idproduct and t1.linha = 1

answered Dec 16, 2015 at 11:39

Thiago

564 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gautam Chawla Over a year ago

This query is also not giving the correct result of the last row, Please check with MYSQL Query

Thiago Over a year ago

The last row (product 3) has two identical dates (2010-01-07). You need to add another condition in the query (order by) or add time with the date. e.g. 2010-01-07 10:00:00 and 2010-01-07 15:32:00

Gautam Chawla Over a year ago

But with the same schema it works in MYSQL Check this :- sqlfiddle.com/#!9/a71b8/1

Thiago Over a year ago

I updated last row inserted in the product_storage with same date (2010-01-07) and the result was different. check this sqlfiddle.com/#!9/964730/1

Gottfried Lesigang Over a year ago

Hi, there was a problem with the literally inserted dates in the Fiddle , think there was sort of a subtraction of int values rather than parsing a date...

Gottfried Lesigang · Accepted Answer · 2015-12-16 13:52:27Z

1

Try it like this:

Explanantion: You cannot use a variable "on the fly", but you can do row-by-row calculation in an APPLY...

SELECT p.idproduct, p.name, p.tax,
       Price.price1,
       (price1 * (1 + tax/100)) AS [price_with_tax]
FROM product p
CROSS APPLY (SELECT top 1 s.price
             FROM product_storage s
             WHERE s.idproduct=p.idproduct AND s.quantity > 0
             ORDER BY s.added ASC) AS Price(price1)

;

EDIT: Your Fiddle uses a bad literal date format, try this:

INSERT INTO product_storage 
    (idstorage,idproduct, added, quantity,price)
VALUES
    (1, 1, '20100101',0,10.0),
    (2, 1, '20100102',0,11.0),
    (3, 1, '20100103',10,12.0),
    (4, 2, '20100104',0,12.0),
    (5, 2, '20100105',10,11.0),
    (6, 2, '20100106',10,13.0),
    (7, 3, '20100107',10,14.0),
    (8, 3, '20100108',10,16.0),
    (9, 3, '20100109',10,13.0)

edited Dec 16, 2015 at 13:52

answered Dec 16, 2015 at 11:53

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

7 Comments

Gautam Chawla Over a year ago

this query is also not giving the correct result of the last row, Please compare with MYSQL sqlfiddle.com/#!9/a71b8/1

Gottfried Lesigang Over a year ago

AS TFroes pointed out, the reason is your thinking of sorting. SQL Server keeps no implicit row order which you could rely on. If there are more than one rows with the same value you are sorting with, it will be totally random which occurs first... Try both queries with unique dates and/or include the time.

Gautam Chawla Over a year ago

Okay, But i have change the identical values, then also it shows the same result Check this :-sqlfiddle.com/#!6/c896a/2

Gottfried Lesigang Over a year ago

@GautamChawla, Yes this was a bit odd. The reason is the way you insert the dates. Just try in your fiddle this line select * from product_storage order by added and explore the added column. For literal date usage read this: stackoverflow.com/a/34275965/5089204

Gautam Chawla Over a year ago

Hi, Thanks this works fine when i convert date time. Thanks for your Help :)

|

Gautam Chawla · Accepted Answer · 2015-12-16 12:59:41Z

0

Here is the correct schema for SQL Server and query runs perfect as Shnugo Replied.

 VALUES
        (1, 1, convert(datetime,'2010-01-01'),0,10.0),
        (2, 1, convert(datetime,'2010-01-02'),0,11.0),
        (3, 1, convert(datetime,'2010-01-03'),10,12.0),
        (4, 2, convert(datetime,'2010-01-04'),0,12.0),
        (5, 2, convert(datetime,'2010-01-05'),10,11.0),
        (6, 2, convert(datetime,'2010-01-06'),10,13.0),
        (7, 3, convert(datetime,'2010-01-07'),10,14.0),
        (8, 3, convert(datetime,'2010-01-07'),10,16.0),
        (9, 3, convert(datetime,'2010-01-09'),10,13.0)

edited Dec 16, 2015 at 12:59

answered Dec 16, 2015 at 12:51

Gautam Chawla

655 bronze badges

Collectives™ on Stack Overflow

Select query using variable not running in mssql

3 Answers 3

5 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related