September 30, 2011 at 2:15 pm
I'm running SQL 2008
I'm trying to run this script against a database but i keep getting this error
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '+'.
here is the query. Please help
SELECT [IdPartition],
LEFT([SavesetID],8)AS Date,
SUBSTRING([SavesetID],38,4)AS DVSFilePath,
SUBSTRING([SavesetID],38,99)AS DVSFileName,
IF(SELECT [IdPartition] FROM [EVVSMailStore1_1].[dbo].[JournalArchive])=0
BEGIN
PRINT 'mailstore1 ptn1\'
END
ELSE
IF(SELECT [IdPartition] FROM [EVVSMailStore1_1].[dbo].[JournalArchive])=1
BEGIN
PRINT 'mailstore1 ptn2\'
END
ELSE
IF(SELECT [IdPartition] FROM [EVVSMailStore1_1].[dbo].[JournalArchive])=2
BEGIN
PRINT 'mailstore1 ptn3\'
END
+LEFT([SavesetID],4)
+'\'
+SUBSTRING([SavesetID],5,2)
+'-'
+RIGHT([SavesetID],2)
+'\'
+SUBSTRING([SavesetID],38,1)
+'\'
+SUBSTRING([SavesetID],39,3)
+'\'
+SUBSTRING([SavesetID],38,99)
+'.DVS' AS FolderStructure
FROM [EVVSMailStore1_1].[dbo].[JournalArchive]
September 30, 2011 at 2:17 pm
You cant use IF statement in a SELECT clause.. You can use CASE expression though.
September 30, 2011 at 2:18 pm
please explain
September 30, 2011 at 2:22 pm
Try this:
SELECT
[IdPartition],
LEFT([SavesetID],8)AS Date,
SUBSTRING([SavesetID],38,4)AS DVSFilePath,
SUBSTRING([SavesetID],38,99)AS DVSFileName,
FolderStructure =
((CASE [IdPartition]
WHEN 0 THEN 'mailstore1 ptn1\'
WHEN 1 THEN 'mailstore1 ptn2\'
WHEN 2 THEN 'mailstore1 ptn3\'
END
)
+LEFT([SavesetID],4) + '\'
+SUBSTRING([SavesetID],5,2) +'-'
+RIGHT([SavesetID],2) +'\'
+SUBSTRING([SavesetID],38,1) +'\'
+SUBSTRING([SavesetID],39,3) +'\'
+SUBSTRING([SavesetID],38,99) +'.DVS'
)
FROM [EVVSMailStore1_1].[dbo].[JournalArchive]
September 30, 2011 at 2:35 pm
That worked thank you
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply