Home Home » Archives for January 2022

union vs union all in sql

Posted on January 20, 2022January 20, 2022 by JointLantic

union vs union all in sql oracle

The UNION operator is a set operator that combines result sets of two or more SELECT statements into a single result set.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)

UNION statement

The following illustrates the syntax of the UNION operator that combines the result sets of two queries:

select name1 from person1
union
select name2 from person2
union 
select name3 from person3

select name1 from person1

union

select name2 from person2

union

select name3 from person3

In this statement, the name1 , mame2 and name3 must have the same number of columns presented in the same order. In addition, the data type of the corresponding column must be in the same data type group such as number or character.

UNION example

select 'nametest' from dual
union
select 'nametest' from dual
union 
select 'nametest' from dual

select 'nametest' from dual

union

select 'nametest' from dual

union

select 'nametest' from dual

Result union statment

nametest

UNION ALL statement

select name1 from person1
union all
select name2 from person2union all select name2 from person2

select name1 from person1

union all

select name2 from person2union all select name2 from person2

UNION ALL example

select 'nametest' from dual
union all
select 'nametest' from dual
union all 
select 'nametester' from dual

select 'nametest' from dual

union all

select 'nametest' from dual

union all

select 'nametester' from dual

Result union all statment

nametest
nametest
nametester

Reference :

Oracle UNION

https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all

How do I find menu navigation in PeopleSoft

Posted on January 12, 2022January 12, 2022 by JointLantic

How do I find navigation in PeopleSoft

SELECT RTRIM(E.PORTAL_LABEL)
|| ' >> ' || RTRIM(D.PORTAL_LABEL)
|| ' >> ' || RTRIM(C.PORTAL_LABEL)
|| ' >> ' || RTRIM(B.PORTAL_LABEL)
|| ' >> ' || RTRIM(A.PORTAL_LABEL)
FROM PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C  ON C.PORTAL_NAME = B.PORTAL_NAME AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN D  ON D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN E  ON E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME WHERE A.PORTAL_URI_SEG2 = 'JOB_DATA'

SELECT RTRIM(E.PORTAL_LABEL)

|| ' >> ' || RTRIM(D.PORTAL_LABEL)

|| ' >> ' || RTRIM(C.PORTAL_LABEL)

|| ' >> ' || RTRIM(B.PORTAL_LABEL)

|| ' >> ' || RTRIM(A.PORTAL_LABEL)

FROM PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME = B.PORTAL_NAME AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME WHERE A.PORTAL_URI_SEG2 = 'JOB_DATA'

Result query

How to get navigation using process name in PeopleSoft?

SELECT DISTINCT RTRIM( REVERSE ( SYS_CONNECT_BY_PATH(REVERSE (portal_label), ' >-')) ,' >- ') PATH
FROM PSPRSMDEFN
WHERE portal_name = 'EMPLOYEE'
AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'
START WITH portal_uri_seg2 IN (
SELECT DISTINCT pnlgrpname
FROM ps_prcsdefnpnl
WHERE prcsname = 'PTSF_GENFEED')
CONNECT BY PRIOR portal_prntobjname = portal_objname;

SELECT DISTINCT RTRIM( REVERSE ( SYS_CONNECT_BY_PATH(REVERSE (portal_label), ' >-')) ,' >- ') PATH

FROM PSPRSMDEFN

WHERE portal_name = 'EMPLOYEE'

AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'

START WITH portal_uri_seg2 IN (

SELECT DISTINCT pnlgrpname

FROM ps_prcsdefnpnl

WHERE prcsname = 'PTSF_GENFEED')

CONNECT BY PRIOR portal_prntobjname = portal_objname;

Query Result

Referrence 1: https://hmahanta.blogspot.com/2021/07/useful-quesries-in-peoplesoft.html
Referrence 2: https://www.shyamsblog.com/2010/10/how-to-get-navigation-using-process.html

SQL Query all Records with PeopleSoft Component

Posted on January 12, 2022January 12, 2022 by JointLantic

SQL Query for Retrieve all Records associated with a PeopleSoft Component

SELECT R.RECNAME AS RECORD_NAME,
       ( CASE
           WHEN R.RECTYPE = 0 THEN 'Table'
           WHEN R.RECTYPE = 1 THEN 'View'
           WHEN R.RECTYPE = 2 THEN 'Derived'
           WHEN R.RECTYPE = 3 THEN 'Sub Record'
           WHEN R.RECTYPE = 5 THEN 'Dynamic View'
           WHEN R.RECTYPE = 6 THEN 'Query View'
           WHEN R.RECTYPE = 7 THEN 'Temporary Table'
           ELSE 'Unknown'
         END )   AS RECORD_TYPE
FROM   PSRECDEFN R
WHERE  R.RECNAME IN (SELECT DISTINCT RECNAME
                     FROM   PSPNLFIELD
                     WHERE  PNLNAME IN (SELECT DISTINCT B.PNLNAME
                                        FROM   PSPNLGROUP A,
                                               PSPNLFIELD B
                                        WHERE  ( A.PNLNAME = B.PNLNAME
                                                  OR A.PNLNAME = B.SUBPNLNAME )
                                           AND A.PNLGRPNAME='JOB_DATA' /*Component Name*/
                                           AND RECNAME <> ' ')
                     UNION
                     SELECT DISTINCT RECNAME
                     FROM   PSPNLFIELD
                     WHERE  PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME
                                        FROM   PSPNLGROUP A,
                                               PSPNLFIELD B
                                        WHERE  ( A.PNLNAME = B.PNLNAME
                                                  OR A.PNLNAME = B.SUBPNLNAME )
                                           AND A.PNLGRPNAME='JOB_DATA'/*Component Name*/
                                           ))
   AND R.RECNAME <> ' '
ORDER  BY R.RECTYPE ;

SELECT R.RECNAME AS RECORD_NAME,

( CASE

WHEN R.RECTYPE = 0 THEN 'Table'

WHEN R.RECTYPE = 1 THEN 'View'

WHEN R.RECTYPE = 2 THEN 'Derived'

WHEN R.RECTYPE = 3 THEN 'Sub Record'

WHEN R.RECTYPE = 5 THEN 'Dynamic View'

WHEN R.RECTYPE = 6 THEN 'Query View'

WHEN R.RECTYPE = 7 THEN 'Temporary Table'

ELSE 'Unknown'

END ) AS RECORD_TYPE

FROM PSRECDEFN R

WHERE R.RECNAME IN (SELECT DISTINCT RECNAME

FROM PSPNLFIELD

WHERE PNLNAME IN (SELECT DISTINCT B.PNLNAME

FROM PSPNLGROUP A,

PSPNLFIELD B

WHERE ( A.PNLNAME = B.PNLNAME

OR A.PNLNAME = B.SUBPNLNAME )

AND A.PNLGRPNAME='JOB_DATA' /*Component Name*/

AND RECNAME <> ' ')

UNION

SELECT DISTINCT RECNAME

FROM PSPNLFIELD

WHERE PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME

FROM PSPNLGROUP A,

PSPNLFIELD B

WHERE ( A.PNLNAME = B.PNLNAME

OR A.PNLNAME = B.SUBPNLNAME )

AND A.PNLGRPNAME='JOB_DATA'/*Component Name*/

))

AND R.RECNAME <> ' '

ORDER BY R.RECTYPE ;

Result SQL

SQL query record peoplesoft component

SELECT DISTINCT cp.pnlgrpname,  
                cp.market,
                cp.descr,
                pgf.recname,
                r.recdescr,
                r.rectype FROM  pspnlgrpdefn cp,
       pspnlgroup cpg,
       pspnldefn pg,
       pspnlfield pgf,
       psrecdefn r
WHERE cp.pnlgrpname = 'JOB_DATA' /* Component Name*/
       AND cp.market = 'GBL'
       AND cp.pnlgrpname = cpg.pnlgrpname
       AND cp.market = cp.market
       AND pg.pnlname = cpg.pnlname
       AND pgf.pnlname = pg.pnlname
       AND pgf.recname = r.recname;

SELECT DISTINCT cp.pnlgrpname,

cp.market,

cp.descr,

pgf.recname,

r.recdescr,

r.rectype FROM pspnlgrpdefn cp,

pspnlgroup cpg,

pspnldefn pg,

pspnlfield pgf,

psrecdefn r

WHERE cp.pnlgrpname = 'JOB_DATA' /* Component Name*/

AND cp.market = 'GBL'

AND cp.pnlgrpname = cpg.pnlgrpname

AND cp.market = cp.market

AND pg.pnlname = cpg.pnlname

AND pgf.pnlname = pg.pnlname

AND pgf.recname = r.recname;

Result query

SQL queries to retrive all record in peoplesoft component

Reference : https://hmahanta.blogspot.com/2021/07/useful-quesries-in-peoplesoft.html

peoplesoft process scheduler tables

Posted on January 12, 2022January 12, 2022 by JointLantic

Peoplesoft process scheduler tables

PS_PRCSDEFN – Process Definition Header
PS_PRCSDEFNGRP – Process Group
PS_PRCSDEFNPNL – Component
PS_PRCSJOBDEFN – Job Header
PSPRCSRQST – Process Request Instances
PS_PRCSJOBITEM – Job Processes
PSPRCSQUE – Process Request Queue
PSPRCSPARMS – Contains process request parameters used by Process Scheduler to run the process
PS_PRCSRQSTDIST – Content Dist Manager – User access. This table is used to authorize user access to the content
PS_MESSAGE_LOG – Message log table
PSPRCSRQSTTEXT – Process Request Text. Contains all long text fields associated with a process request
PS_MESSAGE_LOGPARM – Parameters used for Message log
PSPRCSRQSTXFER – Process Request – Transfer. This record contains the panel transfer keys for a process request that has a panel transfer in it’s definition.
PS_CDM_LIST – Content Distribution Manager – Content List. This record is used by Content Distribution Manager to sore the content list that are sent to the content repository.
PS_PRCSDEFNLANG – Related language for all process definitions
PS_PRCSDEFNXFER – Process definition – Panel transfers. Used to setup information related to allow users to transfer to a desired page upon successful completion of the program from Process Monitor
PS_PRCSDEFNNOTIFY – Process definition – notifications. The table contains information on all the users or roles to be notified based on the process status
PS_PRCSDEFNCNTDIST – Process definition – Distribution. The table contains list of users or roles authorized to view all generated reports and/or logs for the process
PS_PRCSDEFNMESSAGE – Process definition messages
PS_PRCSDEFNMETA – used to sore any metastring for JCL in OS 390.

