Click here to Skip to main content
15,885,365 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
Declare @PeriodType varchar(10)='1'
 Declare @month varchar(10)='03'
 Declare @year varchar(10)='2018'
 
 
 SELECT Shrimp_Name,Grades,Japan,EU,US  
 FROM (  
 SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end)) as 'Price'  from   
 Tbl_Shrimp_Price TSP  
 inner join Tbl_Mast_Shrimp_Region TMR On  TMR.ID=TSP.Region  
 inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade  
 inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType  
 WHERE TSP.ShrimpType=1 and TSP.PeriodType=@PeriodType AND 
 --PostMonth =cast('01-'+@year1 as Varchar(20))
 DATEPART(year, CONVERT(date, PostMonth, 105)) = @year 
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month
 ) as s  
 PIVOT  
 (  
SUM(Cast(Price) as Decimal(18,5)
 FOR [Region_Name] IN (Japan, EU, US)  
 )AS pvt  
 ORDER BY Shrimp_Name,Grades  
  
 SELECT Shrimp_Name,Grades,Japan,EU,US  
 FROM (  
 SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end) as 'Price'  from    
 Tbl_Shrimp_Price TSP  
 inner join Tbl_Mast_Shrimp_Region TMR On  TMR.ID=TSP.Region  
 inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade  
 inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType  
 WHERE TSP.ShrimpType=2  and TSP.PeriodType=@PeriodType  
 AND --PostMonth = cast('01-'+@year1 as Varchar(20))
 DATEPART(year, CONVERT(date, PostMonth, 105)) = @year 
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month 
 ) as s  
 PIVOT  
 (  
SUM(Cast(Price) as Decimal(18,5)
 FOR [Region_Name] IN (Japan, EU, US)  
 )AS pvt  
 ORDER BY Shrimp_Name,Grades


What I have tried:

Above is my Query and i want to Replace instead of 0.00 to '-'
and i am getting error above is my query so please help me-
error after execution-

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'Price'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '('.
Posted
Updated 22-May-18 3:55am
v2
Comments
CHill60 22-May-18 9:37am    
Your question will be clearer if you use the Improve question link to add some sample data to your question showing the table layout for each of your tables

1 solution

Quote:
SQL
(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end)) as 'Price'

Count the parentheses - you have one too many closing parentheses on that expression.
 
Share this answer
 
Comments
Maciej Los 22-May-18 15:29pm    
Hawk eye!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900