[SOLVED] InfoAssist: How to get runtime row sequence number and filter it?
Hi,
We are using InfoAssist to create a report.
I want to get a runtime row sequence number based on a column (like "row_number() over (partition by column_name order by sort_column_name)" in Oracle database). I should then be able to filter the first sequence id.
Can someone please help me?
Thank you.
Kind regards,
Manoj.This message has been edited. Last edited by: Manoj,
Newbie. WebFOCUS 8.2.0.3. Client 8.2.0.4.
April 29, 2020, 11:47 AM
FP Mod Chuck
Manoj
Not sure if this is exactly what you are looking for because I didn't understand what you meant my "partition". DEFINE FILE CAR SEQ_ID/I11 WITH CAR=IF COUNTRY NE LAST COUNTRY THEN 1 ELSE SEQ_ID + 1; END TABLE FILE CAR PRINT CAR BY COUNTRY BY LOWEST SEQ_ID WHERE SEQ_ID EQ 1 ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty, $ ENDSTYLE END
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
April 29, 2020, 12:55 PM
Manoj
Many thanks Chuck.
As I am using InfoAssist, I am assuming that the SEQ_ID is a COMPUTE calculation. When used as a COMPUTE calculation I am getting a proper sequence ID, but if I create a calculation based on DEFINE it's not consistent.
Then I filtered the COMPUTE calculation by limiting it to 1 (right click > Sort > Limit > Custom .. 1), as I can not drag and drop a calculation based on COMPUTE to the Filter pane.
The report is returning expected rows.
Very helpful. Thank you very much.
Kind regards,
Manoj.This message has been edited. Last edited by: Manoj,