SQL Query to fetch in csv format

Query to fetch in csv format

DECLARE @theList varchar(100)
SELECT
@theList = COALESCE(@theList + ', ', '') + RTRIM(ACCOMPLISHMENT)
FROM PS_ACCOMPLISHMENTS
WHERE EMPLID = '3820001'
SELECT @theList

Removing new line character from a string in SQR

Removing new line character in a string

let $enter = chr(13) chr(10)
let $newComment = replace($Comment, $enter, ' ')

Lpad in SQL Server

By converting to a string and padding with the string..

Eg:

SELECT RIGHT(REPLICATE('0',6)+ CONVERT(VARCHAR(6),'7'),6)

SELECT RIGHT(REPLICATE('0',6)+ CONVERT(VARCHAR(6),SUBSTRING(RTRIM('T000006'),2,LEN(MAX('T000006'))-1)+1),6) FROM PS_PERSON

Recursion in SQR

Sometimes, especially if the data is organized in a hierarchical or tree structure, it makes sense to have a procedure call itself. This is known as a recursive call. SQR, unlike most languages, does not have a call stack. So, in order for the recursion to work as expected we have to emulate one in the SQR code itself. It gets even a bit more complicated if we need "begin-select" sections in the recursive procedure. If SQR encounters the "begin-select" line again before the previous cursor is closed it will just fetch the next row. Below are two examples, each with a correct program and one that you might think would work at first glance.

Fibonacci Sequence: Each number is the sum of the two previous numbers. So, the sequence starts: 1,1,2,3,5,8,13,21,34 . . . fib_right.sqr fib_wrong.sqr

Family Tree: This example descends a family tree starting with an ancestor. recurse_right.sqr recurse_wrong.sqr

Source: http://www.ontko.com/sqr/

Justifying text in SQR Reports

Blocktxt.sqr is a program that illustrates the use of a technique of printing a text string so that it wraps, with justified text. In other words, it will be flush left AND flush right, with spaces placed through the text. This style of print is often used in newspaper columns.

!
! This program shows how to block a paragraph (also known as flush right).
! It is not intended to handle all situations and is not supported by MITI.
!
begin-report
do main
end-report

begin-setup
#define paragraph_width 60 ! define the width of the paragraph
end-setup

begin-procedure main

let $str = 'This is a paragraph of text that will be printed as a block. In other words, it will be printed in newspaper terminology as "flush right". The basic technique is divide the white space at the end of each portion into the gaps.'
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta

while 1
!
! loop and process portions of the string
!
do get_next_str($str,$substr)
if $str = ''
!
! null string indicates that the last portion was processed
! only print this part, don't add blanks to it
!
print $substr (+1,10)
break
else
!
! add blanks to the current sub-string
!
do add_blanks_to_gaps($substr)
print $substr (+1,10)
end-if
end-while
end-procedure

begin-procedure get_next_str(:$str,:$substr)
!
! this procedure returns a portion of a string
! and sets the original string to the remainder
!
let #l = length($str)
if #l <= {paragraph_width}
!
! last portion of the string, so return that and set original string to null
!
move $str to $substr
move '' to $str
else
!
! determine how much of the string to return as the substring
!
let #i = {paragraph_width} + 1
let $substr = substr($str,1,#i)
while #i > 0
let #j = instr($substr,' ',#i)
subtract 1 from #i
if #j > 0
break
end-if
end-while
!
! form the $substr and return the remainder of the string in $str
!
let $substr = substr($str,1,#i)
let $str = substr($str,#i+2,#l-#i-1)
end-if
end-procedure

begin-procedure add_blanks_to_gaps(:$substr)
!
! this procedure adds spaces to the "gap" in the substring
! so that the length of the substring is equal to the paragraph width
!
! first determine how long the substring is (#l)
! and how many spaces to add to the substring
!
move ' ' to $gap
let #l = length($substr)
let #s = {paragraph_width} - #l
move 1 to #i
while #s > 0
!
! loop and look for the next "gap"
!
let #j = instr($substr,$gap,#i)
if #j = 0
!
! no more "gaps" in the string, increase $gap and go back to the start
!
let $gap = $gap ' '
move 1 to #i
break
else
!
! found a gap, form a string that will have an extra space at the "gap"
!
let #l = length($substr)
let $substr = substr($substr,1,#j)
substr($substr,#j,length($substr)-#j+1)
let #i = #j + 2
subtract 1 from #s
end-if
end-while
end-procedure

! Here is the sample output

! This is a paragraph of text that will be printed as a block.
! In other words, it will be printed in newspaper terminology
! as "flush right". The basic technique is divide the white
! space at the end of each portion into the gaps.

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

SQLExec

It helps you to execute SQL command from within Peoplecode programs.
It bypasses the component processor and interacts directly with the database.

Syntax
SQLExec({sqlcmd SQL.sqlname}, bindexprs, outputvars)

Limitations:

SQLExec can only select single row of data. If the SELECT statement retrieves more than one row of data, Output variables will hold only the first row of data and the remaining rows will get discarded.

SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events: (SavePreChange,WorkFlow,SavePostChange,FieldChange)

The number of output variables cannot exceed 64.

Examples:

Using Bind Variables in SQLExec
SQLExec(“Select first_name from ps_names where emplid = :1 and name_type = :2”,&emplid, &name_type,&out_first_name)

Using Inline Bind Variables
Inline bind variables are included directly in the SQL string in the form:
:recordname.fieldname
SQLExec("Select sum(posted_total_amt)
from PS_LEDGER
where deptid between :LEDGER.DEPTID_FROM
and :LEDGER.DEPTID_TO", &sum);

Note: You have to change the Inline Bind Variables inside quotes if you are changing the record field name.

Output Variables in SQLExec
SQLExec(“Select first_name from ps_names where emplid = :1 and name_type = :2”,&emplid, &name_type,&out_first_name)

Using Arrays for Bind Variables
By using parameter of type "Array of Any" in place of a list of bind values or in place of a list of fetch result variables, helps you to simplify the statement. This is generally used when you don't know how many values are needed until the code runs.
&Stmt = "INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . . N) VALUES (:1, :2, %DateTimeIn(:3), %TextIn(:4), . . .N)";

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . .N);

SQLExec(&Stmt, &AAny);

Know about Peoplesoft?

What is Peoplesoft?
PeopleSoft is an integrated software package that provides a wide variety of business applications to assist in the day-to-day execution and operation of business processes. Each individual application, such as Financials, Customer Relationship Management and Human Resources, interacts with others to offer an effective and efficient means of working and reporting in an integrated fashion across the enterprise.

What are the modules PeopleSoft offers?
PeopleSoft offers the following modules: Customer Relationship Management Financial Management Service Automation Human Capital Management Campus Solutions Supplier Relationship Management Enterprise Performance Management Supply Chain Management Supply Chain Planning Customer Order Management Logistics Management Manufacturing Supplier Relationship Management Asset Lifecycle Management and more

What's the difference between PeopleSoft, SAP, Oracle and Siebel?
PeopleSoft, SAP and Oracle are the biggest 3 ERP vendors. They make Enterprise Resource Planning software. They also have a lot of larger customers as opposed to some of the smaller vendors. Peoplesoft is no longer a separate company. Oracle has acquired in Dec 2004. Now Oracle is giving support to existing Peoplesoft users. Siebel is primarily a software company that makes CRM (Customer Relationship Management) software. They have a lot of mid market clients as well as a few large ones. Oracle has acquired Siebel also in Jan 2006.