Skip to Content
avatar image
Former Member

ALL in @Prompt SQLServer

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jul 13, 2017 at 07:35 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 13, 2017 at 01:11 PM

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

    Add comment
    10|10000 characters needed 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 ?

  • avatar image
    Former Member
    Jul 17, 2017 at 11:18 PM

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

    Add comment
    10|10000 characters needed characters exceeded