I was given the code captioned below from a user. Regretably, my SQL experience is
practically zero. These tables are not defined in WebFOCUS.

I wanted to ask for any advice on whether this code code could be translated into a
WebFOCUS report request.

Here is the code:

quote:

SELECT
INST_ASET.ASET_ACTV_IND,
INST_ASET.ASET_ID,
sum(INST_ASET.ASET_SMRY_CNT),
sum(INST_ASET.ASET_PRIN_NET_BAL_AMT),
INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL,
INST_ASET_TYP.INST_ASET_TYP_GRP_ID
FROM
INST_ASET,
INST_ASET_DESC,
INST_ASET_TYP
WHERE
( INST_ASET_DESC.INST_ASET_UNIQ_ID(+)=INST_ASET.INST_ASET_UNIQ_ID )
AND ( INST_ASET.INST_ASET_TYP_UNIQ_ID=INST_ASET_TYP.INST_ASET_TYP_UNIQ_ID(+) )
AND
(
( (INST_ASET.SYS_ACTV_DTTM <= @prompt['Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)
and INST_ASET.SYS_REPL_DTTM > @prompt('Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)) )
AND
INST_ASET_TYP.INST_ASET_TYP_GRP_ID = '096'
AND
INST_ASET.ASET_ACTV_IND = 'Y'
)
GROUP BY
INST_ASET.ASET_ACTV_IND,
INST_ASET.ASET_ID,
INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL,
INST_ASET_TYP.INST_ASET_TYP_GRP_ID

Any insights or direction is appreciated!

Thanks,

This message has been edited. Last edited by: Kerry,

Just pass the SQL as it is to WebFOCUS. Just need to make sure the database connection (data adapter) you will be using is set up in the proper environment.

ENGINE SQLORA SET VARCHAR OFF
ENGINE SQLORA SET DEFAULT_CONNECTION database connection (example for where I work its dev07 or db07)
ENGINE SQLORA

SELECT
INST_ASET.ASET_ACTV_IND,
INST_ASET.ASET_ID,
sum(INST_ASET.ASET_SMRY_CNT),
sum(INST_ASET.ASET_PRIN_NET_BAL_AMT),
INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL,
INST_ASET_TYP.INST_ASET_TYP_GRP_ID
FROM
INST_ASET,
INST_ASET_DESC,
INST_ASET_TYP
WHERE
( INST_ASET_DESC.INST_ASET_UNIQ_ID(+)=INST_ASET.INST_ASET_UNIQ_ID )
AND ( INST_ASET.INST_ASET_TYP_UNIQ_ID=INST_ASET_TYP.INST_ASET_TYP_UNIQ_ID(+) )
AND
(
( (INST_ASET.SYS_ACTV_DTTM <= @prompt['Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)
and INST_ASET.SYS_REPL_DTTM > @prompt('Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)) )
AND
INST_ASET_TYP.INST_ASET_TYP_GRP_ID = '096'
AND
INST_ASET.ASET_ACTV_IND = 'Y'
)
GROUP BY
INST_ASET.ASET_ACTV_IND,
INST_ASET.ASET_ID,
INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL,
INST_ASET_TYP.INST_ASET_TYP_GRP_ID;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS INST_ASET
END
-RUN

What format is the date field (SYS_ACTV_DTTM)?
You might need to change the logic there on how the date is being passed.

Hello PRodrigu

here is a very short fex on how you can use you sql pass thru. You will have to change your sql for what you have and modified the date display to howerer you want to see it.

ENGINE SQLMSS SET DEFAULT_CONNECTION WFREPORTDATASRV
SQL SQLMSS PREPARE SQLOUT FOR
select *
from p_Phys
where cc_fac_no = 3322
END
TABLE FILE SQLOUT
PRINT
cc_phys_no
created_user_id
created_date_time
lastname
firstname
mi
HEADING
"HEADING"
FOOTING
"FOOTING"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

Thanks prodrigu and MLM for your help.
The date code I've been given is:

quote:

where SYS_ACTV_DTTM=to_date('19-APR-11','DD-MON-YY)

and it displays in a report as 22-JAN-11.

Tomsweb,

Your select columns don't seem to contain a date column, unless you have added one?

The WHERE date column doesn't print on the report. If you include it in the SELECT columns it should print correctly.

You can change the date output format easily.

--wg

First, you can define these tables in WebFOCUS by creating master and acx files for these tables. Then, you can either query the WebFOCUS master files directly and try to achieve what this SQL query is doing or you can use this SQL code in WebFOCUS SQL passthru such as:

ENGINE SQLORA SET DEFAULT_CONNECTION (connection string goes here)
SQL SQLORA PREPARE SQLOUT FOR
SELECT
INST_ASET.ASET_ACTV_IND,
INST_ASET.ASET_ID,
sum(INST_ASET.ASET_SMRY_CNT),
sum(INST_ASET.ASET_PRIN_NET_BAL_AMT),
INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL,
INST_ASET_TYP.INST_ASET_TYP_GRP_ID
FROM
INST_ASET,
INST_ASET_DESC,
INST_ASET_TYP
WHERE
( INST_ASET_DESC.INST_ASET_UNIQ_ID(+)=INST_ASET.INST_ASET_UNIQ_ID )
AND ( INST_ASET.INST_ASET_TYP_UNIQ_ID=INST_ASET_TYP.INST_ASET_TYP_UNIQ_ID(+) )
AND
(
( (INST_ASET.SYS_ACTV_DTTM <= @prompt['Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)
and INST_ASET.SYS_REPL_DTTM > @prompt('Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)) )
AND
INST_ASET_TYP.INST_ASET_TYP_GRP_ID = '096'
AND
INST_ASET.ASET_ACTV_IND = 'Y'
)
GROUP BY
INST_ASET.ASET_ACTV_IND,
INST_ASET.ASET_ID,
INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL,
INST_ASET_TYP.INST_ASET_TYP_GRP_ID
;
END
-RUN

Next, you would do:

TABLE FILE SQLOUT and print your fields for your report with whatever styling and format you wanted if this is all you needed to do.