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
Zaid shariffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
Zaid shariffAuthor 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
0
PortletPaulEE Topic AdvisorCommented:
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

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Zaid shariffAuthor 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
0
PortletPaulEE Topic AdvisorCommented:
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

0
Zaid shariffAuthor Commented:
sorry about that but im still getting an error:

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

or change line 22 to define an alias of "o"
0
PortletPaulEE Topic AdvisorCommented:
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
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zaid shariffAuthor Commented:
Wow, this works, Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.