Use IF/ELSE in Query
Use IF/ELSE in Query
(OP)
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];
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];
Talk To Other Members
RE: Use IF/ELSE in Query
CODE
LEFT([ArchiveID],8)AS Date,
SUBSTRING([ArchiveID],38,4) AS FilePath,
SUBSTRING([ArchiveID],38,99) AS FileName,
CASE WHEN [PartitionID]=0 THEN 'mailstore1 ptn1\'
WHEN [PartitionID]=1 THEN 'mailstore1 ptn2\'
WHEN [PartitionID]=1 THEN 'mailstore1 ptn3\'
ELSE ''
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];
1 - 3 will have text string at the start of the FolderStructure field; everything else will begin on the LEFT([ArchiveID],4)
value.
soi là, soi carré
RE: Use IF/ELSE in Query
Thank you! CASE worked great. This opens up a lot of things I can do with my SQL queries now. THANK YOU!!!
RE: Use IF/ELSE in Query
You can use IF... BEGIN.. END in Transact-SQL ("T-SQL") - look at Books on Line for details and examples (Help > Contents in SQL Server Management Studio).
soi là, soi carré