Selection (IF or CASE), in SELECT query. HELP plz

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selection (IF or CASE), in SELECT query. HELP plz

Author

Topic

no_tofu_speed
Starting Member

6 Posts

Posted - 2008-08-28 : 19:33:24

Hello all, I am quite a beginner.

I am trying to do a selection (if else) or along those lines, in a SELECT query. Which actually will be in a Stored Procedure.

I did try to search, and did discovered CASE statements but couldn't get that working either.

Below is an illustration of what I am trying to try.

Just different SELECT statements depending in 'Status', using a 'item id' as a parameter.

First with an IF ELSE statement

And then a bad attempt at geting CASE to work.

PLEASE HELP?

CREATE PROCEDURE usp_Read_request_status

@item_Ident nvarchar(12)

AS

IF dbo.Request.Status = 'Requested' THEN

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status, dbo.Request.Req_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id

WHERE dbo.Request.Item_id = @item_Ident

ELSE IF dbo.Request.Status = 'Authorized' THEN

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status, dbo.Auth_request.Auth_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Auth_request ON dbo.Request.Item_id = dbo.Auth_request.Item_id

WHERE dbo.Request.Item_id = @item_Ident

ELSE IF dbo.Request.Status = 'Denied' THEN

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status, dbo.Deny_request.Deny_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Deny_request ON dbo.Request.Item_id = dbo.Deny_request.Item_id

WHERE dbo.Request.Item_id = @item_Ident

ELSE IF dbo.Request.Status = 'Ordered' THEN

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status, dbo.[Order].Order_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.[Order] ON dbo.Request.Item_id = dbo.[Order].Item_id

WHERE dbo.Request.Item_id = @item_Ident

ELSE IF dbo.Request.Status = 'Cancelled' THEN

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status, dbo.Order_cancel.Ord_cancel_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Order_cancel ON dbo.Request.Item_id = dbo.Order_cancel.Item_id

WHERE dbo.Request.Item_id = @item_Ident

ELSE IF dbo.Request.Status = 'Delivered' THEN

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status, dbo.Arrival.Arrival_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Arrival ON dbo.Request.Item_id = dbo.Arrival.Item_id

WHERE dbo.Request.Item_id = @item_Ident

END IF

---------------------------------------------------------------------------------------

CREATE PROCEDURE usp_Read_request_status

@item_Ident nvarchar(12)

AS

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status,

CASE

WHEN dbo.Request.Status = 'Requested' THEN

dbo.Request.Req_date

WHEN dbo.Request.Status = 'Authorized' THEN

dbo.Auth_request.Auth_date

WHEN dbo.Request.Status = 'Denied' THEN

dbo.Deny_request.Deny_date

WHEN dbo.Request.Status = 'Ordered' THEN

dbo.[Order].Order_date

WHEN dbo.Request.Status = 'Cancelled' THEN

dbo.Order_cancel.Ord_cancel_date

WHEN dbo.Request.Status = 'Delivered' THEN

dbo.Arrival.Arrival_date

END

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Auth_request ON dbo.Request.Item_id = dbo.Auth_request.Item_id INNER JOIN

dbo.Deny_request ON dbo.Request.Item_id = dbo.Deny_request.Item_id INNER JOIN

dbo.[Order] ON dbo.Request.Item_id = dbo.[Order].Item_id INNER JOIN

dbo.Order_cancel ON dbo.Request.Item_id = dbo.Order_cancel.Item_id INNER JOIN

dbo.Arrival ON dbo.Request.Item_id = dbo.Arrival.Item_id

WHERE dbo.Request.Item_id = @item_Ident

---------------

The first CASE attempt which does 'complete successfully' in creating the procedure. However does not return anything.

Thinking it is due to specifying tables in the FROM statement that become unused depending on the Case statement contents, I did the following which has errors.

CREATE PROCEDURE usp_Read_request_status

@item_Ident nvarchar(12)

AS

SELECT dbo.Item.Item_id, dbo.Item.Title, dbo.Request.Status,

CASE

WHEN dbo.Request.Status = 'Requested' THEN

dbo.Request.Req_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id

WHERE dbo.Request.Item_id = @item_Ident

WHEN dbo.Request.Status = 'Authorized' THEN

dbo.Auth_request.Auth_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Auth_request ON dbo.Request.Item_id = dbo.Auth_request.Item_id

WHERE dbo.Request.Item_id = @item_Ident

WHEN dbo.Request.Status = 'Denied' THEN

dbo.Deny_request.Deny_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Deny_request ON dbo.Request.Item_id = dbo.Deny_request.Item_id

WHERE dbo.Request.Item_id = @item_Ident

WHEN dbo.Request.Status = 'Ordered' THEN

dbo.[Order].Order_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.[Order] ON dbo.Request.Item_id = dbo.[Order].Item_id

WHERE dbo.Request.Item_id = @item_Ident

WHEN dbo.Request.Status = 'Cancelled' THEN

dbo.Order_cancel.Ord_cancel_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Order_cancel ON dbo.Request.Item_id = dbo.Order_cancel.Item_id

WHERE dbo.Request.Item_id = @item_Ident

WHEN dbo.Request.Status = 'Delivered' THEN

dbo.Arrival.Arrival_date

FROM dbo.Item INNER JOIN

dbo.Request ON dbo.Item.Item_id = dbo.Request.Item_id INNER JOIN

dbo.Arrival ON dbo.Request.Item_id = dbo.Arrival.Item_id

WHERE dbo.Request.Item_id = @item_Ident

END

--------------

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-28 : 23:50:18

I am a beginner as well so hopefully a dba will help but for now maybe this may help. here a few case statements I have that work.

1.

SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < @SourceStartDate THEN @SourceStartDate ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > @SourceEndDate THEN @SourceEndDate ELSE EventDetails.EventEndTime
END AS EndTime
FROM EventDetails

2.

SELECT CAST(@DateRange AS varchar(30)) AS DateRangeSelection,
CASE
WHEN @DateRange = 'Current Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Current Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, - 2, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, - 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN @DateRange = 'Current Hour' THEN GETDATE()
WHEN @DateRange = 'Last Hour' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 12 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 24 Hours' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN @DateRange = 'Today' THEN GETDATE() WHEN @DateRange = 'Yesterday' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Weekend' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
WHEN @DateRange = 'Current Week' THEN GETDATE()
WHEN @DateRange = 'Last 7 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 5 Week Days' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN @DateRange = 'Last Week' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 2 Weeks' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Month' THEN GETDATE()
WHEN @DateRange = 'Last 30 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last 90 days' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN @DateRange = 'Last Month' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Last 3 Months' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN @DateRange = 'Current Quarter' THEN GETDATE()
WHEN @DateRange = 'Last Quarter' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)) WHEN @DateRange = 'Current Year' THEN GETDATE()
WHEN @DateRange = 'Last Year' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS EndDateRange

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-29 : 01:30:08

Let me know if this is what you are looking for.


CREATE PROCEDURE usp_Read_request_status
@item_Ident nvarchar(12)
AS


SELECT i.Item_id, i.Title, r.Status,
CASE
	WHEN r.Status = 'Requested' THEN r.Req_date
	WHEN r.Status = 'Authorized' THEN(
		SELECT top 1 ar.Auth_date
		FROM
		dbo.Auth_request ar
		where	r.Item_id = ar.Item_id)
	WHEN r.Status = 'Denied' THEN (
		SELECT top 1 dr.Deny_date
		FROM 
		dbo.Deny_request dr
		where r.Item_id = dr.Item_id)
	WHEN r.Status = 'Ordered' THEN(
		SELECT top 1 o.Order_date
		FROM 
                dbo.[Order] o
		where r.Item_id = o.Item_id)
	WHEN r.Status = 'Cancelled' THEN(
		SELECT top 1 oc.Ord_cancel_date
		FROM 
                dbo.Order_cancel oc
		where r.Item_id = oc.Item_id)
	WHEN	 r.Status = 'Delivered' THEN(
		SELECT top 1 ar.Arrival_date
		FROM 
                dbo.Arrival ar
		where r.Item_id = ar.Item_id)
	END as MYDATE
FROM 
	dbo.Item i
INNER JOIN
	dbo.Request r 
ON i.Item_id = r.Item_id
WHERE r.Item_id = @item_Ident

no_tofu_speed
Starting Member

6 Posts

Posted - 2008-08-29 : 13:50:22

Vinnie881.
Thanks so very much, it works perfectly. I really appreciate your time and effort to come up with that for me.

And also thanks Harlingtonthewizard for those pieces of working code to look at.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources