Sqr

What is SQR and what for SQR is used for?

It is structured query reporting language used for creating reports and batch processing

Can you pls give some aggregate functions in SQR?

MIN,MAX,AVG,SUM,COUNT

When you run a SQR through workbench by default  where the output  is created?

The out put is created in LIS file in the directory where the program is resides

SQR Columns?

&columns

SQR variables?

#Variables, date variable, list variables and $Variables

Can you name few predefined SQR variables?

#current-line

#sql-status

$sql-error

#end-file

#page-count

#status

#return-status

$current-date

#sqr-pid

$username

Can you name few SQR functions?

Datenow ()

Substr

Unstring, string, datetostr (), strtodate(),to_num,to_char,to_date,length

What is the use of list variables?

List variables hold the set of variables or multiple rows of information of similar structure, list variables are denoted with %

Let %Empinfo = list ($emplid, $name, $deptid, $jobcode, $grade)

What is the difference between Move and Let?

Move is data movement command used to move values from one variable to another variable

Let is also a data assignment command it can be used for complex arithmetic calculations and it replaces most of the arithmetic and string commands

Edit masks can be used with both Move and Let commands

Can we write Print, Dislay, Show in BEGIN-SETUP section?

We don’t use

Can we use BEGIN-SELECT in SETUP SECTION?

No we can’t

Can we write multiple BEGIN-SELECT, BEGIN-SQL statements in a procedure?

Yes we can

How do you create tables in SQR?

Through BEGIN-SETUP by using BEGIN-SQL

What is the difference between SQR Array and LOAD-LOOKUP?

How SQR processes the source program?

During first stage

All source files are inserted in to the program source file

All substitution variables are resolved

Program memory and buffers are allocated

Checks the syntax of the source program

Determines how to optimize the SQL data access

If no compilation errors found, during second stage

Start s processing at begin-program and stops and end-program

Calculates the size of the report

Processes the report body

Processes report heading

Processes report footing

Writes entire page to output file

What are the 5 sections in SQR?

Program section (Begin-program)

Setup section (Begin-Setup)

Heading section (Begin-Heading)

Footing section (Begin-footing)

Procedure section (Begin-procedure)

What is the mandatory section?

Begin-program and begin-procedure

What is use of Setup section?

Used for declaration part, if you are using it in the program it should be the first section

Can you give the syntax for Begin-heading?

 Begin-heading 4

 Move $current-date to $today

 Print 'UNION BANK OF INDIA' (1) center BOLD

 Print ‘ReportID: UBI_SEP1' (1,1)

 Print 'RunDate:' (1,108)

 Print $today (1,118) edit  'DD/MM/YYYY'

 Print 'RunTime:' (2,108)

 Print $today   (2,118) edit 'HH:MI:AM'

 Print ' EMPLOYEE FUTURE SEPARATION INFORMATION' (2)  center

 !print '*' (+1, 10, 55) fill

 PRINT 'LOCATION DESCRIPTION' (+2, 1, 20)

 PRINT 'DEPT DESCRIPTION' (,33,20)

 PRINT 'EXPECTED RET DT' (,62,15)

 PRINT 'EMPLID' (,82,12)

 PRINT 'NAME' (,94,4)

 End-heading

·         Syntax for footing?

Begin-footing 2

      Page-number (1,110) ‘Page’

      Last-page   () ' of’

End-footing

How the RUNCONTROL tables referred in SQR give syntax?

BEGIN-SELECT

RUN_CNTL.COURSE &COURSE

RUN_CNTL.COURSE_START_DT &START_dT

RUN_CNTL.FACILITY &FACILITY

RUN_CNTL.SESSION_NBR

FROM PS_UBI_TRN_RUNCTL1 RUN_CNTL

WHERE RUN_CNTL.OPRID = $prcs_oprid

AND RUN_CNTL.RUN_CNTL_ID = $prcs_run_cntl_id

END-SELECT

What is Load-lookup and Look-up?

Load-lookup is used fetch the data from multiple tables by simplifying the table joins and it also improves the performance of the program.

It can be used either in Setup section or any procedure

If we use it in Setup section it is executed only once

Load-Lookup retrieves two fields form the database key and return_value fields, return_value may refer to one or more columns of the table

