6 Replies Latest reply: May 20, 2016 1:54 PM by Jerry Kobriger RSS

    OLEDB Error-Can't figure out

    Jerry Kobriger

      Hello, I'm using the following SQL expression and getting an OLEDB error returned that I cannot figure out. If I remove the lines that I've highlighted, of course I don't get my full data set, but it does return without error. Any suggestions?

      PS this script does work in DatabaseBrowser.

       

      select bsc.id,

      salesdate,

      bsc.entrydate,

      cast(datediff(day, bsc.entrydate, salesdate) / 10 as int) as daycount,

      commissionedsalesperson,

      cl.name as clname,

      bmtdefaultloc,

      splitpercentage,

      store_name,

      worksheetprofit,

      bsc.name as customer,

      bsc_commissions.bsc_id,

      bmi.year,

      bmi.description,

      CAST(LEFT(length,2)AS int) as lengthft,

      (cast(datediff(day, bsc.entrydate, salesdate) / 10 as int) * CAST(LEFT(length,2)AS int)) as points

      from bsc

      left outer join bsc_commissions

      on bsc.id = bsc_commissions.bsc_id

      left outer join cl

      on bsc_commissions.commissionedsalesperson = cl.cl_id

      left outer join store_info

      on cl.bmtdefaultloc = store_info.store_id

      left outer join bsc_boats

      on bsc.id = bsc_boats.bsc_id

      left outer join bmi

      on bsc_boats.boatmodelid = bmi.id

      where writtendate >= '01/01/2016'

      and commissionedsalesperson is not null

      and cl.name is not null

      and bsc.status = 'o'

      and splitpercentage >= '0.5'

      order by commissionedsalesperson asc;