Facing data type error when changing the order of fields in a query

[Former Member]

Hello Experts

I am working on E sourcing 5.1.

I have a query with some filters . I want to change the position of a particular filter.

Basically I want to move a filter of type date to the top , so that it is the 1st parameter.

I was able to do this by reorder option.

However at runtime it gives me an error saying that "error u201CORA-00932: inconsistent datatypes: expected NUMBER got date."

Later I reverted back my changes so that the date filed is at its original position and faced no issues.

Query worked fine.

Is the order of filters important when executing a query.

Please suggest how can I move the date filter to the top of the query's filter list.

Regards

Vikas

[vikram_shanmugasundaram]

Hi Vikas,

In 5.1, the order of the parameters should patch the order of the filters in the WHERE clause.

In Sourcing 7.0 however, you can use the parameter names in the WHERE clause, so it's much easier.

Hope this helps.

Vikram

[Former Member]

Thanks Vikram

I am not sure if I understand you correctly.

Are you suggesting that I need to modify the query as per the sequence of filter parameters.

Pardon my silly questions , I am very new to this.

Regards

Vikas

[Former Member]

Hi Vikas,

As silly as it may sound, it is true. That is exactly what Vikram meant in his reply. Please make sure that you have a one-to-one correspondence between you filter parameters and its place-holder,i.e. '?', in your query definition.

Things have changed in 7.0 release. You can use named parameters there. This means that you can use a single filter parameter as many times you want in your query.

Good luck!!

Devesh

[Former Member]

Thanks I am not really Sure how to do this , I look at the query and Its gigantic .

when I reorder the filter parameters , Do i have to manually go and edit the select statement or is there a simpler way of doing this.

Regards

Vikas

[Former Member]

Hello Vikas,

Its not the SELECT statements that you need to change. You have to change the WHERE caluse.

In the WHERE clause, some of the conditions will have some table column equated to a '?' symbol. This symbol is a place-holder for a filter prompt. There is a one-to-one correspondence between the '?' symbols and the filter prompts created in the query(under the Filter Prompt tab). This means that the first '?' corresponds to the first filter prompt, second '?' to the second filter prompt and so on.

Keeping the above information in mind, you can attempt to make changes to your query. You can duplicate your existing query and try your hands at it.

One more point worth mentioning : It is evident that if you need to use the same filter prompt at more than once place in the query definition, you have to create more than one instance of that query prompt.(Here by another instance I mean that the two filter prompts should have same values in their fields when you define them.) But even when you create more than one instance of a filter prompt, on the UI it will appear just as a single instance.

If things have become more confusing than before (of which there is a high probability ) then please go through the online help for Queries. It has got an extensive documenation and will be surely be of some help to you.

Good luck!!

Devesh