October 16, 2013 at 6:51 am
The below view is not work .....
Create View XYZ As
IF Exists
(
select Code from PR_Header where Code = (Select 'PR-'+''+ CAST(Count(a.ID)+1 as varchar ) from PR_Header a Group by a.Company)
)
Begin
Select 'PR-'+''+ CAST(Count(ID)+2 as varchar ) as PR_PR ,'Default PR',Company from PR_Header Group by Company
End
Else
Select 'PR-'+''+ CAST(Count(ID)+1 as varchar ) as PR_PR ,'Default PR',Company from PR_Header Group by Company
October 16, 2013 at 7:19 am
I don't think you can use the IF...ELSE construct in a view.
You'll need a stored procedure for that.
The error might have given this away:
Msg 156, Level 15, State 1, Procedure Test, Line 3
Incorrect syntax near the keyword 'IF'.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 16, 2013 at 7:28 am
i already got the error , but i need to store this values in a view
October 16, 2013 at 7:32 am
ikrami2000 (10/16/2013)
i already got the error , but i need to store this values in a view
You cannot "store" values in a view.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 16, 2013 at 8:00 am
Not entirely sure what you are trying to do here but you don't need an IF statement to do this type of thing.
See if this will get what you are looking for.
CREATE VIEW XYZ
AS
SELECT 'PR-' + '' + CAST(Count(ID) + CASE
WHEN EXISTS (
SELECT Code
FROM PR_Header
WHERE Code = (
SELECT 'PR-' + '' + CAST(Count(a.ID) + 1 AS VARCHAR)
FROM PR_Header a
GROUP BY a.Company
)
)
THEN 2
ELSE 1
END AS VARCHAR) AS PR_PR
,'Default PR'
,Company
FROM PR_Header
GROUP BY Company
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 16, 2013 at 3:05 pm
Thanks 🙂 , this is exactly what i want
October 16, 2013 at 3:16 pm
ikrami2000 (10/16/2013)
Thanks 🙂 , this is exactly what i want
You are quite welcome. I would caution you that there are a few things in that code that I would recommend changing. You are concatenating empty strings which is totally pointless. You also have a number of cast to varchar where you don't specify the size of the varchar. Do you know what the default size for a varchar is? I can't remember either. This is why you should always add the size to your declaration.
Something like this:
CREATE VIEW XYZ
AS
SELECT 'PR-' + CAST(Count(ID) + CASE
WHEN EXISTS (
SELECT Code
FROM PR_Header
WHERE Code = (
SELECT 'PR-' + CAST(Count(a.ID) + 1 AS VARCHAR(5))
FROM PR_Header a
GROUP BY a.Company
)
)
THEN 2
ELSE 1
END AS VARCHAR(5)) AS PR_PR
,'Default PR'
,Company
FROM PR_Header
GROUP BY Company
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply