Multi Statements in Free-hand SQL

I have disabled the “Restrict SQL to SELECT only” but still can’t get past an error message DA0005 "Syntax error: expected something between the ‘rows’ keyword and the ‘create’ keyword. -3706

Basically I’m trying to create a volatile table and then use it in a 2nd select statement in the free-hand SQL of Reporter against a Teradata database. Something like below:


create volatile table vmr, no log
as
(select * from bov.v_market_ref vmr
where vmr.region_id not in (‘0’,‘5’)) with data
primary index(market_code) on commit preserve rows;

select
vmr.Region_desc “Region”,
vmr.District_desc “District”,
upper(vdcr.dist_Channel_Name) “Distribution Channel”,
from bov.v_dlr_combo_ref vdcr . . . . .


IS THIS POSSIBLE WITHIN FREE-HAND SQL WINDOW?


jstevens163 (BOB member since 2004-05-07)

How do you remove the SQL Select only restriction?


Nick Daniels :uk: (BOB member since 2002-08-15)

Go into SUPERVISOR and select my ID. Then on the “Configuration” tab I select “Reporter” and then right click for properties. I’m then presented with a “Command restriciton - BusinessObjects - jstevens” panel. Under the “Query Technique” folder there is a “Restrict SQL to SELECT only” option.


jstevens163 (BOB member since 2004-05-07)

I can’t help with the create volatile table issue, but I can tell you that the Supervisor setting you are referencing has no impact on freehand SQL at all. You said it was under the “Query Technique” folder. The terminology is poor, but thats mean it governs universe-based data providers. The Freehand SQL settings are in a separate folder.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

That’s interesting that you say, “the Supervisor setting you are referencing has no impact on freehand SQL at all” because when I enable the “Restrict SQL to SELECT only” I go into the Free-hand SQL panel execute my query and get an immediate message “The only authorized SQL command is SELECT” So obviously it does have an affect on the query panel.


jstevens163 (BOB member since 2004-05-07)

Just when you think you “sorta” understand things smile. I haven’t tested what you stated, but it does sound like you are saying is true … I stand corrected.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Where do I set this “Restrict SQL to SELECT only” for a FreeHand Desktop Intelligence XI Report?


prinzartus :austria: (BOB member since 2006-09-07)

Is it something that can be doable from CMC for BOXI R2?

Thanks.


bobuser9 (BOB member since 2007-04-20)