Syntax error in query

Dev Shed Forums Sponsor:

September 30th, 2012, 07:30 AM

anees_muhd

Contributing User

Join Date: Jul 2008

Location: Cochin, India

Posts: 99

Time spent in forums: 1 Day 11 h 42 m 58 sec
Reputation Power: 5

Syntax error in query

Hi
this is the query that worked correctly in informix database
now i am trying to work that in mysql, but shows syntax error near outer.

Code:

select x.bill_no, sum(invoice_details.line_orig_amt) as Charges,sum(invoice_details.line_balance) as Balance, sum(invoice_details.line_orig_amt)-sum(invoice_details.line_balance) as Payments, 
sum(g.deferred) as deferred, max(x.due_date) as due_date, max(x.eff_date) as eff_date, max(x.hold_date) as hold_date 
from invoice_header x, invoice_details, trans_header, 
outer 
(select y.bill_no, sum(deferred_details.amount) as deferred, invoice_details.bill_no as new_bill_no, 
invoice_details.bill_line_no as new_bill_line_no
 from deferred_details, invoice_details, invoice_header y 
where 
invoice_details.bill_no = deferred_details.bill_no 
and invoice_details.bill_line_no = deferred_details.bill_line_no 
and y.bill_no = invoice_details.bill_no 
and y.bill_status in ('A','P')
and y.bill_cust_code = '805872958' 
group by y.bill_no, invoice_details.bill_line_no, invoice_details.bill_no ) g
 where 
x.bill_no = invoice_details.bill_no
and g.new_bill_no = x.bill_no 
and g.new_bill_line_no = invoice_details.bill_line_no 
and invoice_details.item_code = trans_header.item_code 
and x.bill_status in ('A','P') 
and x.bill_cust_code = '805872958' 
and x.bill_no = g.bill_no group by x.bill_no

could some one please help me on this

Thanks

Anees

Last edited by anees_muhd : September 30th, 2012 at 07:48 AM.

September 30th, 2012, 08:04 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,357

Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 23 sec
Reputation Power: 4140

Code:

SELECT x.bill_no
     , SUM(invoice_details.line_orig_amt) AS Charges
     , SUM(invoice_details.line_balance) AS Balance
     , SUM(invoice_details.line_orig_amt) -
       SUM(invoice_details.line_balance) AS Payments
     , SUM(g.deferred) AS deferred
     , MAX(x.due_date) AS due_date
     , MAX(x.eff_date) AS eff_date
     , MAX(x.hold_date) AS hold_date 
  FROM invoice_header AS x
INNER
  JOIN invoice_details
    ON x.bill_no = invoice_details.bill_no
   AND x.bill_status in ('A','P') 
   AND x.bill_cust_code = '805872958' 
INNER
  JOIN trans_header
    ON trans_header.item_code = invoice_details.item_code 
LEFT OUTER
  JOIN ( SELECT y.bill_no
              , SUM(deferred_details.amount) AS deferred
              , invoice_details.bill_no AS new_bill_no
              , invoice_details.bill_line_no AS new_bill_line_no
           FROM deferred_details
         INNER
           JOIN invoice_details
             ON invoice_details.bill_no = deferred_details.bill_no 
            AND invoice_details.bill_line_no = deferred_details.bill_line_no 
         INNER
           JOIN invoice_header AS y 
             ON y.bill_no = invoice_details.bill_no 
            AND y.bill_status in ('A','P')
            AND y.bill_cust_code = '805872958' 
         GROUP 
             BY y.bill_no
              , invoice_details.bill_line_no
              , invoice_details.bill_no ) AS g
    ON g.new_bill_no = x.bill_no 
   AND g.new_bill_line_no = invoice_details.bill_line_no 
   AND g.bill_no = x.bill_no
GROUP 
    BY x.bill_no

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book