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] Webfocus equivalent for SQL sample

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Webfocus equivalent for SQL sample
 Login/Join
 
Shru1
Member
posted
Hi All,

I am looking for WEbfocus Equivalent for sql sample.
Any help is appriciated

Regards
Sati

This message has been edited. Last edited by: <Kathryn Henning>,


WEbfocus 7.7.02,HTML,PDF & Excel
 
Posts: 4 | Registered: April 16, 2015Report This Post
RSquared
Guru
posted Hide Post
Sati,
What are you trying to do?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Shru1
Member
posted Hide Post
Hi,

I have to convert an existing report into Webfocus. The report criteria is a sample mix of random cities. I need to pull atleast one record for each cities but this should change everytime they run the report. In SQl there is a function called 'SAMPLE' which does this. I am looking for webfocus equivalent.


WEbfocus 7.7.02,HTML,PDF & Excel
 
Posts: 4 | Registered: April 16, 2015Report This Post
prodrigu
Platinum Member
posted Hide Post
Shru1,

Why dont you just use SQL Passthrough? That way you can keep your SQL Query.

Thanks,
prodrigu


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
RSquared
Guru
posted Hide Post
While I haven't used it in a while there are several functions in WEbFOCUS to select Random values. Search the Focal Point using the word Random.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Shahram
Member
posted Hide Post
Unfortunately there is no function similar to SAMPLE in WebFOCUS that I know, so we need to work around it.

This is the sample of SQL "SAMPLE" and it's output:

SELECT TOP 10 LastName FROM DimCustomer TABLESAMPLE(1 PERCENT)

First run Second Run Third Run

LastName LastName LastName
Ortega Yang Romero
Campbell Huang Bryant
Hughes Torres Mehta
Alvarez Zhu Malhotra
Vazquez Johnson Diaz
Chapman Ruiz Lu
Anand Alvarez Raji
Vazquez Mehta Moreno
Martinez Verhoff Goel
Chande Carlson Howard

------------------------------------------------------------------
This is one way to do in WebFOCUS:

TABLE FILE DIMCUSTOMER
SUM COMPUTE random/D20.6 = RDNORM('D20.6'); NOPRINT
BY TOTAL HIGHEST 10 random NOPRINT
BY LASTNAME
END

First run Second Run Third Run

LASTNAME LASTNAME LASTNAME
Gonzalez Rizaldy Ramirez
Ptaszynski Weadock Roessler
Hee Anderson Jones
Saraiva Weber Raman
He Chander Kelly
Gray Sanders Narayanan
Palit Rodriguez Skjønaa
Zheng Hill Thomas
Ruggiero Madan Song
Diaz Ferrier Jordan


All Releases
All OS, All Outputs
 
Posts: 15 | Registered: October 24, 2014Report This Post
Shru1
Member
posted Hide Post
Hi All,

Thank you for all your suggestions!!! I really appreciate it. I will try them and post back

Thanks
Sati


WEbfocus 7.7.02,HTML,PDF & Excel
 
Posts: 4 | Registered: April 16, 2015Report This Post
StuBouyer
Guru
posted Hide Post
If Sharam's solution isn't giving you what you want then you can simply do a SQL pass thru to get your sample and then use WebFOCUS to format it

SQL SQLMSS PREPARE SQLOUT FOR
SELECT TOP 10 LastName FROM DimCustomer TABLESAMPLE(1 PERCENT)
END

TABLE FILE SQLOUT
PRINT LastName AS 'Last Name'
ON TABLE PCHOLD FORMAT HTML
END



You will need to change SQLMSS in the first line to match the data adapter you are using if it isn't SQL Server

Hope this helps

Stu


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Shru,
If you do not use SQL passthru, I suggest you use the RDUNIF function (which gives a uniform distribution) and not the RDNORM function (which gives a normal distribution meaning more numbers around the average).


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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] Webfocus equivalent for SQL sample

Copyright © 1996-2020 Information Builders