BusinessObjects Board

Prompt in a derived table

How do you put a prompt in a derived table?

I will appreciate if someone can help me.

Thanks,
Purnima Sharma


prnmsharma :us: (BOB member since 2008-03-18)

Quite simple – you just embed the @prompt code within the SQL source of the table. For example:


SELECT
   table.field1,
   table.field2,
   @prompt(...)
FROM
   table

Joe


joepeters :us: (BOB member since 2002-08-29)

If you think you’re going to turn your derived table into a materialised view/indexed view in the future, then you may want to build the prompt as a self-restricting join (remembering to add it to the appropriate context(s)!)

Thanks for the quick reply. Now that I am able to put a prompt, how do I show a LOV with an option of ‘ALL’?

Thanks a lot!
Purnima Sharma


prnmsharma :us: (BOB member since 2008-03-18)

You will find answers in Designer’s FAQs here:


Marek Chladny :slovakia: (BOB member since 2003-11-27)

All those articles relate to a LOV from a table in a database. I need to put a LOV from the database table in the derived table. The syntax listed in the articles don’t work. Folowwing is the syntax I am using:

select icn_no, period, year, begin_odom, end_odom, miles_driven, odom_desc
from (SELECT VEHICLE_ACCESS.icn_no,
kellogg_periods.period,
kellogg_periods.year,
odometer_history_datasource.description odom_desc,
first_value(ODOMETER_HISTORY.odometer) over(partition by ODOMETER_HISTORY.icn_no, kellogg_periods.period, kellogg_periods.year order by ODOMETER_HISTORY.odom_date asc) begin_odom,
first_value(ODOMETER_HISTORY.odometer) over(partition by ODOMETER_HISTORY.icn_no, kellogg_periods.period, kellogg_periods.year order by ODOMETER_HISTORY.odom_date desc) end_odom,
/* miles driven */
(first_value(ODOMETER_HISTORY.odometer)
over(partition by ODOMETER_HISTORY.icn_no,
kellogg_periods.period,
kellogg_periods.year order by ODOMETER_HISTORY.odom_date desc)) -
(first_value(ODOMETER_HISTORY.odometer)
over(partition by ODOMETER_HISTORY.icn_no,
kellogg_periods.period,
kellogg_periods.year order by ODOMETER_HISTORY.odom_date asc)) miles_driven,
row_number() over(partition by odometer_history.icn_no, kellogg_periods.period, kellogg_periods.year order by odometer_history.odom_date desc) row#
FROM VEHICLE_ACCESS,
ODOMETER_HISTORY,
ODOMETER_HISTORY_DATASOURCE,
kellogg_periods
WHERE (ODOMETER_HISTORY.DATASOURCE =
ODOMETER_HISTORY_DATASOURCE.DATASOURCE)
AND (VEHICLE_ACCESS.ICN_NO = ODOMETER_HISTORY.ICN_NO)
and (odometer_history.odom_date >= kellogg_periods.begin_date and
odometer_history.odom_date <= kellogg_periods.end_date)
AND (VEHICLE_ACCESS.LESSEE_CODE = ‘0G97’)
and ODOMETER_HISTORY_DATASOURCE.DESCRIPTION in (@prompt(‘Enter Data Source’,‘A’,‘ODOMETER_HISTORY_DATASOURCE\description’,multi,free)))
where row# = 1

When I put the table name, it gives me an error.

Thanks,
Purnima Sharma


prnmsharma :us: (BOB member since 2008-03-18)

Essentially a derived table is a view, which is SQL script that runs when you execute the SQL, so how would you put an LoV into a SQL script?

Performance wise you could create a table that holds your LoV’s and link that table to your derived table. Then alter the SQL that generates the LoV in the properties of the object associated with the LoV.


dessa :madagascar: (BOB member since 2004-01-29)

The 3rd parameter of the @prompt() function is not a pointer to a database table.column.

It needs to point to a class/object in the universe. Please have a look at the explanation/documentation of the @prompt() function and its parameters.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

It still doesn’t work, probably because it is derived table. Following is the revised sql:

