sqrfocus

7/24/2019 sqrfocus

1/40

Bob Hughes, CSUEB, SQR vs. Focus, 5/2006NCAL IBUG/FUSE

Hyperion SQR vs.

Focus and WebFocusBob Hughes

Information and Computing Services

California State University, East Bay

Hayward, California [email protected]

NORCAL IBUG/FUSENorthern California IBI/Focus

Technology Forum and Users Group

May 2006

7/24/2019 sqrfocus

2/40


SQR vs.Focus

Oracle/PeopleSoft has delivered, sinceinception large numbers of reports andprocesses in an SQR library in additionto COBOL, PeopleCode, etc..

SQR stands for Structured QueryReport Writer currently owned byHyperion Group.

7/24/2019 sqrfocus

3/40


SQR vs.Focus

SQR takes standard SQL and adds

procedural programming logic.

In general, SQR is not used for ad-hocreporting. It provides a 3GL level

formatting capabilities combined using

SQL as a base.

7/24/2019 sqrfocus

4/40


SQR History

SQ Software created SQR in the mid 1980s.

They had a marketing agreement with D & Nsystems (which changed its name to SQLSolutions, and was later acquired by Sybase).

Sybase purchased SQ Software in the early1990s. To avoid competing directly withOracle, Sybase had a marketing and

development agreement with MITI for theOracle versions of the product.

MITI acquired the full rights to SQR in the mid1990s. MITI later changed its name toSQRiBE Technologies.

7/24/2019 sqrfocus

5/40


SQR History

Brio Technologies acquired SQRiBE inAugust, 1999. Brio Technologies later

changed its name to Brio Software.

Brio licensed the source code to Peoplesoft

sometime around 2000.

Hyperion Software acquired Brio Software inOctober, 2003.

SQR is mainly found in use in

Oracle/PeopleSoft Applications.

l History Source Ray Ontko

7/24/2019 sqrfocus

6/40


SQR History

Hyperion is reselling Brios products on anOEM basis until the deal closes on October

16, 2003. The Brio brand is expected to be

dropped immediately after the deal closes,

though the Intelligence product name is likely

to continue. It is not yet clear what the future

of SQR will be OLAP Report March 3,

2004

SQR continues to be used in

Oracle/PeopleSoft Environments

7/24/2019 sqrfocus

7/40


SQR To Be Around Longer,,,

Oracle Announcement 4/25/06

Charles Phillips, Oracle President and member of theOracle Board of Directors, announced a new programcalled Applications Unlimited. ApplicationsUnlimited now gives Oracle customers using Oracle,PeopleSoft, Siebel and JD Edwards applications theoption of remaining on these products, with continuedsupport, development and new releases in perpetuity.

Specifically Phillips focused on 4 major points forcustomers:

Releases on current products will continue, evenafter Fusion

7/24/2019 sqrfocus

8/40


SQR To Be Around Longer

Oracle Announcement 4/25/06

Customer driven Product Roadmaps will bedelivered

Development Teams will be dedicated tospecific product lines

There will be no forced upgradesl It had been previously announced that Oracle

