Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Scratch all this...somebody sold a boat that was 9 feet long...that meant my code trimmed it to 9' (including the " ' ")
Hey,
Every time, you are using with bsc extension like bsc.id. But, your table name is bsc only..
Try to use Alias name....
Update: Please post me with error..
try to assign table alias to field
CAST(LEFT(bsc.length,2)AS int) as lengthft,
give alias of the table from which field is coming
Thank you, but the problem actually was because I was left trimming to two characters, and somebody sold a 9 foot boat. So, the data pulled in as 9' and with the " ' " it caused an error. I still haven't been able to figure out how to separate feet and inches when the data is stored as 10'11 (ten feet eleven inches.)
Jerry,
Can you please elaborate more.
If possible, Please tag the error message...
Yes, this stemmed from a previous issue. Our data is stored as text in SQL in the format "ft'in" so that ten feet, five inches would be 10'5
Unfortunately I couldn't figure out a good way to separate the feet and inches, so I figured a trim function would work, hence the CAST(LEFT....
What that resulted in was that SQL would select just the first two characters, and that was fine until somebody sold a 9' boat. Then the the " ' " got included in the selected data.
So ultimately what I'm looking for is a good way to select everything before the " ' " and then select everything after the " ' " so that I get the result of feet and inches being separate.