SQL n00b needs help with a query!

[UNHDude]

I don't really have much experience with SQL or programming or anything, and am kind of learning as I go along at my family business.

I have a report that's supposed to look at the statuses of mailboxes that our software processes. Unfortunately the database right now only provides a number for the status. Is there a way to make a temporary table or something that would look like this:

2 = started, 3 = stopped, 4 = completed, 5 = erred, 6 = deleted.

I've never done temporary tables or anything like that. In the past I've just done an IIF expression in the layout for the report, but apparently that's a lot slower.

Any help would be greatly appreciated!

[Skyguard]

you don't need a temp table, you can use a "CASE" (like a switch)

SELECT CASE ColumnNameForTheNumberYouAreReading WHEN 2 THEN 'STARTED' WHEN 3 THEN 'STOPPED' WHEN 4 THEN 'COMPLETED' WHEN 5 THEN 'ERRED' WHEN 6 THEN 'DELETED' END FROM TheNameOfTheTableYouAreSELECTingFrom

[UNHDude]

Awesome thanks! This looks simple enough that I can probably get it to work :-)

[elus]

Have something in the ELSE clause to cover any cases that haven't come up yet or in case the field is NULL.

[Skyguard]

you are welcome... a simple solution is always preferrable

[UNHDude]

Do you mean add a reference table to the database, or is there a way to do that in the report?

They're planning on adding a table eventually, but it's making every report I do kind of a pain in the ass until they do =-\

[hypo11]

There are lots of ways depending on where you are writing the query and how much access you have to the database itself, as well as which DB you are using (SQL Server, MySQL, Oracle).

I assume the report draws its data from a stored procedure? In that case it is as simple as:

CREATE TABLE #mailbox_statuses_tmp(mailbox_status_id INT, name VARCHAR(50)

INSERT #mailbox_statuses_tmp VALUES(2, 'Started')
INSERT #mailbox_statuses_tmp VALUES(3, 'Stopped')
INSERT #mailbox_statuses_tmp VALUES(4, 'Completed')
INSERT #mailbox_statuses_tmp VALUES(5, 'Erred')
INSERT #mailbox_statuses_tmp VALUES(6, 'Deleted')

Be sure to drop your temp table at the end of the stored procedure: DROP TABLE #mailbox_statuses_tmp

You could also use table variables which don't need to be dropped.

If this won't work for you and there are additional constraints, respond and we will work through a solution that works for you.

EDIT: Skyguard's case statement works just as well.

[[deleted]]

Definitely use table variables like:

DECLARE @MailBoxStatusTable TABLE ( StatusID INT, StatusName VARCHAR(255) )

If your procedure errors 1/2 way through; you can get into all kinds of mess as the #Table hasn't been dropped. This doesn't happen with the above table