PS NVison and use of SQR)now owned byHyperion, would fade with move to OracleFusion suite(replacement for PS, etc..

7/24/2019 sqrfocus

9/40

Sample Code

7/24/2019 sqrfocus

10/40


SQR Sample Code

7/24/2019 sqrfocus

11/40


SQR Sample Code

The PROGRAM section consists of a single

DO command, which invokes the procedurelist_customers.

In SQR, a procedure is a group of commands

that are performed one after the other, like a

procedure (or subroutine) in other

programming languages. A DO commandinvokes a procedure.

More lines of Code are required in SQR than

Focus, even in simple procedures

7/24/2019 sqrfocus

12/40


SQR Sample Code

SQR distinguishes column names from SQR

commands in a SELECT paragraph by theirindentation. Column names must be placed at thebeginning of a line.

SQR commands must be indented at least onespacein the following example, the POSITIONcommand is indented to prevent it from being takenas a column name. The word FROM must be the firstword in a line. The rest of the SQR SELECT

statement is then written freely, after SQL syntax.The SELECT paragraph is a loop. The SQRcommands, including printing of columns, areexecuted in a loop, once for each record thatSELECT returns. The loop ends after the last recordis returned.

7/24/2019 sqrfocus

13/40


SQR Sample Code

In Focus positional coding(column

specific placement of commands is notrequired.

Focus performs formatting after a

complete pass of the database to collect

data which is more efficient the SQR. InSQR each select statement is

processed as a loop for each record.

7/24/2019 sqrfocus

14/40


SQR-Sample Code-Results

7/24/2019 sqrfocus

15/40


Focus Sample Code

TABLE FILE CUSTOMER

PRINT NAME AS Name

CITY AS City

STATE AS State

PHONE AS Phone

END

SQR Program had 25 lines compared to

Only 6 lines using Focus or WebFocus

7/24/2019 sqrfocus

16/40


SQR Code - Variables

But what if you want to use the value ofphone for another purpose, for example, in acondition? The following example shows you how to do this.

Thephone column is an SQR column variable. Column variables are preceded with anampersand (&). Unlike other program variables, column variables are read-only. You canuse their existing value, but you cannot assign a new value to a column variable.

7/24/2019 sqrfocus

17/40


SQR Code - Variables

In the sample program, &phone is a column variable that you can use in

SQR commands as if it were a string, date, or numeric variable,depending on its contents. In the condition, &phone is compared to ,an empty string. If &phone is an empty string, the program prints Nophone instead. Note that the column variable &phone inherited its

name from thephone column. This is the default, but you can changeit

7/24/2019 sqrfocus

18/40


Focus Sample Code

DEFINE FILE CUSTOMER

PHONETXT/A21=IF PHONE EQ THEN No Phone ELSE PHONE ;

END

TABLE FILE CUSTOMER

PRINT NAME AS Name

CITY AS City

STATE AS StatePHONETXT AS Phone

END

With Focus or Web Focus

Handling of Variables Is

more intuitive with lesscoding..

7/24/2019 sqrfocus

19/40


SQR Code Positioning Data

In a SELECT statement (repeated here), positioning occurs after each columnname. This positioning implies a PRINT command for that column. As before,omitting the line number in the position lets it default to the current line.

After the last column, there is a POSITION command: POSITION(+1).The plus sign (or minus sign) indicates relative positioning in SQR. Aplus sign moves the print position forward from the current position, anda minus sign moves it back. The +1 in our program means one linedown from the current line. This command advances the current printposition to the next line.

7/24/2019 sqrfocus

20/40


SQR Program Coding

In more complex programs, the SQR

coding effort can become much more

intensive than when using a 4GL such as

Focus or WebFocus dramatically

increasing Programming Costs.

The logarithmic increase in costs will be

shown next in the CrossTabs Report

example.

7/24/2019 sqrfocus

21/40


SQR Code CrossTabs / Array Processing

7/24/2019 sqrfocus

22/40


SQR Code CrossTabs / Array Processing(contd)

7/24/2019 sqrfocus

23/40



7/24/2019 sqrfocus

24/40



7/24/2019 sqrfocus

25/40

Bob Hughes, CSUEB, SQR vs. Focus, 5/2006

NCAL IBUG/FUSE

Focus Sample Code

JOIN ORDER_NUM IN ORDERS TO ORDER_NUM INORDLINES AS J1

JOIN PRODUCT_CODE IN ORDERS TO PRODUCT_CODE IN PRODUCTS AS J2

DEFINE FILE ORDERS

ORDER_MONTH/M=ORDER_DATE;

END

TABLE FILE ORDERS

WRITE ORDER_QUANTITY

BY DESCRIPTION

ACROSS ORDER_MONTH

ON TABLE ROW-TOTAL COLUMN-TOTAL

END

SQR 80 Line Program

Is replaced by 11 Line

Program Using Focus or

WebFocus

7/24/2019 sqrfocus

26/40


NCAL IBUG/FUSE

SQR Code Basic Summary

The DO command invokes a procedure.

A procedure begins with BEGIN-PROCEDURE and endswith END-PROCEDURE.

A SELECT paragraph begins with BEGIN-SELECT andends with END-SELECT.

SQR commands in a SELECT paragraph must be indentedat least one space to prevent them from being taken for

column names.In a SELECT statement, you can print a column by entering

it at the beginning of a line with a position qualifier. This iscalled an implied PRINT command.

The POSITION command gives a position.

7/24/2019 sqrfocus

27/40

File Description Access

7/24/2019 sqrfocus

28/40


NCAL IBUG/FUSE

SQR vs. WebFocus File Description Access

7/24/2019 sqrfocus

29/40


NCAL IBUG/FUSE

SQR vs. MS Access File Description Access

7/24/2019 sqrfocus

30/40


NCAL IBUG/FUSE

SQR vs. Focus File Description Access

Oracle/PeopleSoft Tools Query Facility

7/24/2019 sqrfocus

31/40


NCAL IBUG/FUSE

SQR vs. Focus File Description Access

Oracle/PeopleSoft Tools Query Facility

7/24/2019 sqrfocus

32/40

SQR and Program Editors

7/24/2019 sqrfocus

33/40


NCAL IBUG/FUSE

SQR and Programming Editors

Everything used from NotePad to SQR

specific editors such as SQR Express

jEdit (jedit.sourceforge.net has an sqrparser

plugin see http://home.mchsi.com/~ssatguru/SQR/SqrParser/SqrParser.html

Gold, EditPlus2 and others have languagesupport files for SQR.

7/24/2019 sqrfocus

34/40

SQR 3rd Party Tools

7/24/2019 sqrfocus

35/40


NCAL IBUG/FUSE

SQR 3rd Party Tools/IDEs

SQR Runner 4 Pro

7/24/2019 sqrfocus

36/40


NCAL IBUG/FUSE

SQR 3rd Party Tools/IDEs

SQR Express

7/24/2019 sqrfocus

37/40

SQR Documentation

Resources

7/24/2019 sqrfocus

38/40


NCAL IBUG/FUSE

SQR Documentation

Official Doc is at Hyperion Developer Site

http://dev.hyperion.com/resource_library/technical_documentation/sqr/sqr_8.cfm

7/24/2019 sqrfocus

39/40


NCAL IBUG/FUSE

SQR Resources

http://www.sqr-info.com/

http://www.sqrug.org/sqr-users/

http://www.sqrug.org/ftp/

http://dev.hyperion.com/resource_library/techni

cal_documentation/

http://www.sqrevangelism.com/Book/index.htm

7/24/2019 sqrfocus

40/40

sqrfocus

Documents

Transcript of sqrfocus