Link to home
Start Free TrialLog in
Avatar of Zaid shariff
Zaid shariff

asked on

How make a result of 'case when' as condition

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
Avatar of Misha
Misha
Flag of Russian Federation image

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

Avatar of Zaid shariff
Zaid shariff

ASKER

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

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

sorry about that but im still getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'o'
seriously? change line 2 to read *
not o.*

or change line 22 to define an alias of "o"
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Wow, this works, Thanks a lot