[CLOSED] Converting SQL to FOCUS code

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,

WF8206,Windows 7,8,10
HTM,PDF,EXCEL

Posts: 229 | Location: MI | Registered: September 13, 2017

CoolGuy

Virtuoso

posted

Hide Post

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!

8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.

Posts: 1113 | Location: USA | Registered: January 27, 2015

Ignored post by CoolGuy posted

Show Post

FP Mod Chuck

Virtuoso

posted

Hide Post

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.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

Posts: 2127 | Location: Customer Support | Registered: April 12, 2005

Ignored post by FP Mod Chuck posted

Show Post

CoolGuy

Virtuoso

posted

Hide Post

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!

8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.

Posts: 1113 | Location: USA | Registered: January 27, 2015

Ignored post by CoolGuy posted

Show Post

BabakNYC

Virtuoso

posted

Hide Post

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.

WebFOCUS 8206, Unix, Windows

Posts: 1853 | Location: New York City | Registered: December 30, 2015

Ignored post by BabakNYC posted

Show Post

Michael L Meagher

Platinum Member

posted

Hide Post

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.

WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980

Posts: 115 | Location: Seattle, WA | Registered: April 07, 2015

Ignored post by Michael L Meagher posted

Show Post

Doug

Expert

posted

Hide Post