Discussion Board for collaboration on QlikView Scripting.
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.
cast(datediff(day, bsc.entrydate, salesdate) / 10 as int) as daycount,
cl.name as clname,
bsc.name as customer,
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
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;
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..
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.)
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.