free-hand sql with lov promtpt

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)


Listserv Archives (BOB member since 2002-06-25)

Mark

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)

OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’ Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’


Listserv Archives (BOB member since 2002-06-25)

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.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com


Listserv Archives (BOB member since 2002-06-25)

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)


Listserv Archives (BOB member since 2002-06-25)