Search This Blog

Loading...

Thursday, June 26, 2014

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

Tuesday, June 17, 2014

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

Saturday, March 1, 2014

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

Friday, January 24, 2014

PeopleSoft: Working with grids have same record at Level 0 and Level 1

In PeopleSoft, we use grids to populate data in a list wise manner. And generally in Setup pages, we see same record being used at Level 0 and Level 1.

In such case, there was an issue that creeped up while saving the data in the page. Along with the data in the grid , extra row gets inserted to the database record which contains only data for Level0 fields.

To avoid the unwanted row, it is noticed that the page design is causing the issue. The Level 0 field which we enter data from Search Page is in Edit mode. So the Component Processor allocated buffer for the Level0 fields expecting some data. During Save , along with Level1 data in grid , Level 0 data also got inserted.

To resolve the issue, the Level 0 field is made 'Display Only', such that Component processor will treat this field as 'Read Only' and does not allocate buffer. Now when we save data , only data from the grid gets inserted.

Monday, January 13, 2014

Oracle: Differnce between to_date('01-JAN-00','DD-MON-RR') and to_date('01-JAN-1900','DD-MON-YYYY')

Difference between to_date('01-JAN-00','DD-MON-RR') and to_date('01-JAN-1900','DD-MON-YYYY')

Result of to_date('01-JAN-00','DD-MON-RR') will be '01-JAN-2000'  where 'RR' refers to current century
Result of to_date('01-JAN-1900','DD-MON-YYYY') will be '01-JAN-1900'.

Note: By default, if you do a 'Save As' with Insert , the default format will be in 'RR'.



Saturday, November 30, 2013

GetFile and Open file

Both get file and open file are used to access file from server.
Both have the same syntax.
GetFile(filename, mode [, charset] [, pathtype])
Open   (filespec, mode [, charset] [, pathtype])

Only difference is that Whenever a call is made to GetFile , a new instance is created. Meaning each instance requires a separate file object to refer the file.
So usage of Getfile will be effective in case multiple files are accesed at a time.

For Open file , for each call same file object is used to access file.
That means, file object &File can be used to access file1 and subsequent call to another file closes the current file and accesses the another file.

Tuesday, October 29, 2013

Output in multiple Excel sheets using VB Script

Lets see how we add data to sheets manually.
We need an Excel Application to open Excel file. So we click on 'MS Excel'.
Then a new workbook opens.
First sheet enter data and name the tab(sheet name).
In the Second sheet enter data and name the tab(sheet name).
Save and Close the file.

The same is done through programming in the below example.

Set objXL = CreateObject("Excel.Application") -- Create XML application object.
Set objWB = objXL.Workbooks.Add   -- Open Workbook
Set objSHTLG = objWB.Worksheets(1) -- Open first sheet
objSHTLG.Name = "ABC"   -- Name the first sheet

With objSHTLG.QueryTables.Add("TEXT;D:\ABC.txt", objSHTLG.Cells(1, 1)) 
-- Load data from ABC.txt file
.Refresh BackgroundQuery = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 2)
.Refresh BackgroundQuery = False
End With

Set objSHTLG = objWB.Worksheets(2) -- Open Second Sheet
objSHTLG.Name = "TEST"  -- Name the Second sheet

With objSHTLG.QueryTables.Add("TEXT;D:\TEST.txt", objSHTLG.Cells(1, 1))
-- Load data from TEST.txt file
.Refresh BackgroundQuery = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 2)
.Refresh BackgroundQuery = False
End With

objWB.SaveAs "C:\Book1.xlsx" -- Save the Excel File as Book1.xlsx
objXL.Quit  -- Close the file.

Some Info on the below code:
With objSHTLG.QueryTables.Add("TEXT;D:\ABC.txt", objSHTLG.Cells(1, 1)) 
TEXT -- refers type of file
After semicolon, the file path from which data gets loaded onto the sheet.
objSHTLG.Cells(1, 1) -- Row 1 Col 1 of the sheet from which data load happens.

Running the VB Script: Double Click on the ".vbs" file. The code gets executed and a new file "Book1.xlsx" gets created.

Below is the snapshots of the practical example:
Text Files which are considered in the example.
Content in Text Files:

Output after running VB Script.