locked
ODBC--Call failed, Missising Expression #936 RRS feed

  • Question

  • I edited this SQL query to pull data from Oracle but it gives the error above, what could be the cause of this error?

    select * from (with dso as (select year, month, product_line_id, third_party_cust_indic as sales_type, 
                        sum(beg_snb_sales) + sum(beg_snb_ra) as beg_snb_sales, 
                        sum(snb_sales) + sum(snb_ra) as snb_sales, 
                        sum(beg_snb_cost) as beg_snb_cost,
                        sum(snb_cost) as snb_cost,
                        sum(spa) as spa_amount,
                        sum(invoiced_sales) + coalesce(sum(ic_extended_price),0) as invoiced_sales,
                        sum(gross_invoiced_sales) as gross_invoiced_sales,
                        sum(invoiced_cost)+ coalesce(sum(ic_extended_cost),0) as cost,
                        sum(beg_bl_future) + sum(beg_bl_dated) + sum(beg_bl_alloc) + sum(beg_bl_not_produced) as beginning_backlog,
                        sum(bl_future) + sum(bl_dated) + sum(bl_alloc) + sum(bl_not_produced) as current_backlog,
                        sum(bl_alloc) as bl_alloc,
                        sum(bl_dated) as bl_dated,
                        sum(bl_future) as bl_future,
                        sum(bl_not_produced) as bl_not_produced,
                        sum(promos) + sum(edi_disc_allowance) + sum(new_store_allowance) + sum(ord_level_disc_allowance) + sum(rdc_allowance) + sum(cert_rec_allowance)
                        + sum(defective_allowance) + sum(freight_allowance)  + sum(adv_coop_allowance) + sum(store_svc_allowance) + sum(promo_amt) + sum(below_min_order_fee) as order_level_discounts,
    manufacturing_location, inv_legal_entity || ' ' || inv_legal_entity_desc as shipping_location
                       
               from prodsales.monthly_sales_orders
              where year = 2018
                and month = 10
                and (CUST_LEGAL_ENTITY = '001' )
             --  or ar_legal_entity = '001')
                and PRODUCT_LINE_ID <> '000'
                and BUSINESS_UNIT_ID Not in ('OI','IC','BI','RF') 
              group by year, month, product_line_id, third_party_cust_indic, manufacturing_location, inv_legal_entity || ' ' || inv_legal_entity_desc
             )
    , Pl as (select distinct product_line_id as pl from dso)
    , YN as (select distinct sales_type as slstyp, year, month, from dso)
    , pl_indic as (select * from pl, yn)
       
    select p.year, p.month,  product_grouping_id as global_product_group,  pl as product_line_id,  coalesce(dso.product_line_id, pl || '++') as pl_method,
               slstyp as sales_type,
               coalesce(invoiced_sales + snb_sales - beg_snb_sales, 0) as  gross_sales_after_ra,
               coalesce(gross_invoiced_sales, 0) as  gross_sales_before_ra,
          --     coalesce(invoiced_sales + snb_sales - beg_snb_sales, 0) - coalesce(order_level_discounts,0) as  gross_sales_after_ra,
          --     coalesce(invoiced_sales, 0) as gross_sales_after_ra,
               coalesce(order_level_discounts,0) as order_level_discounts,
               coalesce(cost, 0) as cost,
               coalesce(beginning_backlog, 0) as beginning_backlog,
               coalesce(current_backlog, 0) as current_backlog,
               coalesce(current_backlog - beginning_backlog, 0) as backlog_change,
                --gross_sales_after_ra + backlog_change
               coalesce((invoiced_sales + snb_sales - beg_snb_sales) + (current_backlog - beginning_backlog), 0) as orders,
         --      coalesce(coalesce(invoiced_sales + snb_sales - beg_snb_sales, 0) - coalesce(order_level_discounts,0) + (current_backlog - beginning_backlog), 0) as orders,
               coalesce(spa_amount, 0) as spa_amount,
               (p.year * 100) + p.month as month_key,
               sysdate as create_date,
               coalesce(bl_alloc,0) as bl_alloc,
               coalesce(bl_dated,0) as bl_dated,
               coalesce(bl_future,0) as bl_future, 
               coalesce(bl_not_produced,0) as bl_not_produced, 
               coalesce(invoiced_sales,0) as invoiced_sales, 
              
               coalesce(snb_sales,0) as snb_sales,  
               coalesce(beg_snb_sales,0) as beg_snb_sales,
    manufacturing_location,  shipping_location
          from pl_indic p
    left join dso
            on p.pl = dso.product_line_id
           and p.slstyp = dso.sales_type
    inner join prodsales.rpt_product_grouping_v plv
            on p.pl = plv.product_line_id
        order by pl)

    Monday, October 28, 2019 2:43 PM

All replies

  • You are using a passthrough query, correct?

    -Tom. Microsoft Access MVP

    Monday, October 28, 2019 3:12 PM