Skip to Content
0

ALL in @Prompt SQLServer

Jul 12, 2017 at 09:54 PM

63

avatar image

HI,

I am in the process of converting Oracle universe to a SQL Server Universe.

While converting @prompt filter object I get error "Invalid syntax error at 'StringValue'"........Looks like this is a very generic error but tricky to solve.

Here is the condition in oracle univ.

@Select(Policy\Policy Link Name) like @Prompt('60. Enter Policy Link (Type ALL for All Policy Link Names):', 'A', 'Policy\Policy Link Name List',Mono,Free,not_Persistent,{'ALL'},user:15) OR ('ALL' in @Prompt('60. Enter Policy Link (Type ALL for All Policy Link Names):', 'A', 'Policy\Policy Link Name List',Mono,Free,not_Persistent,{'ALL'},user:15) )

I did try to replace the code with different operands and arguments in sqlserver univ but not able to solve it.

It is important to use Like in this scenario as our LOV's are %..%

Please advice

Thank You!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Arijit Das Jul 13, 2017 at 07:35 AM
0

@Prompt syntax is not dependent on database. In the second condition after OR operator you used 'in' operator along with 'mono' selection option. Correct syntax would be a combination of 'in' and 'multi' or '=' and 'mono'.

-------------------------------------------------

Try:

@Select(Policy\Policy Link Name) like
@Prompt('60. Enter Policy Link (Type ALL for All Policy Link Names):',
'A',
'Policy\Policy Link Name List',
Mono,
Free,
not_Persistent,
{'ALL'},
user:15
)
OR
(
'ALL' =
@Prompt('60. Enter Policy Link (Type ALL for All Policy Link Names):',
'A',
'Policy\Policy Link Name List',
Mono,
Free,
not_Persistent,
{'ALL'},
user:15
)
)

Share
10 |10000 characters needed characters left characters exceeded
Chaitanya Reddy Jul 13, 2017 at 01:11 PM
0

Hi Arijit,

I understand that @functions are independent of DB type....I was trying to say that the same syntax works absolutely fine in Oracle DB

I did try the code you mentioned ....and I get incorrect syntax near ',' error

@Select(Policy\Policy Link Name) LIKE @Prompt('60. Enter Policy Link (Type ALL for All Policy Link Names):', 'A', 'Policy\Policy Link Name List',Mono,Free,not_Persistent,{'ALL'},user:15)
OR ('ALL' = @Prompt('60. Enter Policy Link (Type ALL for All Policy Link Names):', 'A', 'Policy\Policy Link Name List',Mono,Free,not_Persistent,{'ALL'},user:15) )

Show 1 Share
10 |10000 characters needed characters left characters exceeded

The same syntax works fine with me.

Are you using UDT or IDT ? What are the steps you are following to convert Oracle universe to a SQL Server Universe ?

0
Chaitanya Reddy Jul 17, 2017 at 11:18 PM
0

Hi,

I am using UDT.

I change connection to sql server

Change table owner name to reflect sqlserver owner name

run integrity check

This process works fine for all other 20 universe that I have and reports matches perfectly. Let me know if I am missing anything?

Does your object have values like.... %...%???

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Can you share a screenshot of the error ? Are you using any custom LOV object ?

0

We are not using custom LOV.

Policy Link Name List object defined as

'%' + DBO.RPT_PLAN_GRP_POLICY_LNK_NAME.POLICY_LINK_NAME + '%'

g3eli.png (14.1 kB)
0