×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Use IF/ELSE in Query

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

RE: Use IF/ELSE in Query

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,
    
    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

(OP)

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

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é

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close