ALL in @Prompt SQLServer

[Former Member]

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!

[Former Member]

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

[arijit_das]

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

[Former Member]

We are not using custom LOV.

Policy Link Name List object defined as

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

[Former Member]

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

[arijit_das]

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 ?

[arijit_das]

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