sql exec stored procedure

Posted on January 5, 2022January 5, 2022 by JointLantic

oracle sql exec stored procedure

This syntax sql for execute store procedure in oracle

Procedure Name: JS_WS_GET_ABS_TYPE

create or replace procedure "JS_WS_GET_ABS_TYPE"
(p_EMPID IN VARCHAR2
 pcursor OUT SYS_REFCURSOR
)
AS
.....

create or replace procedure "JS_WS_GET_ABS_TYPE"

(p_EMPID IN VARCHAR2

pcursor OUT SYS_REFCURSOR

)

.....

How to execute prodecure and print output in sql

Set serveroutput on;

DECLARE
v_cur SYS_REFCURSOR;
v_1 VARCHAR2(254);
v_2 VARCHAR2(254);
v_3 VARCHAR2(254);
v_4 VARCHAR2(254);
v_5 VARCHAR2(254);
v_6 VARCHAR2(254);
v_7 VARCHAR2(254);
v_8 VARCHAR2(254);
v_9 VARCHAR2(254);
v_10 VARCHAR2(254);
BEGIN
JS_WS_GET_ABS_TYPE('0003',v_cur);

LOOP
FETCH v_cur INTO v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10;
EXIT WHEN v_cur%NOTFOUND;
dbms_output.put_line(v_1 || ', ' || v_2 || ', ' || v_3 || ', ' || v_4 || ', ' || v_5 || ', ' || v_6 
|| ', ' || v_7 || ', ' || v_8 || ', ' || v_9 || ', ' || v_10);
END LOOP;
CLOSE v_cur;
END;

Set serveroutput on;

DECLARE

v_cur SYS_REFCURSOR;

v_1 VARCHAR2(254);

v_2 VARCHAR2(254);

v_3 VARCHAR2(254);

v_4 VARCHAR2(254);

v_5 VARCHAR2(254);

v_6 VARCHAR2(254);

v_7 VARCHAR2(254);

v_8 VARCHAR2(254);

v_9 VARCHAR2(254);

v_10 VARCHAR2(254);

BEGIN

JS_WS_GET_ABS_TYPE('0003',v_cur);

LOOP

FETCH v_cur INTO v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10;

EXIT WHEN v_cur%NOTFOUND;

dbms_output.put_line(v_1 || ', ' || v_2 || ', ' || v_3 || ', ' || v_4 || ', ' || v_5 || ', ' || v_6

|| ', ' || v_7 || ', ' || v_8 || ', ' || v_9 || ', ' || v_10);

END LOOP;

CLOSE v_cur;

END;

Syntax sql for select store procedure in oracle

SELECT Text FROM User_Source WHERE Name ='JS_WS_GET_ABS_TYPE' ORDER BY Line

1	SELECT Text FROM User_Source WHERE Name ='JS_WS_GET_ABS_TYPE' ORDER BY Line

How to display BLOB image in XML Publisher

Posted on January 5, 2022January 5, 2022 by JointLantic

How to display BLOB image in XML BI Publisher

BI Publisher – Retrieving images from BLOB data in RFT template

If results XML contains image data that had been stored as a BLOB in the database, then use the following syntax in a form field inserted in the template where you want the image to render at runtime:

Syntax:

&lt;fo:instream-foreign-object content-type="<span class="italic">image/jpg</span>"&gt;
&lt;xsl:value-of select=".//<span class="italic">IMAGE_ELEMENT</span>"/&gt;
&lt;/fo:instream-foreign-object&gt;

<fo:instream-foreign-object content-type="<span class="italic">image/jpg</span>">

<xsl:value-of select=".//<span class="italic">IMAGE_ELEMENT</span>"/>

</fo:instream-foreign-object>

Example:

<fo:instream-foreign-object content-type=”image/jpg”>
<xsl:value-of select=”.//B.SIGN_TT_IMAGE”/>
</fo:instream-foreign-object>

display BLOB image in XML BI Publisher

Reference 1 :https://www.youtube.com/watch?v=5lUBMxx0pd0
Reference 2 :https://docs.oracle.com/middleware/11119/bip/BIPRD/create_rtf_tmpl.htm#iamge_and_chart