oracle - Showing Different aggregate for different products -

i want code used generate aggregate product product. product aggregate can year date(ytd), months date(mtd) , quarter date(qtd). user pass parameter on basis code should decide kind of output user wants.

if year passing in parameter code should generate aggregate starting of year sysdate.  if quarter no passing in parameter code should generate aggregate starting of quarter sysdate.  if month passing in parameter code should generate aggregate starting of month sysdate. 

it means on basis of parameter should able decide kind of user want 3. input data this-

product table      product_id       product_name          price     1                  mobile               200     2                   t.v.                400     3                  mixer                300 

and

sales table-

 product_id          sales_date         quantity     1                   01-01-2015            30     2                   03-01-2015            40     3                   06-02-2015            10     1                   22-03-2015            30     2                   09-04-2015            10     3                   21-05-2015            40     1                   04-06-2015            40     2                   29-07-2015            30     1                   31-08-2015            30     3                   14-09-2015            30 

and ouput column contains 3 columns are- product_id, product_name , total. column total_amount(quantity*price) have calculate sale on basis of input given user , this- example ,

 if pro_test procedure  call pro_test('ytd') -- should return productwise ytd, call pro_test('qtd') -- should return productwise qtd , on.. 

you looking where clause :-) list conditions or , done.

select    p.product_id,    p.product_name,    coalesce(sum(s.quantity * p.price), 0) total product p  left join sales s on s.product_id = p.product_id    (:aggregate = 'ytd' , to_char(s.sales_date, 'yyyy') = to_char(sysdate, 'yyyy')) or   (:aggregate = 'mtd' , to_char(s.sales_date, 'yyyymm') = to_char(sysdate, 'yyyymm')) or   (:aggregate = 'qtd' , to_char(s.sales_date, 'yyyyq') = to_char(sysdate, 'yyyyq')) group p.product_id, p.product_name; 

edit: here how corresponding pl/sql function like:

create or replace function matches_date_aggregate(in_sales_date date, in_aggregate char)    return integer begin   if (in_aggregate = 'ytd' , to_char(in_sales_date, 'yyyy') = to_char(sysdate, 'yyyy'))   or (in_aggregate = 'mtd' , to_char(in_sales_date, 'yyyymm') = to_char(sysdate, 'yyyymm'))   or (in_aggregate = 'qtd' , to_char(in_sales_date, 'yyyyq') = to_char(sysdate, 'yyyyq'))     return 1;   else     return 0;   end if; end matches_date_aggregate; 

your query's where clause become:

where matches_date_aggregate(s.sales_date, :aggregate) = 1 

the function cannot return boolean unfortunately, though oracle's pl/sql knows boolean data type, oracle sql doesn't.