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

 

Tags (1)
9 REPLIES
Junior Contributor

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

Highlighted
Junior Contributor

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

Junior Contributor

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.

Enthusiast

Re: need help to write sql query

sorry dnoeth,met accident and could not able to reply faster.

 

am getting error  when i implemented the logic,attached screenshot for the sameTeradata_screenshot.png

Enthusiast

Re: need help to write sql query

Dnoeth,could you please help me on this

Junior Contributor

Re: need help to write sql query

Of course this fails, you try to use a condition in the Select-list, you need to wrap into a CASE.

 

But I still don't know why you don't use my original QUALIFY, which returns exactly what you want.