#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Cochin, India
    Posts
    99
    Rep Power
    6

    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Cochin, India
    Posts
    99
    Rep Power
    6
    Originally Posted by r937
    Code:
    SELECT x.bill_no...
    That worked correctly
    Thank you very much

    Anees

IMN logo majestic logo threadwatch logo seochat tools logo