Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View with EXISTS Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 5:36 AM
Points: 20, Visits: 237
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

Post #1505164
Posted Wednesday, October 16, 2013 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,545, Visits: 11,359
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'.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505177
Posted Wednesday, October 16, 2013 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 5:36 AM
Points: 20, Visits: 237
i already got the error , but i need to store this values in a view
Post #1505183
Posted Wednesday, October 16, 2013 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,545, Visits: 11,359
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505188
Posted Wednesday, October 16, 2013 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505215
Posted Wednesday, October 16, 2013 3:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 5:36 AM
Points: 20, Visits: 237
Thanks , this is exactly what i want
Post #1505423
Posted Wednesday, October 16, 2013 3:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505427
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse