Union / Minus operator in SQR

There are different scenarios where we will be using Union / Union all / Intersect / Minus operator in sqr.
below is the example of using union in sqr

begin-select 
field-a &fld-a 
field-b &fld-b 
from tbl1 
where ....
union 
select field-c, field-d from tbl2 where....
end-select

Delete PeopleSoft Query

There could be different reasons we would like to delete a query from the database. Below are the SQL that are needed to delete a query from database.

   DELETE FROM PSQRYDEFN WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYSELECT WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYRECORD WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYFIELD WHERE QRYNAME='Queryname';

The macro cannot be found or has been disabled while accessing BI publisher in MS Word

Recently I have installed XML publisher in my local client workstation.

When I open Microsoft word and try to access Oracle BI publisher I got below error

From different google sources below are two most common errors and how should Word be setup correctly

Templates should correctly enabled

Even after above 2 actions my issue is not solved.

Solution to above issue is

Solution 1:

Search for MSComctlLib.exd in your local drive and rename all the existence, such as MSComctlLib.exd_bak.

usually the file will occur in %USERPROFILE%Application Data\Microsoft\Forms or C:\Users\ramu\AppData\Roaming\Microsoft\Forms

now re-open microsoft word, this will create new file of MSComctlLib.exd  and try accessing BI publisher.

if above soltion doesnot solve your issue. try below

Solution 2:

For 64-bit operating systems, type the following in command prompt (Run command prompt as administrator, otherwise you will get error code 0x80004005): 

Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"

For 32-bit operating systems, type the following:

Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

open the Microsoft word and access BI Publisher now.

Equivalent of grep in windows

There are instances where we want to search for table / field referenced in sqr on windows.
The built in windows command FindStr mirrors the capabilities of the Unix command Grep.

Findstr /?
FINDSTR [/B] [/E] [/L] [/R] [/S] [/I] [/X] [/V] [/N] [/M] [/O] [/P] [/F:file]
        [/C:string] [/G:file] [/D:dir list] [/A:color attributes] [/OFF[LINE]]
        strings [[drive:][path]filename[ ...]]
  /B         Matches pattern if at the beginning of a line.
  /E         Matches pattern if at the end of a line.
  /L         Uses search strings literally.
  /R         Uses search strings as regular expressions.
  /S         Searches for matching files in the current directory and all
             subdirectories.
  /I         Specifies that the search is not to be case-sensitive.
  /X         Prints lines that match exactly.
  /V         Prints only lines that do not contain a match.
  /N         Prints the line number before each line that matches.
  /M         Prints only the filename if a file contains a match.
  /O         Prints character offset before each matching line.
  /P         Skip files with non-printable characters.
  /OFF[LINE] Do not skip files with offline attribute set.
  /A:attr    Specifies color attribute with two hex digits. See “color /?”
  /F:file    Reads file list from the specified file(/ stands for console).
  /C:string  Uses specified string as a literal search string.
  /G:file    Gets search strings from the specified file(/ stands for console).
  /D:dir     Search a semicolon delimited list of directories
  strings    Text to be searched for.
  [drive:][path]filename Specifies a file or files to search.

Example 1:

findstr /n /i /s /c:"ps_job" "d:\sqr\*.*" > "c:\temp\output.txt"

The above example will search for ps_job in all files on folder / sub-folders of d:\sqr\ and redirect the output to output.txt in c:\.
/n - will print the line number ps_job is referenced.
/i - will ignore case-sensitive
/s - will search string in all files on folder and subfolders od d:\sqr\
/c - denotes to search for string.

Example 2:

findstr /n /i /s /g:"d:\search_all.txt" "d:\sqr\*.*" > "c:\temp\output.txt"

we can also search for multiple strings in one command.
save the file having multiple strings in d:\search_all.txt (multiple strings should be ended with newline)
this example will search of all strings defined in d:\search_all.txt on d:\sqr\ and redirect the output to c:\temp\output.txt

Time and Labor installation steps

Below are the basic installation steps required for Time and Labor

(SQR 4407) Referenced variables not defined

There are 2 causes of this issue to occur.

1. Code between Begin-Select and End-select are not correctly formatted
or
2. check for variable which got error out, if the variable is referenced inside a procedure which have parameters (Called as function) and also referenced outside the issue occurs, the solution will be pass the referenced variable as parameter to the procedure.

Example for 2:

begin-select
----
----
    let $emplid=&emplid
----
----
end-select
do proc1($setid)


begin-procedure proc1($setid)
begin-select
----
---
where emplid = $emplid and setid=$setid
----
end-select
end-procedure

should be changed as below

begin-select

----

----

    let $emplid=&emplid

----

----

end-select

do proc1($setid,$emplid)

begin-procedure proc1($setid,$emplid)

begin-select

----

---

where emplid = $emplid and setid=$setid

----

end-select

end-procedure

(SQR 9004) TrueType font file cumbwr__.ttf cannot be opened

There are two things to check; either the font file does not exist in the location specified in pssqr.ini file, or the path specified in sqr.ini file is not correct. 

01 = Font type. 
Verify if the setup is correctly into the pssqr.ini to the  [TrueType Fonts] section 
The Font Path=directory where fonts resides and the TrueType collection file (.ttf).

For example:
 [TrueType Fonts]

; This section specifies the mapping from TrueType font names used in 
; above configuration section and physical file path of the font on 
; operating system. For TrueType collection file (.ttc), font directory 
; number should also be specified.
; (ex. font name=file path, directory number)

; Font Path=directory where fonts resides.  Default is SQRDIR.  On 
; Windows, Windows font directory will be looked up too.  Fonts not 
; residing on other directories must be specified full physical path.

Font Path= <PSHOME>\fonts\truetype\
Albany=albw.ttf
Albany-Bold=albwb.ttf
Cumberland=cumbwr__.ttf
Cumberland-Bold=cumbwb__.ttf
Thorndale=thowr___.ttf
Thorndale-Bold=thowb___.ttf
Angsana=angsa.ttf

02 = Font file path.
Verify if the thowr___.ttf collection is located to the correct destination  <PSHOME>\fonts\truetype.

Note: if you are running sqr on unix then check pssqr.unx file