I include an SQL file but it won't execute.
General information on the SQL file
With commandes using # tables (temp tables)
declared variable I feel webfocus won't process.
if I create the same script as a Stored procedure and if i execute that procedure it will work.
any help if welcome.
Rao

Rao,

Can you post your code between the code tags (last icon in the list).

Also please update your profile signature with your product suite, release, and platform so that we may better help you.

I don't know but will this work?
=========
declare @tmptablename as varchar(100)
declare @tmpasofdate as datetime
set @tmpasofdate = ''
if @tmpasofdate = ''
begin
set @tmpasofdate =
(
select last_business_date from vortex.dbo.paf_iimgenriskcalendar
where
convert(varchar(12),current_calendar_date,101) = convert(varchar(12),getdate(),101)
)

end

set @tmptablename = '#tmpmaindashboard'
if (select count(*) from tempdb..sysobjects where id = object_id('tempdb..'+ @tmptablename) ) =1
exec ('drop table ' + @tmptablename )

set @tmptablename = '#tmpmaindashboardfinal'
if (select count(*) from tempdb..sysobjects where id = object_id('tempdb..'+ @tmptablename) ) =1
exec ('drop table ' + @tmptablename )


create table #tmpmaindashboard
(
asofdate datetime ,
strategy varchar(50),
capital int,
grossnotional float,
mtm float,
var float,
var_percent float,
order_by int
)

create table #tmpmaindashboardfinal
(
id_order int identity(1,1),
asofdate datetime ,
strategy varchar(50),
capital int,
grossnotional float,
mtm float,
var float,
var_percent float,
order_by int
)


insert into #tmpmaindashboard (strategy,capital)
select b.strategy,a.strategy_capital /1000000 capital
from
vortex.dbo.paf_strategy_master a, (select distinct strategy strategy from vortex.dbo.paf_daily_position_data) b
where
a.strategy_gorisk_name = b.strategy + ' total' and
a.strategy_capital is not null and a.strategy_active_flag = 'y'

update #tmpmaindashboard set asofdate = cast(p.asofdate as datetime), grossnotional = p.notional,
mtm = p.mtm
from
#tmpmaindashboard a,(
select convert(varchar(12),asofdate,101) asofdate ,strategy,
round(sum(abs(position))/1000,0) notional,
round(sum(mtm)/1000,0) mtm
from
vortex.dbo.paf_daily_position_data_historical
where
strategy <> 'test' and
asofdate = @tmpasofdate
group by asofdate,strategy ) p
where
p.strategy = a.strategy


insert into #tmpmaindashboardfinal (asofdate,strategy,capital,grossnotional,mtm)
select asofdate, strategy,capital, grossnotional , mtm from #tmpmaindashboard order by mtm desc

update #tmpmaindashboardfinal
set var = v.grpvar
from
#tmpmaindashboardfinal a,
(select strategy,(var) grpvar
from vortex.dbo.paf_daily_simulation_data_historical
where strategy <> '' and folder = '' and vartype = 'sleeve level' and
strategy <> 'test' and
convert(varchar(12),asofdate,101) = convert(varchar(12),@tmpasofdate,101)
) v
where a.strategy = v.strategy


insert into #tmpmaindashboardfinal (asofdate,strategy,capital,grossnotional,mtm)
select @tmpasofdate,'total',sum(capital),sum(grossnotional),sum(mtm) from #tmpmaindashboard


update #tmpmaindashboardfinal set var = f.var
from
#tmpmaindashboardfinal a,(
select avg(groupvar) var
from vortex.dbo.paf_daily_simulation_data_historical
where strategy <> '' and folder = '' and vartype = 'sleeve level'
and strategy <> 'test' and
convert(varchar(12),asofdate,101) = convert(varchar(12),@tmpasofdate,101)
) f
where
a.strategy = 'total'


update #tmpmaindashboardfinal set var_percent = round(var/(capital*1000000)*100,2)
update #tmpmaindashboardfinal set order_by = 0 where strategy = 'total'
update #tmpmaindashboardfinal set order_by = 1 where strategy <> 'total'



select * from #tmpmaindashboardfinal order by id_order
============

quote:

I don't know but will this work?

