need help to write sql query

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

need help to write sql query

Hello Guys,

 

Need help to write this sql queries

 

Requirement:

If the orders_purchased_by=Online  then appropriate anount  should be displayed ,else if the orders_purchased_by<>online then maximum amount should be displayed.

 

Source:

sourcesourceTargetTarget

 

  • General
Tags (1)
6 REPLIES
Highlighted
Senior Apprentice

Re: need help to write sql query

SELECT *
FROM tab
QUALIFY
   -- all Online purchases
   orders_purchased_by = 'Online'
OR
   -- for customers without Online purchase only a single row with the highest amount
Row_Number() Over (PARTITION BY customerkey ORDER BY CASE WHEN orders_purchased_by = 'Online' THEN 0 ELSE 1 end, amount DESC) = 1
Enthusiast

Re: need help to write sql query

Thanks Dnoeth for quick reply

 

want to write single sql query in the case statement,is it possible

Senior Apprentice

Re: need help to write sql query

If you want to add an indicator if it's an Online purchase:

CASE WHEN orders_purchased_by = 'Online' THEN 'Y' ELSE 'E' END
Enthusiast

Re: need help to write sql query

Hi Dnoeth,

 

The problem here am facing is CASE WHEN orders_purchased_by = 'Online' then that appropriate amount should be displayed not an static value

else max(amount ) should be displayed

 

Query:

sel customerkey,ordrs_purchase_by,

CASE  WHEN max(orders_purchased_by) = 'Online' THEN  amount

else

case when max(orders_purchased_by) <> 'Online'  then max(amount)

else

max(default value)

end

end

from table_nm

group by

customerkey,ordrs_purchase_by;

 

since i want case when max(amount) should be displayed when the condition max(orders_purchased_by) <> 'Online'  

have included everywhere aggregate function like max but not for the condition "CASE  WHEN max(orders_purchased_by) = 'Online' THEN  this corresponding amount should be displayed

but this query will throw you error  in the second line in the amount field as i have not used aggregate function in the amount

CASE  WHEN max(orders_purchased_by) = 'Online' THEN  amount

 

if any suggestion pls sorry for the long detailed mail

 

 

 

Enthusiast

Re: need help to write sql query

any solution for this guys thanks

Senior Apprentice

Re: need help to write sql query

Why do you want to use CASE, doesn't my solution work for you?

It's a single query and seems to return what you want, all rows for ONLINE and the row with the highest amount for other customers.