and ODOMETER_HISTORY_DATASOURCE.DESCRIPTION in (@prompt(‘Enter Data Source’,‘A’,‘Kelloggs Period Odometers\Odometer Source’,multi,free)

It gives me an error ‘Invalid definition’.

Thanks,
Purnima


prnmsharma :us: (BOB member since 2008-03-18)

You are missing a closing parenthesis :wink: However if that was just a “cut & paste” error, then I’d check that the object “Odometer Source” is of the correct data type.

Try creating a stand-alone pre-defined condition in your universe using just your prompt.

Try replacing the @prompt with a static value (for now) in your derived table.

Let us know how you go.

Pete


Peter Hughes :uk: (BOB member since 2005-11-21)

It was a pasting error. It works fine in a stand alone object. Also if I use it in a prompt in a database column, it works fine. The only problem is when you use it in a derived table. Somehow, it doesn’t accept the column from a database in a derived table.

Thanks,
Purnima Sharma


prnmsharma :us: (BOB member since 2008-03-18)

If it does not work this way (as a condition in the query of the derived table) then create the derived table without it and then add the same condition as a self-restricting join applied on the derived table.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

I have to use it in the derived table, the reason being that the prompt is in the inner query (please see the query in the first post). The derived table uses analytical function to get one row per vehicle in the outer query and then I join the results of the outer query to another detail table. If I do it according to your suggestion, it will result in multiple rows which I want to avoid. In such condition, I am not able to use certain fileds as measures.

Thanks,
Purnima


prnmsharma :us: (BOB member since 2008-03-18)

If you add it as a self-restricting join, it applies it to the table for every occasion that anything from that table is used, derived or otherwise.

I believe what he is saying is that without the prompt inside the derived table, the output from the derived table is wrong. So a stub join would not provide the same functionality.


Dave Rathbun :us: (BOB member since 2002-06-06)

How many distinct values of ODOMETER_HISTORY_DATASOURCE.DESCRIPTION are there?

The thing is that using the prompt you are trying to limit the inner select only to one DESCRIPTION and then to work with the results in the outer select.

What if, if there are not so many distinct DESCRIPTION values, what if the inner select gave you the same results as it is giving you now but for all distinct values of DESCRIPTION and then the one needed DESCRIPTION value would be filtered by a self-restricting join applied on the derived table?

The SQL for the derived table would look something like this:

SELECT icn_no,
       period,
       year,
       odom_desc,
       begin_odom,
       end_odom,
       miles_driven
  FROM ( SELECT VEHICLE_ACCESS.icn_no,
                kellogg_periods.period,
                kellogg_periods.year,
                odometer_history_datasource.description odom_desc,
                first_value(ODOMETER_HISTORY.odometer)
                    over(partition by ODOMETER_HISTORY.icn_no, kellogg_periods.period, kellogg_periods.year, odometer_history_datasource.description order by ODOMETER_HISTORY.odom_date asc) begin_odom,
                first_value(ODOMETER_HISTORY.odometer)
                    over(partition by ODOMETER_HISTORY.icn_no, kellogg_periods.period, kellogg_periods.year, odometer_history_datasource.description order by ODOMETER_HISTORY.odom_date desc) end_odom,
                /* miles driven */
                (first_value(ODOMETER_HISTORY.odometer)
                    over(partition by ODOMETER_HISTORY.icn_no, kellogg_periods.period, kellogg_periods.year, odometer_history_datasource.description order by ODOMETER_HISTORY.odom_date desc))
                  -
                  (first_value(ODOMETER_HISTORY.odometer)
                      over(partition by ODOMETER_HISTORY.icn_no, kellogg_periods.period, kellogg_periods.year, odometer_history_datasource.description order by ODOMETER_HISTORY.odom_date asc)) miles_driven,
                row_number() over(partition by odometer_history.icn_no, kellogg_periods.period, kellogg_periods.year, odometer_history_datasource.description order by odometer_history.odom_date desc) row#
           FROM VEHICLE_ACCESS,
                ODOMETER_HISTORY,
                ODOMETER_HISTORY_DATASOURCE,
                kellogg_periods
          WHERE (ODOMETER_HISTORY.DATASOURCE = ODOMETER_HISTORY_DATASOURCE.DATASOURCE)
            AND (VEHICLE_ACCESS.ICN_NO = ODOMETER_HISTORY.ICN_NO)
            AND (odometer_history.odom_date >= kellogg_periods.begin_date AND
                 odometer_history.odom_date <= kellogg_periods.end_date)
            AND (VEHICLE_ACCESS.LESSEE_CODE = '0G97')
--            AND ODOMETER_HISTORY_DATASOURCE.DESCRIPTION in (@prompt('Enter Data Source','A','ODOMETER_HISTORY_DATASOURCE\description',multi,free))
       )
 WHERE row# = 1 

As you can check, I added

, odometer_history_datasource.description

into every

over(partition by 

clause.

Create a derived with this syntax and then create a self-restricting join (with @prompt() function) on ODOM_DESC column of the derived table.

I think this should work. However, if there are too many possible distinct values in the ODOMETER_HISTORY_DATASOURCE.DESCRIPTION column then the performance may not be excellent.

Give it a try and let us know :slight_smile:


Marek Chladny :slovakia: (BOB member since 2003-11-27)

:+1: Yes, just seen that it’s in the subquery within the derived table.