Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

OLEDB Error-Can't figure out

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;

6 Replies
Not applicable

Re: OLEDB Error-Can't figure out

Scratch all this...somebody sold a boat that was 9 feet long...that meant my code trimmed it to 9' (including the " ' ")

loveisfail
Not applicable

Re: OLEDB Error-Can't figure out

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..

Life is so rich, and we need to respect to the life !!!
kush141087
Not applicable

Re: OLEDB Error-Can't figure out

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

Not applicable

Re: OLEDB Error-Can't figure out

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.)

loveisfail
Not applicable

Re: OLEDB Error-Can't figure out

Jerry,

Can you please elaborate more.

If possible, Please tag the error message...

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: OLEDB Error-Can't figure out

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.