I am trying to pull an object as a list of values. The object is run_date.datelist, this is just a list of dates from the rapidus_values table. This is giving me a syntax error. I need this to dynamic, i.e. sql driven or an object.
This is not the entire sql statement. I can’t build this report using Business Objects Reporter, it has to be free-hand sql.
select a.field_name, a.value_count as Current_Count, from rapidus_values a,
where a.run_date = @Prompt(‘Select the current Rapidus Build Date’,‘a’,{run_date.datelist},multi,free)
Might need to be a ‘D’ for type (date) instead of ‘a’ (alphanumeric).
where a.run_date = @Prompt(‘Select the current Rapidus Build Date’,‘D’,{run_date.datelist},multi,free)
Mike
select a.field_name, a.value_count as Current_Count, from rapidus_values a,
where a.run_date = @Prompt(‘Select the current Rapidus Build Date’,‘a’,{run_date.datelist},multi,free)
At 04:08 PM 3/17/2000 -0500, you wrote:
I am trying to pull an object as a list of values. The object is run_date.datelist, this is just a list of dates from the rapidus_values table. This is giving me a syntax error. I need this to dynamic, i.e. sql driven or an object.
This is not the entire sql statement. I can’t build this report using Business Objects Reporter, it has to be free-hand sql.
select a.field_name, a.value_count as Current_Count, from rapidus_values a,
where a.run_date = @Prompt(‘Select the current Rapidus Build Date’,‘a’,{run_date.datelist},multi,free)
In a message dated 00-03-17 16:19:51 EST, you write:
This is not the entire sql statement. I can’t build this report using
Business Objects Reporter, it has to be free-hand sql.
I’m curious; can you provide details on why you cannot use the Reporter module?
select a.field_name, a.value_count as Current_Count,
from rapidus_values a,
where a.run_date = @Prompt(‘Select the current Rapidus Build Date’,‘a’,{run_date.datelist},multi,free)
This syntax is incorrect. If you need a list of values in a prompt for free hand SQL, then you must hard code the list. It looks like you are attempting to reference a table.column combination, and that is not supported. You cannot reference an object in FHS, as there is no universe in use.
I would suggest (as has someone else) that you provide a prompt for the date but do NOT attempt to provide a list of values. Let the user enter a date. You would, however, want to alter the prompt type to ‘D’ from ‘A’, as that way you will at least have BusObj validate the user input against correct date values.
I am actually selecting the same columns from the same table with 2 different dates. These are counts of a weekly process. I need the variance between each columns counts. I only going to keep 4 weeks worth of data, therefore only 4 dates. The user will not know the exact date, they need a LOV. I used the suggestion by Kathy Vazquez and it I am allowed to enter the dates at the prompt, but no LOV.
Here is the entire statement:
select a.field_name, d.long_description, a.field_value, a.value_count as Current_Count,
c.value_count as Previous_count, sum(a.value_count - c.value_count) from rapidus_values a, rapidus_values_conf b, dm_answerset_lu d, (select c.value_count, c.field_value, c.field_name
from rapidus_values c
where c.run_date = @Prompt(‘Select the Previous Rapidus Build Date’,‘a’,multi,free)) c where a.field_name = b.field_name (+)
and c.field_name = a.field_name
and c.field_value = a.field_value
and b.count_type = ‘VC’
and a.run_date = @Prompt(‘Select the current Rapidus Build Date’,‘a’,multi,free)
and d.new_code = a.field_value
group by a.field_name, d.long_description, a.field_value, a.value_count, c.value_count
The report would look some thing like this:
(fieldname) (count of 03/15/2000) (variance) (count of 03/07/2000) customer type1 12 2 10
customer type2 30 10 20
the table looks like this:
field name varchar(100);
count number(10);
date number(8); # (I have converted the date to a number)