help on my script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help on my script

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 13:13:34

Here is my script Wile executing i got this error

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'My'.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[PriceReport_BasePG]
@usname varchar(100),@name varchar(50)
as
begin
DECLARE @sql VARCHAR(1000)
SET @sql = '
SELECT MobID,
MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END)-MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS BasepriceGap,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS Myprice
--MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) AS MyBottprice,
--MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) - MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS BotPriceGap
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price) AS low,
CASE Merchant
WHEN ''' + @usname + ''' THEN 1
ELSE 0
END AS My
FROM tbl_Shopzilla_' + @name + '_merchant
) AS t
GROUP BY MobID
HAVING MAX(My) = 1

SELECT DISTINCT t1.MPN_SKU,
t1.Product_Name,
m.Myprice,
m.BasepriceGap,
-- m.MyBottprice,
-- m.BotPriceGap,
m.LowPrice,
m.LowPriceMerchant,
m.HighPrice,
m.HighPriceMerchant

FROM ##Temp AS m
INNER JOIN tbl_Shopzilla_' + @name + '_Product AS t1 ON t1.MobID = m.MobID

drop table ##temp'

exec (@sql)
end

-- exec [PriceReport_BasePG] 'teptronics','teptronics'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 13:16:59

use print(@sql) instead of exec(@sql) and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 13:25:03

I got this output

SELECT MobID,
MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END)-MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS BasepriceGap,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS Myprice
--MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) AS MyBottprice,
--MAX(CASE WHEN My = 1 THEN Bottomline_Price ELSE NULL END) - MAX(CASE WHEN low = 1 THEN Bottomline_Price ELSE NULL END) AS BotPriceGap
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Bottomline_Price) AS low,
CASE Merchant
WHEN 'teptronics' THEN 1
ELSE 0
END AS My

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-30 : 13:28:52

add this to the end:
) as dt

No, you're never too old to Yak'n'Roll if you're too young to die.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 13:35:25

After adding this i received the same error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-30 : 13:44:58

You are missing the FROM in the derived table.

FROM (Your Derived Table Query FROM SomeTable) AS dt

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 13:49:12

I cleared my error ..
DECLARE @sql VARCHAR(1000) is not enough for the entire query DECLARE.. By declaring @sql VARCHAR(1500) I solved it..Tnx for nice replies

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-30 : 13:53:15

I use nvarchar(4000) or varchar(8000) to avoid issues like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 13:58:47

I will try this ..Thanks for your advice

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 14:08:30

In my next procedure as in the same format I am trying to convert dynamic query ..This time I received this error

Warning: Null value is eliminated by an aggregate or other SET operation.

(1536 row(s) affected)
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "t1.MPN_SKU" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "t1.Product_Name" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.Myprice" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.Baseprice" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.BasepriceGap" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.BotPrice" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.BotPriceGap" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.LowPrice" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.LowPriceMerchant" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.HighPrice" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "m.HighPriceMerchant" could not be bound.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-30 : 14:39:17

Please start a new topic on your next stored procedure and your errors. Include the code plus the PRINT of the dynamic query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 15:01:33

I cleared my error.. Thanks for your reply..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources