What I'm trying to do is use more than one CASE WHEN condition for the same column.
Here is my code for the query:
SELECT Url='',
p.ArtNo,
p.[Description],
p.Specification,
CASE
WHEN 1 = 1 or 1 = 1
THEN 1
ELSE 0
END as Qty,
p.NetPrice,
[Status] = 0
FROM Product p (NOLOCK)
However, what I want to do is use more then one WHEN for the same column "qty".
As in the following code:
IF
// CODE
ELSE IF
// CODE
ELSE IF
// CODE
ELSE
// CODE
This question is related to
sql
sql-server-2008
I had a similar but it was dealing with dates. Query to show all items for the last month, works great without conditions until Jan. In order for it work correctly, needed to add a year and month variable
declare @yr int
declare @mth int
set @yr=(select case when month(getdate())=1 then YEAR(getdate())-1 else YEAR(getdate())end)
set @mth=(select case when month(getdate())=1 then month(getdate())+11 else month(getdate())end)
Now I just add the variable into condition: ...
(year(CreationTime)=@yr and MONTH(creationtime)=@mth)
Something Like this, Two Conditions Two Columns
SELECT ITEMSREQ.ITEM AS ITEM,
ITEMSREQ.CANTIDAD AS CANTIDAD,
(CASE WHEN ITEMSREQ.ITEMAPROBADO=1 THEN 'APROBADO'
WHEN ITEMSREQ.ITEMAPROBADO=0 THEN 'NO APROBADO'
END) AS ITEMS,
(CASE
WHEN ITEMSREQ.ITEMAPROBADO = 0
THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM NO APROBADO PARA ENTREGA' END
WHEN ITEMSREQ.ITEMAPROBADO = 1
THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM AUN NO RECIBIDO'
WHEN REQUISICIONES.RECIBIDA=1 THEN 'RECIBIDO'
WHEN REQUISICIONES.RECIBIDA=0 THEN 'NO RECIBIDO'
END
END)
AS RECIBIDA
FROM ITEMSREQ
INNER JOIN REQUISICIONES ON
ITEMSREQ.CNSREQ = REQUISICIONES.CNSREQ
Combining all conditions
select a.* from tbl_Company a
where a.Company_ID NOT IN (1,2)
AND (
(0 =
CASE WHEN (@Fromdate = '' or @Todate='')
THEN 0
ELSE 1
END
) -- if 0=0 true , if 0=1 fails (filter only when the fromdate and todate is present)
OR
(a.Created_Date between @Fromdate and @Todate )
)
case
when a.REASONID in ('02','03','04','05','06') then
case b.CALSOC
when '1' then 'yes'
when '2' then 'no'
else 'no'
end
else 'no'
end
Just use this one, You have to use more when they are classes.
SELECT Url='',
p.ArtNo,
p.[Description],
p.Specification,
CASE
WHEN 1 = 1 or 1 = 1
THEN 1
WHEN 2 = 2
THEN 2
WHEN 3 = 3
THEN 3
ELSE 0
END as Qty,
p.NetPrice,
[Status] = 0
FROM Product p (NOLOCK)
Its just that you need multiple When
for a single case to behave it like if.. Elseif else..
Case when 1=1 //if
Then
When 1=1 //else if
Then....
When ..... //else if
Then
Else //else
.......
End
This can be an efficient way of performing different tests on a single statement
select
case colour_txt
when 'red' then 5
when 'green' then 4
when 'orange' then 3
else 0
end as Pass_Flag
this only works on equality comparisons!
You can use below example of case when with multiple conditions.
SELECT
id,stud_name,
CASE
WHEN marks <= 40 THEN 'Bad'
WHEN (marks >= 40 AND
marks <= 100) THEN 'good'
ELSE 'best'
END AS Grade
FROM Result
case when first_condition
then first_condition_result_true
else
case when second_condition
then second_condition_result_true
else
second_condition_result_false
end
end
end as qty
Source: Stackoverflow.com