error in query when adding prompts

[Former Member]

Hello Everyone,

I have a simple query which is working just fine in query manager. Now, I need to narrow the query by adding two prompts. However, when I add a prompt, it will complain with an error saying:

Incorrect Syntax Near pch1.itemcode...

However, I am NOT using that table.. in fact, the query access an external table...

I have read note 0000730960 SAP Business One does not identify variables in long queries, but this is not the case.

I have reduced the query to just:

select

t0.airline_id as 'Airline'

from

hostostmp..flights t0

where

t0.airline_id='[%0]'

but it is still giving me the error...

Even more, if I change the query for some actual real value, it will work. For instace, t0.airline_id='MP' it will work like a charm.

I use prompts (up to 3 prompts) in many other querys with no problems...

But this one, is a really wierd one. Support insists in reducing the query, based on the note, but this is simple a shot in the dark. They have no idea.

So, any hint will certainly be apprecieted.

Best regards,

Leo

[former_member201110]

Hi Leonardo,

I'm afraid I don't think you can use prompts on external tables. Your other queries you mention, are the prompts on SBO tables or external tables?

As you know, when a prompt is inserted in to a query, SBO generates a separate window that allows the user to enter values for any prompts. If SBO does not recognise the table as a system table then it cannot generate the prompt window and the query fails (..and you get that misleading error message).

Kind Regards,

Owen