I tried running two reports using standard Business Objects procedures. Report #1 was successful and can be run as script external to Business Objects.
When I try to run the first report as sql script within Business Objects, I get an error message. Doesn’t Business Objects support the ‘&varname’ as a prompt?
Report #1
select p.pt_name,p.pt_mrn,v.vis_adm_datm,l.loc_datm,l.loc_name from patient p,visit v, pat_loc_hist l
where p.pt_mrn=v.pt_mrn
and vis_adm_datm between ‘&begin_date’ and ‘&end_date’
and v.vis_last_pt_type=‘IP’
and v.vis_idx_id=l.vis_idx_id
and substr(l.loc_name,1,(instr(l.loc_name,’:’) -1)) in
(‘PICA’,‘PICB’)
order by p.pt_mrn,v.vis_adm_datm,l.loc_datm;
When I run another report, Business Objects also seems to not accept the exists.
Report #2
select p.pt_name,p.pt_mrn,v.vis_adm_datm,l.loc_datm,l.loc_name from patient p,visit v, pat_loc_hist l
where p.pt_mrn=v.pt_mrn
and vis_adm_datm between ‘&begin_date’ and ‘&end_date’
and v.vis_last_pt_type=‘IP’
and v.vis_idx_id=l.vis_idx_id
and exists(
select ‘x’ from pat_loc_hist l
where v.vis_idx_id=l.vis_idx_id
and substr(l.loc_name,1,(instr(l.loc_name,’:’)-1))
in (‘PICA’,‘PICB’));
Both of these are syntactically correct and produce desired results as direct script. Does anyone have suggestions how I can incoporate into Business Objects?
Thank you.
When I try to run the first report as sql script within BusinessObjects, I get an error message. Doesn’t Business Objects support the ‘&varname’ as a prompt?
Hi, Bruce!
You didn’t mention what the error message was – some kind of helpful message like ‘syntax error’?
So, are you saying that you want to prompt the user for begin date and end date in BusinessObjects? The SQL within BO would include the @Prompt or the @Variable functions. The easiest way to accomplish this would be through the query builder if you have a universe built.
The format might appear like this in freehand SQL:
WHERE Creation_Date > @Prompt(‘Enter
Date:’, ‘D’,)
Please excuse re syntax message.
It was approximately - nonnumeric character found where numeric expected .
My guess is that Business Objects expected a date format and instead, per the query, received the ‘&varname’. Isn’t this supported in free-hand sql within Business Objects?
Although I could build using query builder, I wanted to learn something about building free-hand sql scripts based on a Business Objects report that I already knew works.
Also, any suggestions about the EXISTS clause, or how to do in Business Objects?
mlmalone@ATT.NET 02/29 12:39 PM >>>
When I try to run the first report as sql script within BusinessObjects, I get an error message. Doesn’t Business Objects support the ‘&varname’ as a prompt?
Hi, Bruce!
You didn’t mention what the error message was – some kind of helpful message like ‘syntax error’?
So, are you saying that you want to prompt the user for begin date and end date in BusinessObjects? The SQL within BO would include the @Prompt or the @Variable functions. The easiest way to accomplish this would be through the query builder if you have a universe built.
The format might appear like this in freehand SQL:
WHERE Creation_Date > @Prompt(‘Enter
Date:’, ‘D’,)
Well, my last response showed how you would use a prompt to get these values. I don’t know what &varname is – is this a local BO variable (@variable(varname)) or something else?
I think there is something in the list server about the EXISTS clause – search under EXISTS to find the related messages.
&varname was a shorthand for the date to be inserted in the query. SQL permits use of ‘&variable name’ to be used in code to allow dates, etc to be inserted as with prompts in Business Objects. When I use this syntax outside of Business Objects I receive valid data. When I use this syntax within Business Objects the error message non-numeric data found where numeric data expected is produced. This is evidently due to Businesss Objects rejecting the standard sql prompt syntax when used in free-hand sql.
Has anyone found a way to get around this limitation so I can use sql script to obtain data seemingly unable to obtain using standard Business Objects drop and drag, such as the EXISTS clause? Post this discussion I have played with the EXISTS clause to produce desired results using free-hand sql, but I still am unable to include a prompt with free-hand sql and wondered if someone else has done so.
Well, my last response showed how you would use a prompt to get these values. I don’t know what &varname is – is this a local BO variable (@variable(varname)) or something else?
I think there is something in the list server about the EXISTS clause – search under EXISTS to find the related messages.
Well, my last response showed how you would use a prompt to get these values. I don’t know what &varname is – is this a local BO variable (@variable(varname)) or something else?
I think there is something in the list server about the EXISTS clause – search under EXISTS to find the related messages.