Hi

I am new to webfocus tool. can you pls tell me how can i use sql queries in webfocus.

i need to get the result of this db2 query in webfocus report.

select max(e.actl_d), e.dbas_n, sum(e.AVERAGE)
from
(select d.actl_d, a.dbas_n, b.tbs_n, AVG(c.spce_use_q) as AVERAGE from
hcddm.dbas_dim a,
hcddm.tbs_dim b,
hcddm.tbs_fct c,
hcddm.fscl_date_dim d
where
a.dbas_dim_i = b.dbas_dim_i and
b.tbs_dim_i = c.tbs_dim_i and
c.date_dim_i = d.date_dim_i
Group by d.actl_d, a.dbas_n, b.tbs_n) e
group by e.dbas_n

To use SQL passthru using a DB2 query then use code like this -

ENGINE SQLDB2 SET SERVER [type your data adapter name here without square brackets]
-* Note: you must set your data adapter up using the WF tools first.
SET SQLENGINE = SQLDB2
-* You might not require this next line
SQL SQLDB2 SET VARCHAR OFF
SQL
select max(e.actl_d), e.dbas_n, sum(e.AVERAGE)
from
(select d.actl_d, a.dbas_n, b.tbs_n, AVG(c.spce_use_q) as AVERAGE from
hcddm.dbas_dim a, 
hcddm.tbs_dim b,
hcddm.tbs_fct c,
hcddm.fscl_date_dim d
where
a.dbas_dim_i = b.dbas_dim_i and
b.tbs_dim_i = c.tbs_dim_i and
c.date_dim_i = d.date_dim_i
Group by d.actl_d, a.dbas_n, b.tbs_n) e
group by e.dbas_n
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DB2OUT
END
-RUN
TABLE FILE DB2OUT
rest of your report code
ON TABLE SET STYLE *
put your styling here
ENDSTYLE
END

Hi,

You can do it using sql passthru.Just create a .fex file in webfocas and paste the following -

ENGINE DB2 SET DEFAULT_CONNECTION database_name
SQL DB2

select max(e.actl_d), e.dbas_n, sum(e.AVERAGE)
from
(select d.actl_d, a.dbas_n, b.tbs_n, AVG(c.spce_use_q) as AVERAGE from
hcddm.dbas_dim a,
hcddm.tbs_dim b,
hcddm.tbs_fct c,
hcddm.fscl_date_dim d
where
a.dbas_dim_i = b.dbas_dim_i and
b.tbs_dim_i = c.tbs_dim_i and
c.date_dim_i = d.date_dim_i
Group by d.actl_d, a.dbas_n, b.tbs_n) e
group by e.dbas_n

TABLE FILE SQLOUT
PRINT *
END

When executing the query it pops a message

>ENGINE<
ENGINE SQLDB2 SET SERVER xxxxx

xxxxx is my db2 server name.

i executed the following..

ENGINE SQLDB2 SET SERVER ETLUX001
SET SQLENGINE = SQLDB2
SQL SQLDB2 SET VARCHAR OFF
SQL


select max(e.actl_d), e.dbas_n, sum(e.AVERAGE)
from
(select d.actl_d, a.dbas_n, b.tbs_n, AVG(c.spce_use_q) as AVERAGE from
hcddm.dbas_dim a,
hcddm.tbs_dim b,
hcddm.tbs_fct c,
hcddm.fscl_date_dim d
where
a.dbas_dim_i = b.dbas_dim_i and
b.tbs_dim_i = c.tbs_dim_i and
c.date_dim_i = d.date_dim_i
Group by d.actl_d, a.dbas_n, b.tbs_n) e
group by e.dbas_n
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DB2OUT
END
-RUN
TABLE FILE DB2OUT
rest of your report code
ON TABLE SET STYLE *
put your styling here
ENDSTYLE
END

Another way to use SQL with DB2 is to PREPARE first, then EXECUTE. We use &ER variables for our column names (for various reasons that can't be explained here). This works nice and you can pass variable values to the query:

-************************************************************
-* SQL PREPARE WACP01 *
-************************************************************
SQL DB2
PREPARE &PREPARE FOR
SELECT
PRD_ENDT
, PRD_TYP
, &RPT1_FLD01
, &RPT1_FLD02
, &RPT1_FLD03
:
FROM &TABLE1
WHERE PRD_ENDT = ?
AND PRD_TYP = ?
AND . . .
FOR FETCH ONLY WITH UR;
END
-RUN
SQL DB2 EXECUTE &PREPARE USING '&RPTG_DT' , &RPTG_PRD_TYP ;
END
-RUN
TABLE FILE &PREPARE
PRINT *
ON TABLE HOLD AS WACP01H1
END
-RUN
:
JOINS TO FOCUS OR DB2 REFERENCE FILES
:
DEFINE …
:
TABLE ..
PRINT . .
- style sheet etc.
END

quote:

>ENGINE<

Moju,

What is the complete error message that you are receiving?

Tony,

I am receiving a message window like

--------------------------------
Error parsing report request
>ENGINE<
ENGINE SQLDB2 SET SERVER xxxx
--------------------------------

I am using Webfocus 5.3 version.

Moju,

I take it that your data adapter has been setup and tested using the WF tool set in the console, and that you are using the data adapter name?

Moju,

We use DB2 as well. Here is what we place in the fex. Verion 5.3 and 7.1.3;\

ENGINE DB2 SET DEFAULT_CONNECTION &DBNAME
ENGINE DB2

SELECT
...
...
...
TABLE ON TABLE HOLD AS &VIEWNAME FORMAT ALPHA
END

The &DBNAME would be replaced by your DB2 connection name set up on the WF Server. &VIEWNAME is what you want the result set to be named so you can TABLE FILE &VIEWNAME.

Hope this helps.

Kevin

Very sorry for delayed reply.

As i was very busy with my project i could not able to update this result.

I am not able to use sql queries even after using this queries. The First line itself showing error.
ENGINE DB2 SET DEFAULT_CONNECTION &DBNAME
ENGINE DB2

I think it is possible only in server version. i am using the client version. so i could not able to connect this db2.

But, i solved that problem by having two reports. i saved the output of first report using hold then i prepared the final report.

Thank you so much for your replies.. Again sorry for the delay.