Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Converting SQL to FOCUS code

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Converting SQL to FOCUS code
 Login/Join
 
Platinum Member
posted
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, 2017Report This Post
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, 2015Report This Post
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, 2005Report This Post
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, 2015Report This Post
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, 2015Report This Post
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, 2015Report This Post
Expert
posted Hide Post
quote:
I have a PowerPoint of it if anyone wants to PM me.

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




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Converting SQL to FOCUS code

Copyright © 1996-2020 Information Builders