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
Microsoft SQL ServerMySQL Server

Avatar of undefined
Last Comment
Zaid shariff

8/22/2022 - Mon
Misha

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

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
PortletPaul

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Zaid shariff

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

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

Zaid shariff

ASKER
sorry about that but im still getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'o'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

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

or change line 22 to define an alias of "o"
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Zaid shariff

ASKER
Wow, this works, Thanks a lot