Look-up command is used to search through internal memory arrays created and populated by Load-lookup; it returns the return_value for every key

By default 100 rows will be stored in array if we don’t specify the rows

Begin-setup

  Load-lookup  

     Name=prods

      Table=products

      key=product_code

      return_value=description

End-setup

...

Begin-select

order_num (+1,1)

product_code

  lookup prods &product_code $desc

  print $desc (,15)

from orderlines

End-select

What is while used for?

It performs the body of the loop while the specified condition is true

While $file_name

  Read file…..

End-while

Give few examples for edit masks?

Text edit, Numeric Edit, Date Edit

What is the Position command used for?

It is used to set the current position

Example: position (+1)

Position (+3, 2)

How will you control vertical spacing in your report?

 By using next-listing command

E.g.: next-listing skiplines=1 Need=3

What is Need command?

It controls the end of the page printing (It is used with vertical printing)

Begin-select

state       (,1) 

name        (,11)

addr1       (+1,11)

addr2       (+1,11)  

city        (+1,11)  

phone          (,+2) edit (xxx)bxxx-xxxx

  next-listing skiplines=1 need=4  

  let #state_total = #state_total + 1

From customers

Order by state, name

End-select

Name few print commands?

Bold

Underline

Center

Wrap

Match

Fill

How do you control horizontal spacing?

Next-column and Use-column

How do you disable heading?

By using NONE command

How do you alter report heading?

By using alter-report

Alter-report

Heading=’ABC corporation’

Heading-size=5

What is a bind variable in SQR?

Bind variables are runtime variables their values are assigned and changed during run time

How will the SQR program accepts input by users during run-time?

Through INPUT command

INPUT command is processed during run-time

Example: Input $state  maxlen=2  'Please enter state abbreviation'

What are substitution variables in SQR?

Substtution variables are compile-time variables those cannot be changed during run-time, eg..used to specify the print positions

Substitution variable names must be enclosed in braces “{}”

#define  col1  1

#define  col2  27

#define  col3  54

#define  order_by  state, county, city, co_name

.

.

.

.

begin-select

company      (1,{col1})

  print '{bold}'      (0,{col2})         ! Print contact in boldface.

contact         ()

  print '{light}'         ()             ! Back to lightface.

phone         (0,{col3})                 ! Note: There must be enough

  next-listing                           ! space between col2

from customers                           ! and col3 for both

order by  {order_by}                     ! font changes and the

end-select                               ! contact field.

What is ASK command?

ASK is a input command and is processed during compile-time

ASK command is only used in begin-setup section and must appear prior to any substitution variable

Begin-setup

ASK EmplID ‘Please enter the Employee ID’

End-setup

.

.

.

.

Where A.emplid =’{EmplID}’

What are the dynamic variables in SQR?

Dynamic query variables are used to construct dynamic SQL code , must be enclosed in []

Program ex15b.sqr

begin-program

   let $col1 = 'name'

   let $col2 = 'city'

   let #pos = 32

   do list_customers_for_state

   position (+1)

   let $col1 = 'city'

   let $col2 = 'name'

   let #pos = 18

   do list_customers_for_state

end-programbegin-procedure give_warning

   display 'Database error occurred'

   display $sql-error

end-procedure ! give_warningbegin-procedure list_customers_for_state

   let $my_order = $col1 || ',' || $col2

begin-select on-error=give_warning

[$col1] &column1=char (,1)

[$col2] &column2=char (,#pos)

   position (+1)

from customers

order by [$my_order]

end-select

end-procedure ! list_customers_for_state

How do you create SQR for multiple reports give a example?

 (+1,1)

  print #label_count () edit 9,999,999

end-procedure ! end_mailing_labelsbegin-procedure print_letter

use-report form_letter

begin-document (1,1)

&name

&addr1

&addr2

@city_state_zip

Program ex17b.sqr

#define MAX_LABEL_LINES        10

#define LINES_BETWEEN_LABELS   3begin-setup

  declare-layout labels

    paper-size=(10,11)   left-margin=0.33

  end-declare

  declare-layout form_letter

  end-declare

  declare-layout listing

  end-declare

  declare-report labels

    layout=labels

  end-declare

  declare-report form_letter

    layout=form_letter

  end-declare

  declare-report listing

    layout=listing

   end-declare

end-setup

begin-program

  do main

end-programbegin-procedure main

  do init_mailing_labels

begin-select

name

addr1

addr2

city

state

zip

  move &zip to $zip xxxxx-xxxx

phone

  do print_label

  do print_letter

  do print_listing

from customers

end-select

  do end_mailing_labels

end-procedure ! mainbegin-procedure init_mailing_labels

  let #label_count = 0

  let #label_lines = 0

  use-report labels

  columns 1 29 57  ! enable columns

  alter-printer font=5 point-size=10

end-procedure ! init_mailing_labelsbegin-procedure print_label

  use-report labels

  print &name   (1,1,30)

  print &addr1  (2,1,30)

  let $last_line = &city || ', ' || &state || ' ' || $zip

  print $last_line (3,1,30)

  next-column at-end=newline

  add 1 to #label_count

  if #current-column = 1

    add 1 to #label_lines

    if #label_lines = {MAX_LABEL_LINES}

      new-page

      let #label_lines = 0

    else

      next-listing no-advance skiplines={LINES_BETWEEN_LABELS}

    end-if

  end-if

end-procedure ! print_label

begin-procedure end_mailing_labels

  use-report labels

  use-column 0  ! disable columns

  new-page

   print 'Labels printed on ' (,1)

  print $current-date ()

  print 'Total labels printed = '

.b

.b

                                                $current-date

Dear Sir or Madam:

.b

    Thank you for your recent purchases from ACME Inc. We would

like to tell you about our limited time offer. During this month,

our entire inventory is marked down by 25%. Yes, you can buy your

favorite merchandise and save too.

    To place an order simply dial 800-555-ACME.

    Delivery is free too, so don't wait.

.b

.b

                                Sincerely,

                                Clark Axelotle

                                ACME Inc.

end-document

position () @city_state_zip

print &city  ()

print ', '   ()

print &state ()

print ' '    ()

move &zip to $zip xxxxx-xxxx

print $zip   ()

new-page

end-procedure ! print_letter

begin-heading 4 for-reports=(listing)

print 'Customer Listing' (1) center

   print 'Name' (3,1)

   print 'City' (,32)

   print 'State' (,49)

   print 'Phone' (,55)

end-headingbegin-footing 1 for-reports=(listing)

   ! Print "Page n of m" in the footing

   page-number (1,1) 'Page '

   last-page   () ' of '

end-footingbegin-procedure print_listing

  use-report listing

   print &name (,1)

  print &city (,32)

  print &state (,49)

  print &phone (,55)

  position (+1)

end-procedure ! print_listing

How do you generate Form letters?

By using Begin-document

1.WHY SQR PROGRAMMING REQUIRES MORE LOGIC ?

   MORE LOGIC IS REQUIRED WHICH IS NOT POSSIBLE WITH GUI REPORT TOOLS.

2.WHERE THE DATABASE UPDATES ARE REQUIRED AS PART OF SQR PROGRAM?

   REPORTS+TABLE CHANGES

3.WHAT R THE SQR USES?

   FOR REPORTING,BACK GROUND SQL PROCESSING,INTERFACE OR FILE HANDLING

4.WHAT R THE SECTION'S IN SQR, WHICH SECTIONS ARE MANDATORY?

   BEGIN-PROGRAM,BEGIN PROCEDURE,BEGIN HEADER,BEGIN FOOTER,BEGIN SETUP.

   BEGIN-PROGRAM SECTION IS MANDATORY FOR SQR PROGRAM

5.WHAT R THE PARAGRAPHS WE R HAVING IN SQR AND IN WHICH SECTIONS WE CAN USE PARAGRAPHS?

   a.BEGIN SELECT---WE CAN USE IN BEGIN PROGRAM AND BEGIN PROCEDURE

   b.BEGIN SQL----BEGIN PROGRAM,BEGIN PROCEDUREAND BEGIN SETUP

   c.BEGIN DOCUMENT--BEGIN PROCEDURE,BEGIN PROGRAM

6.WHAT R THE DIFFERENT TYPES OF VARIABLES WE HAVE IN SQR?

   a.COLUMN VARIABLES(&)

   b.SQR VARIABLES

       STRING VARIABLES($)

       NUMERIC VARIABLES(#)

       DATE VARIABLES($)

   c.SQR LITERALS

       STRING LITERALS

       NUMERIC LITERALS

       DATE LITERALS

   d.SYSTEM VARIABLES

   e.SUBSTITUTION VARIABLES

7.WHAT R THE RUNTIME VARIABLES AND COMPILE TIME VARIABLES?

   a.SQR BIND VARIABLES(SQR RUNTIME VARIABLES)

   b.SUBSTITUTIONAL VARIABLES

   c.DYNAMIC QUERY VARIABLES

8.WHAT R THE RUNTIME VARIABLES VARIABLES AVAILABLE IN SQR?

   a.INPUT

9.WHAT IS THE DIFFERENCE BETWEEN ASK AND INPUT?

   a.ASK WILL BE PROCESSED AT THE TIME OF COMPILE STAGE

     INPUT WILL BE PROCESSED AT THE TIME OF EXECUTION STAGE

   b.ASK WE CAN USE IN SET UP SECTION

     INPUT WE CAN USE IN ALL SECTIONS

   c.THERE IS NO DATATYPE AND FORMAT TYPE FOR ASK COMMAND

     INPUT WE CAN SPECIFY THE DATATYPE LENGTH AND FORMAT

   d.ASK WE CANNOT USE IN SQR REPORTS RUNNING FROM THE PEOPLESOFT ENVIRONMENT

     INPUT WE CAN USE IN PEOPLESOFT

10.WHAT R THE DEBUG COMMANDS WE R HAVING IN SQR?

   SHOW,DISPLAY NOLINE,DEBUG XXX(IS USED COMPULSARY IN COMMAND LINE FLAG),#DEBUG,#IF,#IF       NDEFF,#ENDIFF

11.WHAT IS THE DIFFERENCE BETWEEN SHOW AND ENDIFF COMMAND?

   USING THE SHOW COMMAND I CAN SHOW MORE THAN ONE VARIABLE VALUES IN A SINGLE LINE.

   USING DISABLE COMMAND WE CANNOT BUT WITH USING DISPLAY COMMAND WITH NOLINE WE CAN SHOW IN THE    SAME LINE.

12.WHAT R THE WAYS WE CAN INCREASE THE PERFORMANCE TUNING?

   LOAD LOOKUP,ARRAYS,MULTIPLE REPORTS,USING RUNTIME FILES(SQT),RUN ON THE SERVER

13.WHAT R THE DIFFERENT FILES WE USE IN SQR?

   .SQR,.LIS,.SPF,.SQT,.MAX,.LOG,.SQC

14.HOW TO DECLARE THE LOCAL VARIABLES?

   a.APPARENTLY WE HAVE TO SPECIFY AS LOCAL VARIABLE(LOCAL DATATYPE VARIABLENAME)

   b.IF U R USING THE VARIABLE IN LOCAL PROCEDURE THAT VARIABLE BECOMES LOCAL VARIABLE

15.HOW TO WRITE LOCAL PROCEDURE?

   APPARENTLY WE HAVE TO SPECIFY PROCEDURE AS LOCAL

     eg:BEGIN PROCEDURE PROCEDURE NAME LOCAL

   IF THE PROCEDURE IS HAVING PARAMETERS(IT CAN BE INPUT OR OUTPUT)

16.WHAT R THE STEPS REQUIRED TO RUN SQR FROM PEOPLESOFT?

   a.WRITE THE SQR SOURCE PROGRAM

   b.MAKE IT AS API AWARE

   c.CREATE THE PAGE USING EXISTING RUN CONTROL TABLES OR NEW RUN CONTROL TABLES.

   d.SCHEDULE THE PROGRAM USING PROCESS SCHEDULER MANAGER

17.WHAT IS THE USE OF MAKING SQR PROGRAM API AWARE?

   WE CAN SEE THE STATUS OF OUR SQR PROGRAM IN PROCESS MONITOR

18.WHAT SQC'S WE R USING TO MAKE YOUR PROGRAMAS API AWARE?

   STDAPI.SQC

19.WHAT ARE PROCEDURES IN STDAPI.SQC?

   STDAPI-INIT

   STDAPI-TERM

20.WHAT SQC'S NORMALLY WE R USING IN THE SQR PROGRAM IN PEOPLE SOFT ENVIRONMENT?

   SETENV.SQC

21.WHAT R THE SQC'S R AVAIBLE TO REPRESENT DATE PROCEDURES?

   DATETIME.SQC,CURRENTDATE.SQC

22.ERROR HANDLING?

   ONERROR = SKIP/WARN/STOP (INSETUP SECTION)

   ONERROR = PROCEDURE(NAME) (PROCEDURE SECTION)

23.HOW DO U RETRIEVE 1000 ROWS OUT OF 100000 IN SQR SELECT?

      BEGIN-SELECT PARAGRAPH LOOPS= NN

24.DO U HAVE INCLUDED FILES(.sqc's) AT THE BOTTOM OF AN SQR PROGRAM? 41201863

ans: yes --stdapi.sqc,  curdttime.sqc, datetime.sqc,number.sqc readxlat.sqc,sqrtrans.sqc.

25.HOW DO U WRITE OUTPUT OF AN SQR REPORT TO AN EXCEL FILE?

ans: GENERATE .CSV FILE

26.HOW DO U DEBUG THE SQR PROGRAMS?

ans: show,display,-debug, #debug, #ifdef,#ifndef,#if

27.DIFFERENT SECTIONS IN SQR?

ans:  begin-procedure,begin-program, begin-setup,begin-heading,begin-footing

28.WHAT IS THE MAX LEVELS YOU CAN USE "ON BREAK" COMMAND OF SQR.?

ans default is 30 which can be changed in sqr.ini and the max is 64k-1.

29.HAVE U DONE ANY MULTI LEVEL REPORTING USING SQR.?

if multiple reports then

 a yes using declare-layout and declare-report and then using it in the procedure.

if it is a multy-levels in on-break then

a yes, while using on-break on more than one field and use order by clause in the begin-select

30.NAME SOME system VARIABLES IN SQR.?

ans: $current-date,#sql-count,$sql-error,$sqr-database , #current-column,#current-line .

31.CAN U USE "ON-BREAK" COMMAND IF U R WRITING THE OUTPUT TO A FILE INSTEAD OF TO A PRINTER.?

ans: no we cannot use.

32.QUESTIONS ON THE INCLUDED ".SQC" FILES IN AN SQR REPORT/PROCESS...(LIKE FILES WHICH INTERACT

THE PROCESS MONITOR FOR UPDATING STATUS)....?

ans:stdapi.sqc

33.difference between ask and input in sqr?

ask is used in setup section only ask is used only for windows and there is no data type. where as input can be used anywhere and datatype,format and length needed to be declared compulsary

34. NAME SOME global VARIABLES IN SQR.?

a all the user defined variables are global.

35.   HOW DO U DEBUG THE SQR PROGRAM

Ans : using show and display

36.WHATS ARE THE SQC U HAVE USED

37.HOW DO U RUN SQR  FROM PEOPLE SOFT ENVIRONMENT?

38.WHAT IS LET,MOVE,SHOW , DISPLAY?

39.WHAT IS SQT FILE? WHAT ARE THE ADVANTAGES OF SQT FILES?

40.WHAT ARE THE TYPES OF REPORTS U CAN GENERATE USING SQR

41.   HOW DO WE INCLUDE SQR PROGRAM IN ANOTHER SQR PROGRAM?

42.   TELL ME ABOUT SQC’S INVOLVING DATES?

43.   TELL ME ABOUT SQC’S INVOLVING PRINTER?

44.   TELL ME ABOUT SQC’S INVOLVING PAGESETUP?

45.   WHAT IS USE OF .INI FILES?

46.   IF I WANT TO USE SYSTEM COMMANDS WHICH SQC I HAVE TO USE?

47.   HOW DO WE RUN SQR IN PEOPLE SOFT ENVIRONMENT?

48.   WHAT IS DIFFERENCE BET’N SHOW AND DISPLAY?

49.   HOW DO U DEBUG SQR PGMS?

50.   HOW DO U INCLUDE GRAPHS IN SQR SQR PGMS?

51.     CAN WE SEND SQR OUTPUT TO WORD DOCUMENT OR CRYSTAL REPORT?

52.   CAN WE USE ON-BREAK ON NUMERIC VARIABLES?

53.   SQC'S

54.   HOW CAN YOU MAKE SQR API-AWARE?