betachristopher
Programmer
If I could write this query in Crystal Reports or Access, it would be no problem, but I'm no good at SQL variables. I'm sure something is wrong with my IF/ELSE, and I think I have to declare it as a variable first, but I need help with syntax.
Code:
SELECT [PartitionID],
LEFT([ArchiveID],8)AS Date,
SUBSTRING([ArchiveID],38,4) AS FilePath,
SUBSTRING([ArchiveID],38,99) AS FileName,
IF(SELECT [PartitionID] FROM [MailArchive].[dbo].[JournalArchive])=0
BEGIN
PRINT 'mailstore1 ptn1\'
END
ELSE
IF(SELECT [PartitionID] FROM [MailArchive].[dbo].[JournalArchive])=1
BEGIN
PRINT 'mailstore1 ptn2\'
END
ELSE
IF(SELECT [PartitionID] FROM [MailArchive].[dbo].[JournalArchive])=2
BEGIN
PRINT 'mailstore1 ptn3\'
END
+LEFT([ArchiveID],4)
+'\'
+SUBSTRING([ArchiveID],5,2)
+'-'
+RIGHT([ArchiveID],2)
+'\'
+SUBSTRING([ArchiveID],38,1)
+'\'
+SUBSTRING([ArchiveID],39,3)
+'\'
+SUBSTRING([ArchiveID],38,99)
+'.' AS FolderStructure
FROM [MailArchive].[dbo].[JournalArchive];