We help IT Professionals succeed at work.

How make a result of 'case when' as condition

115 Views
Last Modified: 2018-09-24
How make a result of 'case when' as condition

for eg:

Select *,
case when(case
when condition then 100*100 else 0
end as amount)>1000 then 0 else 1
end
from table1
Comment
Watch Question

MishaProgrammer
CERTIFIED EXPERT

Commented:
May be you want something like this?
SELECT * from
 (
 SELECT col1, col2, 
        CASE
 WHEN col3 <200 THEN 'Data1'
 WHEN col3 >300 THEN 'Data2'
  ELSE 'Data3'
  END AS SomeColunm 
  FROM Table1
 ) as t
 WHERE SomeColunm  = 'Data1'

Open in new window

Author

Commented:
thanks for the reply:
im trying this but is not working

Select *, case
when ( case
when lr_rate is null or lr_tonnage is null then booking_freight_rate*contracted_truck_weight
when price_type = 'PER_TON' 
then lr_rate*lr_tonnage
when price_type = 'PER_TRUCK' 
then lr_freight_price_rate
else booking_freight_rate*contracted_truck_weight
end as freight)>150000 then 0 else 0 end
from base_order
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Use a cross apply. In that you can create a column alias that you can then reuse in the where clause or the select clause. Don't forget to give the cross apply an alias and to use that when referring to the calculated columns.
SELECT
    o.*
  , ca.freight
  , CASE
        WHEN ca.freight > 15000
        THEN 1
        ELSE 0
    END
FROM base_order AS o
CROSS APPLY (
    SELECT
        CASE
            WHEN lr_rate IS NULL OR lr_tonnage IS NULL THEN booking_freight_rate * contracted_truck_weight
            WHEN price_type = 'PER_TON' THEN lr_rate * lr_tonnage
            WHEN price_type = 'PER_TRUCK' THEN lr_freight_price_rate
            ELSE booking_freight_rate * contracted_truck_weight
        END AS freight
) ca

Open in new window

ps: you may have an unnecessary condition (the first row below)

            /* WHEN lr_rate IS NULL OR lr_tonnage IS NULL THEN booking_freight_rate * contracted_truck_weight */ 
            WHEN price_type = 'PER_TON' THEN lr_rate * lr_tonnage
            WHEN price_type = 'PER_TRUCK' THEN lr_freight_price_rate
            ELSE booking_freight_rate * contracted_truck_weight

Open in new window

Author

Commented:
thank you but im getting this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'APPLY ( SELECT CASE WHEN lr_rate IS NULL OR lr_tonnag' at line 11
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
it does not work for MySQL, I looked at the topics and saw SQL Server, where cross apply is available. Please don't use topics that do not apply to your question.

For MySQL:
SELECT
    o.*
  , CASE
        WHEN ca.freight > 15000
        THEN 1
        ELSE 0
    END
FROM (
    SELECT
        o.*
      , CASE
            WHEN lr_rate IS NULL OR
                lr_tonnage IS NULL
            THEN booking_freight_rate * contracted_truck_weight
            WHEN price_type = 'PER_TON'
            THEN lr_rate * lr_tonnage
            WHEN price_type = 'PER_TRUCK'
            THEN lr_freight_price_rate
            ELSE booking_freight_rate * contracted_truck_weight
        END AS freight
    FROM base_order AS o
) ca

Open in new window

Author

Commented:
sorry about that but im still getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'o'
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
seriously? change line 2 to read *
not o.*

or change line 22 to define an alias of "o"
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Wow, this works, Thanks a lot

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions