Hello! In my report I run a subq 1

[thrybergh]

Hello!

In my report I run a subquery which looks at a series of flags held for a particular activity number. If one of the flags is set to "Q" we know that this activity has a "fixed price". When we have a fixed price activity, we calculate costs differently.

I need to be able to set a flag to indicate that we are dealing with a fixed price activity. My problem is that I do not know how to set a flag (or "global variable&quot so that I can check it later. I have tried (using a formula) but receive an error because another formula references the column with an incompatible frequency.

I would be grateful for any advice

Thank you.

 

[sem]

You should place your column to the right group. When you have some grouping (suppose emp grouped by dept), you can not place non-agregated info about employee salary to the level of dept, because there's a number of salaries. You need to use some kind of aggregation (selecting any first obtained value is also agregation!) or to place this field to the "lower" level (emp). It depends on your specific task, so I can not provide direct decision, just an explanation of your error. I think that in your case subquery may potencially (as any query!) return more than 1 record. To choose only 1, you may use calculated field instead of subquery.

Another, pure pl/sql decision, is to store all you need in some package variable(s). Then you may operate them in report triggers.

Regards, Dima

 

[thrybergh]

Thank you. My subquery is returning a count of the number of "Q" values.

I will investigate "package variables" and see if I can use them to help me.

 

[thrybergh]

Hello Sem.

I created a library and this allowed me to have a global flag which I can set once I have counted the "Q" records. I created a procedure to set the flag value and called this from the group filter on the subquery. I can then test the flag value in my other formula and calculate the costs differently from an if/else condition.

Thank you for pointing me in the right direction!

:->

 

[olobs]

I am wondering if you could achieve the use of global variable through Oracle package or PL/SQL?