HI,

I had issues when i convert SQL logic to Webfocus.
I was able to see the SQL code generated for reports using the trace.But when i have some complex report requirement,i am confused how to convert the logic to focus.

I have used SQL pass through when i had difficulty to implement some logic in focus code.
So i have a SQL query(duplicated) here,can some one please tell me ,how it gets converted into focus code.Or Focus equivalent of the SQL query
There is no way for me to see the focus code when i use sql pass through.i wanna see the focus interpretation of my sql query.
I am requesting this,coz i wanna avoid doing SQL pass through.

  

SELECT 
g1.PRODUCT_CODE
,COUNT(g1.PRODUCT_CODE)
,SUM(SALECODE)
FROM
(SELECT 
g0.Product_code as PRODUCT_CODE
,g0.Product_descr AS PRODUCT_CLASS
,COUNT(fr.SALE_CODE) AS SALECODE
,MIN(g0.clearance) AS CLEA
from 
(SELECT fa.PROD_TYPE_ID as Product_code 
,do.Prod_TYPE_DESCR as Product_descr
,fa.ACT_CODE as act_id
,fa.CLE_FLAG as clearance
,fa.OR_ID as or_id
FROM 
FACT_ACTIVITY fa inner join DIM_PROD_TYPE do ON fa.PROD_TYPE_ID = do.PROD_TYPE_ID
group by fa.PROD_TYPE_ID,do.PROD_TYPE_DESCR,fa.ACT_CODE,fa.CLE_FLAG,fa.OR_ID) g0
inner join FACT_SALES fr on fr.ACT_CODE = g0.act_id
group by 
g0.Product_code
,g0.Product_descr
)g1
GROUP BY 
g1.PRODUCT_CODE

This message has been edited. Last edited by: FP Mod Chuck,

I would look at the following syntax overview and get to know WebFOCUS a bit more. This should just be a simple conversion if you know the language well enough.

Reporting Syntax Summary

Also this page takes you to the other bits you need to learn.

Good luck!

srajeevan

The beauty of WebFOCUS is being able to use SQL passthru to get a result set without trying to recreate complex SQL in WebFOCUS. You have what you need just create a SQL report via that interface.

I would agree with Chuck, but it may be that srajeevan may be restricted to InfoAssist+ development only. That's why I shared the suggestions I did with him here and in the PM he sent me.

If you have code access, I would do what Chuck suggests, but if you are restricted to IA+, then look at what I shared with you via PM.

Good luck!

You can also create a synonym for this SQL and expose it to IA users. That way, they only see the outcome of the Prepare for the SQL and don't have to use PASSTHRU.

I have had real good luck with creating synonyms for SQL Server stored procedures. There are few tricks if the stored procedure is expecting parameters. I gave a presentation to the Northwest users group about this last July. I have a PowerPoint of it if anyone wants to PM me.

quote:

I have a PowerPoint of it if anyone wants to PM me.

I'd like that. Contact me via PM for more information.