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>,

Sati,
What are you trying to do?

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.

Shru1,

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

Thanks,
prodrigu

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.

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

Hi All,

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

Thanks
Sati

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

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).