How to generate XML file from SQR program?

Like any other file, the basic logic to opening a file / writing data and then closing file is same even for XML file generation.

Of-course, the approach followed to implement these basic stuff will be slightly different. Here we need to make use of  xml elements. So thoughtful of time needs to be spent on the design and layout of the XML file.

Approach to design XML file and implement the logic:

Once we are clear with the required information, we should next proceed to implementing the logic.

For instance, let's consider we need to generate XML file which has Address Details of an Employee.

Step 1 would be to first categorize elements. So here we can select Employee as outer element.

Step 2 would be to list out the data elements like here it is Address1,City,State,Country

XML will have to look something like below. 

Note, intentionally excluded the tag - <  in the below example as the below structure auto-converts without tags when viewed online.

?xml version="1.0" encoding="Windows-1252"?>
Employee>
Address>
Address1>"Hitec City"/Address1>
City>"Hyderabad"/City>
State>"TS"/State>
Country>"India"/Country>
/Address>
Employee>

To implement the above logic in SQR, first you need to initialize xml tags.

let $xmlcntrl_begin  = '<'

   let $xmlcntrl_end    = '>'

   let $xmltag_end      = '/'

   let $xmltext_delim   = '"'

   let $xmltag          = ''

   let $xmlcontent      = ''

   let $xmloutput_line  = ''

etc..

Below is sample code to write content.

let $xmloutput_line = $xmlcntrl_begin || $xmltag || $xmlcntrl_end  ||

                               $xmlcontent ||

                               $xmlcntrl_begin || $xmltag_end || $xmltag || $xmlcntrl_end

         write #outputfile  from $xmloutput_line

if we decode above statement it would resolve to something like.

<City>"Hyderabad"</City>

Note: if there are nested elements, ensure the tags are properly closed.

Once the data is written, we need to close XML file.

Execution:

Once the SQR is run , the file will be seen posted in the server path mentioned while opening the file.

SQR: How to create Summary Charts using print-chart

Usually Summary reports, show up break up of count of records processed of the total records.
Representing these Summary reports through diagrams will help client understand better.

 Declare-Chart is used to describe the layout of the chart.
Array is created to store data.
Through Print-Chart we read data from Array and print onto the chart.

Below is one sample code to retrieve data and display in chart.

Begin-Setup
 Create-Array    ! This Array is passed to the Print-Chart
    Name = emp_hired
    Size = 10    ! Maximum of 10 rows of data
    Field = count:number:3    ! Fields in each row
  Declare-Chart tot_emp_hired
    Chart-Size = ( 30,30)
    Title = 'Employee Fetched'
    Type = histogram
    3D-Effects = yes
    X-Axis-Label = 'Company'
    Y-Axis-Label = 'Employee Hired'
  End-Declare
End-Setup

begin-program
 do Stdapi-Init        !stdapi.sqc
 do Init-DateTime    ! datetime.sqc
 do Init-Number        ! number.sqc
 do Get-Current-DateTime! curdttim.sqc
 do EmpChartReport
 do Stdapi-Term            !stdapi.sqc
end-program

