MySQL Forums
Forum List  »  Oracle

Oracle TO MySql quey
Posted by: raj piso
Date: October 07, 2008 11:45AM

Hi All,

Following is my Oracle query that has join. I already have corresponding tables in Mysql ready. Only thing I need to do is convert following Oracle Query to Mysql query. Any expert can help ?

select


bill_to_customer_name,

bill_to_customer_id,

(sysdate -1) prev_cal_date,

sum(decode(trunc(invoiced_date), trunc(sysdate -1), 1, 0) * accounted_selling_price * pos_quantity) prevday,

sum(decode(p.period_name, c.period_name, 1, 0) * accounted_selling_price * pos_quantity) currmonth,

sum(decode(p.period_name, to_char(add_months(sysdate,-1) ,'MON-') || substr(c.period_year,3), 1, 0) * accounted_selling_price * pos_quantity) prevmonth,



sum(decode(p.quarter_num || p.period_year, '1' || c.period_year, 1, 0) * accounted_selling_price * pos_quantity) q1,

sum(decode(p.quarter_num || p.period_year, '2' || c.period_year, 1, 0) * accounted_selling_price * pos_quantity) q2,

sum(decode(p.quarter_num || p.period_year, '3' || c.period_year, 1, 0) * accounted_selling_price * pos_quantity) q3,

sum(decode(p.quarter_num || p.period_year, '4' || c.period_year, 1, 0) * accounted_selling_price * pos_quantity) q4

from



fpc_billing_srep_v d,



gl.gl_peri p,



gl.gl_peri c

where d.org_code = :OrgCode



and nvl(line_type,' ') = 'LINE'

and nvl(d.product_type,' ') 'Freight'

and d.invoiced_date>= c.year_start_date

and invoiced_date > p.start_date-1

and invoiced_date < p.end_date+1

and trunc(sysdate) > c.start_date-1

and trunc(sysdate) < c.end_date+1

and nvl(accounted_selling_price,0) <> 0



and nvl(pos_quantity,0) 0



and nvl(cde_flag,' ') 'Y'

group by



bill_to_customer_id,

bill_to_customer_name

order by



bill_to_customer_name



It gives me error. at trunc(sysdate -1), 1, 0) * accounted_selling_price * pos_quantity) prevday.

Also, in above query whereever there is sysdate I want to use 30-Sep-2008 date instead.

Anyone expert in Oracle and Mysql can help me converting above query in MySql ?

thanks in advance,
pp

Options: ReplyQuote


Subject
Views
Written By
Posted
Oracle TO MySql quey
6060
October 07, 2008 11:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.