How make a result of 'case when' as condition

Zaid shariff
Zaid shariff used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
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
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
Most Valuable Expert 2014
Awarded 2013
Commented:
SELECT
   d.*
  , 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
) AS d

I will be useful for you to learn how to identify and fix mistakes of this nature

Author

Commented:
Wow, this works, Thanks a lot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial