SQL

Integration Broker

PeopleSoft Integration Broker related articles, setups, tips and tricks.

Breadcrumb SQL to find the Navigation path for the component in the PIA

Breadcrumb SQL:

WITH parent (breadcrumb, portal_label, portal_objname, portal_prntobjname, portal_reftype, component) AS
(SELECT varchar(rtrim(portal_label), 1000)
, portal_label
, portal_objname
, portal_prntobjname
, portal_reftype
, RIGHT(portal_urltext, length(portal_urltext) - locate('.', portal_urltext))
FROM psprsmdefn
WHERE portal_name = 'EMPLOYEE'
AND portal_reftype = 'C'
UNION ALL
SELECT varchar(rtrim(c.portal_label), 1000) || ' > ' || p.breadcrumb
, c.portal_label
, c.portal_objname
, c.portal_prntobjname
, c.portal_reftype
, p.component

SQL beautifier

Here comes a sql beautifier web site for the readers who don’t know about this.

We can format the sql in a second by using this. Give a try and will see the difference in sql. Useful to understand the sql in easier way.

http://www.dpriver.com/pp/sqlformat.htm

Regards,
Hari.A

SQL practise - Online

Here is an online SQL learning website with Interpreter

http://sqlcourse.com/create.html

This will be useful for all beginners.

Regards,
Hari.A

START WITH and CONNECT BY in Oracle

Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.

Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?

SELECT emplid, supervisor_id
  FROM ps_employees
 WHERE supervisor_id = '1122456';

EMPLID  SUPERVISOR_ID