Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-02-2012
04:50 PM

04-02-2012
04:50 PM

My input data is as follows:

Product | category | value |

1 | Entry | 2400 |

1 | Secondry | 1200 |

1 | Premium | 2500 |

1 | Entry | 1200 |

2 | Secondry | 1200 |

2 | Premium | 2500 |

3 | Secondry | 1200 |

3 | 01:10 | 250 |

4 | Entry+ | 240 |

4 | Entry | 500 |

5 | Secondry | 200 |

Following are the business logics i have to implement on the input data

1) If there is only one product then take that record. ( e.g. Product 5)

2) If there are more than one product then take the row which has max(value) and Category is Entry.

3) If there are more than one product and category "Entry" is not available then check for the category premium and max(value).

4) If both categories Entry and premium does not exist then ignore them.

Expected output after applying the business logic is as follows:

Expected output | ||

Joining column | category | value |

1 | Entry | 2400 |

2 | Premium | 2500 |

4 | Entry | 500 |

5 | Secondry | 200 |

Can you please suggest the query for it. I tried qualify rank function but could not figure how to implement the logic of step 2 and 3 mentioned above

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-02-2012
11:34 PM

04-02-2012
11:34 PM

Hello there

I was able to produce your requirement with the below

Hope it assists, Bryce

create volatile table bsw_0

(

product integer

, category varchar(20)

, xvalue integer

) on commit preserve rows;

insert into bsw_0 values (1,'Entry',2400);

insert into bsw_0 values (1,'Secondry',1200);

insert into bsw_0 values (1,'Premium',2500);

insert into bsw_0 values (1,'Entry',1200);

insert into bsw_0 values (2,'Secondry',1200);

insert into bsw_0 values (2,'Premium',2500);

insert into bsw_0 values (3,'Secondry',1200);

insert into bsw_0 values (3,'01:10',250);

insert into bsw_0 values (4,'Entry+',240);

insert into bsw_0 values (4,'Entry',500);

insert into bsw_0 values (5,'Secondry',200);

--- The bit you care about

sel

product

, category

, xvalue

from

bsw_0

where

product in

(sel product from bsw_0 having sum(1)=1 group by 1)

UNION

sel

product

, category

, xvalue

from

(

sel

product

, category

, xvalue

, case

when category='Entry' then 0

else 1

end rankval

, row_number () over

(partition by product order by

rankval

, xvalue desc

) as row_num

from

bsw_0

) a

where

product in

(sel product from bsw_0 where category in ('Entry','Premium')) and

row_num=1

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2012
12:07 AM

04-03-2012
12:07 AM

this migh do without union

select product,

substr(val,16) as category,

cast(substr(val,5,11) as integer) as xvalue

from

(

select product, max(case when category = 'Entry' then 2

when category = 'Premium' then 1

else 0

end !! xvalue !! category

) as val, count(*) as cnt

from bsw_0

group by 1

) as t

where cnt = 1

or (cnt > 1 and category in ('Entry','Premium'))

order by 1;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2012
04:56 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-03-2012
01:13 PM

04-03-2012
01:13 PM

You can also move the logic into QUALIFY:

SELECT * FROM bsw_0

QUALIFY COUNT(*) OVER (PARTITION BY product) = 1

OR

(category IN ('Entry', 'Premium')

AND ROW_NUMBER()

OVER (PARTITION BY product

ORDER BY CASE category

WHEN 'Entry' THEN 1

WHEN 'Premium' THEN 2

ELSE 3

END, xvalue DESC) = 1

)

Dieter