cancel
Showing results for 
Search instead for 
Did you mean: 

Need help with a derived table expression code

Former Member
0 Kudos

Hello all ,

I'm new to this. I'm trying to get a derived table from SQL using the following where clause in my select and "It cannot process" I get to explanation on why this doesnt work, The clause works great in SQL query but when I try to use it in expression editor it doesnt. Please help !!!

where

Convert(INTEGER,substring(@Variable('BOUSER'),1,charindex('-',@Variable('BOUSER'))-1))=0 or Convert(integer,substring(@Variable('BOUSER'),1,charindex('-',@Variable('BOUSER'))-1))= dbo.Facility.FacilityNumber

Thank you all in advance!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Dragos,

Are you using SQL Server database?

What error do you get when you parse this statement?

Can you explain what you are exactly trying to achieve so that we can try to tweak the code if possible and check?

Would also like to know the BO version?

Regards,

Yuvraj

Former Member
0 Kudos

Hello Yuvraj,

Thanks for the reply

Yes I am using a SQL database BO version 4.0. I am trying to get the digits before the "-" char in the user id (@variable('BOUSER')) to match it with the facility number corresponding with that specific user.

The error I get is  "

[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared."

The code works in an SQL stored procedure with no issues but as soon as I copy it in the derived table and add the @variable('BOUSER') I get that error.

Thanks

Dragos

Former Member
0 Kudos

Hi Dragos,

Can you try using CAST function instead of CONVERT function and check?

Regards,

Yuvraj

Answers (0)