Use IF/ELSE in Query 1

betachristopher · Sep 30, 2011

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];

drlex · Oct 3, 2011

You'll be wanting a CASE WHEN... ELSE... END syntax.

Code:

SELECT [PartitionID],
    LEFT([ArchiveID],8)AS Date,
    SUBSTRING([ArchiveID],38,4) AS FilePath,
    SUBSTRING([ArchiveID],38,99) AS FileName,
    [b][red]
    CASE WHEN [PartitionID]=0 THEN 'mailstore1 ptn1\'
         WHEN [PartitionID]=1 THEN 'mailstore1 ptn2\'
         WHEN [PartitionID]=1 THEN 'mailstore1 ptn3\'
    ELSE ''
    END
    [/red][/b]
        +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é

betachristopher · Oct 3, 2011

Thank you! CASE worked great. This opens up a lot of things I can do with my SQL queries now. THANK YOU!!!

drlex · Oct 3, 2011

No problem; thanks for the accolade.

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é

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Use IF/ELSE in Query 1

betachristopher

Programmer

drlex

Technical User

betachristopher

Programmer

drlex

Technical User

Similar threads

Part and Inventory Search

Sponsor