You don't know what and will this work how? What is it exactly you are trying to accomplish? Please elaborate.

If this is a stored procedure, WebFOCUS can handle that, but you have to specify what parameters are being used. Executing stored procedures is documented in the manuals.

If you want to use SQL passthru, WF can probably handle that, but again, WF uses &variables, not the @parms used by SQL. How to use SQL passthru is also documented.

It probably will not work without some modification if run thru standard WF. If you're wanting someone else to figure out your SQL and translate it to WF, there are paid consultants that do that kind of work.

Please include in your profile the platform(s) and databases being used.

hi Darin,
Thanks for writing the message.
Simple question to gurus...
If I have my SQL file why can't it work when Stored procedure with the same script works?

simple example
If I create a sp with the following code

declare @tmp int
set @tmp = 10

select * into #tmpatable from table1

update #tmptable set rowint = rowint*@tmp

select * from #tmptable

/***/

The above code will work as SP. try as a sql file and get the same result as SP that is the question.
I am not able to do with the sql file so thought any one can help with this simple concept.

I don't need help on SQL code to modify to WEB focus code.
Thanks,
Rao

Again, we ask you to please put your platform in your signature.

I'm still not sure what you want to do but there is special syntax in WebFOCUS to pass SQL directly to the RDBMS. And that is:

SQL SQLORA (for example if your database is Oracle)
SELECT ...
;
END

It is also important to remember that WebFOCUS is stateless so that if you have multiple commands to send to the RDBMS and they depend on each other, you have to group the commands inside the syntax above.

The other obvious question is that if you already have a functioning stored procedure, why don't you run it from WebFOCUS? There is setup involved of course but I'm sure you can find some doc on that on the IBI site.

Thanks Ginny.
I currently run via Stored proc.
The more flexibility I can get not going through the complexe DBA-Group-4 levels of change controls for SP modification if I need a simple report, I use the SQL Passthrough.
Unfortunately if it is single SQL Webfocus has no problem but the minute i introduce the MS SQL comments like declare, set etc, it won't take it.
How do I tell webfocus that all the following commands are grouped?
May be I am missing that particular syntax.
The first 2 lines in the fex are
ENGINE SQLMSS SET DEFAULT_CONNECTION CONNSQL1
SQL SQLMSS PREPARE SQLOUT FOR
declare @tmpvar as varchar(20)
...
.... my sql statements
......
;
END

the key here is how can i group the various SQL statements which might contain selects in middle, insert into # tables and updates etc and a final Select for the report.....
Thanks once again for your help.
Rao

I don't know what your hardware and OS platform are, but in our environment, I would do the following.

First, I would shell to Linux and have SQLPlus run the procedure. I would then copy/ftp the file to somewhere WebFOCUS can find it. Create a master for the file and then run my report. For example:

-UNIX sqlplus USERID@kids/PASSWORD @/opt/ibi/apps/misc_production/iveassgn.sql
-UNIX mv output_file.txt /opt/ibi/apps/baseapp
-*
 FILEDEF IVEOUTPUT DISK /opt/ibi/apps/baseapp ( LRECL XXX RECFM V
 TABLE FILE IVEOUTPUT
.
.
.
 END

The reason I would do it like this is because I have not had much luck running a procedure like yours using SQL Passthru.

I may be mistaken, but I have always understood SQL Passthru as being a way to run ANSI SQL via a Focexec. Statements that declare variables are not considered ANSI SQL. They are the progarmming languages used to create Stored Procedures and as such are RDBMS specific. What you would need to do is to convert all the 'declarative' statements into FOCUS syntax (creating & variables) and use SQL passthru for the pure SQL parts.
Hope that helps.

That's not entirely true, Diptesh. If you say SQL enginename, you can pass customized SQL for that engine. If you do a TABLE request and view the SQL, that is ANSI SQL.

There is some SQL that cannot be done via SQL pass-thru. View case #23032569 - although it's not about WebFocus and it's a different version than you have, but I believe the answer might be the same. To be sure, I think you'll need to open a case with IBI.

Good luck.

Jessica Bottone

I have run across a single instance of code that would not work via passthru, so yes, that is possible. The answer is to run via stored procedure. You send parameters and are returned an answer set. No code passed, etc., etc.