begin-procedure EmpChartReport
! Load the array with the Chart Data. Max rows is specified in Create-Array
  Put  20
  Into emp_hired(0)  count(0)
  Put  12
  Into emp_hired(1)  count(0)   
  Put  4
  Into emp_hired(2)  count(0)

  Move 5 TO #x
  Move 10 TO #y
  Move 3 TO #row
  Move 3 TO #col

 Print-Chart tot_emp_hired (#x,#y)
    Fill = color
    Sub-Title = 'Employee hired report'
    Data-Array-Row-Count = #row   
    Data-Array-Column-Count = #col
    Data-Array-Column-Labels = ('ABC1', 'ABC2', 'ABC3')
    Data-Array = emp_hired
end-procedure

Sample Output:

SQR: Global Variables

In SQR, we donot explicitly declare variable as global in general unless the variables are passed through the procedure in_list.
Below is the sample program to demonstrate usage of variable value globally.

!**********************************************************************
!begin-program
!**********************************************************************

Begin-Program
  do Stdapi-Init          !stdapi.sqc
  do Init-Report          !Init Report        
  do Process-Main       !Process-Main-Logic
  do Stdapi-Term              !stdapi.sqc 
End-Program

!**********************************************************************
!Init-Report
!**********************************************************************
begin-procedure Init-Report
let $Flag = 'N'
end-procedure Init-Report

!**********************************************************************
!Process-Main
!**********************************************************************
begin-procedure Process-Main
! Business logic
BEGIN-SELECT

show ' Before override = ' $Flag
let $Flag = 'Y'
show ' After override = ' $Flag
    do procedure-1
 END-SELECT
end-procedure Process-Main

!**********************************************************************
!procedure-1
!**********************************************************************
begin-procedure procedure-1
let $Flag = 'N'
show ' In procedure-1 = ' $Flag
end-procedure procedure-1

Output:
Before override = N
After override = Y
In procedure-1 = N

SQR: Date formatting in CSV file

For writing data to ouput files we use write command in SQR. And if we have date as one of the output fields, clients prefer '01062014'. This can be achieved by applying formatting to the date field.

Below is the manual way of doing in Excel file. Enter date as show in pic 1 and tab out.
Once we tab out, below is the format of the date that appears in the file.




But this is not we expected. So now lets apply formatting.
So when we prepend =" to date and append ", it converts date to Text format , thus retaining required format.



Fine. Now we need to achieve same through the SQR. Below is the sample code to do the same.
Sample Code:
  let $write_header = 'EMPLID,EFFDT,FORMATTED EFFDT'
  let $emplid = 'EMP101'
  let $effdt  = '01022014'
  let $text = '="05062014"'

  let $delim = ','
 
write 1 from $write_header

write 1 from $emplid
         $delim
         $effdt
         $delim
         $text
SQR Output for a comma separated CSV file

SQR Basics: Usage of DISTINCT clause

Below is the syntax for the usage of DISTINCT clause in SQR.

BEGIN-SELECT DISTINCT
[COL1]
[COL2]
.
.
.
FROM [RECORD_NAME]
END-SELECT

SQR: BEGIN-SELECT to get Count value

In SQR, we use below select construct to retrieve data from database.

BEGIN-SELECT
COL1
COL2
.
.
FROM TABLE_NAME
WHERE CRITERIA
END-SELECT

Now I need count of EE working in Department '10'. Below is the code to achieve this.

BEGIN-SELECT
COUNT(EMPLID) &Count_EE
FROM EE_DEPT_TBL
WHERE DEPARTMENT_ID = 10
END-SELECT

Sometimes, by mistake we might use #Count_EE as column variable since the value of Count is numeric. So coming to basics, all column variables will be referred with & (Ampersand).

SQR Commands

1. Example of Rpad

let $COUNTRY = 'USA'

Below example adds spaces at the end.
Rpad($COUNTRY,6,' ') ==> USA   
                                       1 2 3 4 5 6

Rpad($COUNTRY,6,z) ==> USA  z z z
                                       1 2 3 4 5 6

Rpad($COUNTRY,3,z) ==> USA
                                       1 2 3

2. on-error command:

begin-sql on-error=Procedure-name

end-sql

The procedure will act as error handler and will have below code.
Whenever a runtime error occurs, the dynamic error message will be displayed.

begin-procedure Procedure-name
display 'Database error occurred'
display $sql-error
end-procedure ! Procedure-name

Writing SQR Program Part 1

SQR --- Structured Query Reporting tool.

SQR Program is divided into 3 parts.

1. Header Section -- Report Title/Report Time/Report Name etc fit into this section.
2. Body   Section  -- Main Program logic goes into this section.
3. Trailer Section  -- Page Number etc will go in this section.

Remember Line 1 of Header section is different from Line 1 of Body section. That means each section of the program has its own memory area.

Lets see how to write program with these sections.

BEGIN-HEADER
END-HEADER

BEGIN-PROGRAM
END-PROGRAM


BEGIN-FOOTER
END-FOOTER

Example:
Reference of below program is from peoplebooks.
begin-program
   print 'Hello, World.' (1,1)
end-program
begin-heading 1
   print 'Tutorial Report' (1) center
end-heading
begin-footing 1
   ! print "Page n of m" in the footing
   page-number (1,1) 'Page '
   last-page   () ' of '
end-footing

begin-heading 1 means Header requires 1 line of space for printing Title.