Home » Applications » PeopleSoft, JD Edwards & Siebel » pl sql block INTO clause is expected in this SELECT statement (Oracle Peoplesoft v9.2)
pl sql block INTO clause is expected in this SELECT statement [message #657390] Mon, 07 November 2016 14:24 Go to previous message
Developer9
Messages: 6
Registered: June 2015
Junior Member
Hi ,

I am executing PL/SQL blocks using DataStage through ODBC connection stage getting below error when job executes this PL/SQL .

PL/SQL block
BEGIN
SET TRANSACTION READ ONLY;
WITH    pjb
(
        emplid
,       empl_rcd
,       paygroup
)
AS
(
SELECT
        emplid
,       empl_rcd
,       paygroup
FROM    (
        SELECT
                j.emplid
        ,       j.empl_rcd
        ,       j.paygroup
        ,       RANK() OVER (PARTITION BY j.emplid
                             ORDER BY     j.effdt       DESC
                                        , j.effseq      DESC
                                        , j.empl_rcd    ASC
                                        , jc.effdt      DESC
                                        , l.effdt       DESC) AS rank_id
        FROM    sysadm.ps_job j
        INNER JOIN sysadm.ps_location_tbl l
        ON      l.location  = j.location
        AND     l.effdt    <= j.effdt
        INNER JOIN sysadm.ps_s_location_tbl sl
        ON      sl.location = l.location
        AND     sl.setid    = l.setid
        AND     sl.effdt    = l.effdt
        INNER JOIN sysadm.ps_jobcode_tbl jc
        ON      jc.jobcode  = j.jobcode
        AND     jc.effdt   <= j.effdt
        WHERE   j.paygroup        IN ('004','005')
        AND    (j.empl_status     IN ('A','L','P','S')
        OR     (j.empl_status NOT IN ('A','L','P','S')
        AND     j.action_dt       >= (SELECT SYSDATE - 28 FROM DUAL)))
        AND     jc.job_function   IN ('RTL','RX')
        AND     jc.job_family NOT IN ('DISMGR')
        AND     sl.s_fac_type     IN ('RT','OF')
        AND     j.effdt           <= SYSDATE
        ) x
WHERE   rank_id = 1
)
SELECT  /*+PARALLEL*/
        a.emplid
,       a.paygroup
,       a.s_am_daily_hrs
,       a.abs_type_optn
,       a.pay_end_dt
,       b.calc_rslt_val
FROM    (
        SELECT
                emplid
        ,       paygroup
        ,       s_am_daily_hrs
        ,       abs_type_optn
        ,       pay_end_dt
        FROM    (
                SELECT
                        ja.emplid
                ,       ja.paygroup
                ,       ah.s_am_daily_hrs
                ,       ah.abs_type_optn
                ,       ah.pay_end_dt
                ,       RANK() OVER (PARTITION BY ja.emplid ORDER BY ah.pay_end_dt DESC) AS rank_id
                FROM     pjb ja
                INNER JOIN sysadm.ps_s_am_calc_hours ah
                ON      ah.emplid   = ja.emplid
                AND     ah.empl_rcd = ja.empl_rcd
                WHERE   ah.abs_type_optn IN ('VAC','FLT','BIR','ANN','PTO')
                ) x
        WHERE   rank_id = 1
        ) a
INNER JOIN 
        (
        SELECT
                jb.emplid
        ,       acm.calc_rslt_val
        FROM    pjb jb
        INNER JOIN sysadm.ps_gp_pye_prc_stat prc 
        ON      jb.emplid           = prc.emplid
        AND     jb.empl_rcd         = prc.empl_rcd
        INNER JOIN sysadm.ps_gp_pye_seg_stat seg 
        ON      prc.emplid          = seg.emplid
        AND     prc.empl_rcd        = seg.empl_rcd
        AND     prc.cal_run_id      = seg.cal_run_id
        AND     prc.gp_paygroup     = seg.gp_paygroup
        AND     prc.cal_id          = seg.cal_id
        AND     prc.orig_cal_run_id = seg.orig_cal_run_id
        INNER JOIN sysadm.ps_gp_rslt_acum acm 
        ON      seg.emplid          = acm.emplid
        AND     seg.cal_run_id      = acm.cal_run_id
        AND     seg.empl_rcd        = acm.empl_rcd
        AND     seg.gp_paygroup     = acm.gp_paygroup
        AND     seg.cal_id          = acm.cal_id
        AND     seg.orig_cal_run_id = acm.orig_cal_run_id
        AND     seg.rslt_seg_num    = acm.rslt_seg_num
        AND    (acm.empl_rcd        = acm.empl_rcd_acum
        OR      acm.acm_type        = 'N')
        INNER JOIN sysadm.ps_gp_pin_cmpnt cmp 
        ON      acm.pin_num         = cmp.pin_entl_bal_num
        INNER JOIN sysadm.ps_gp_pin pin 
        ON      cmp.pin_num         = pin.pin_num
        WHERE   prc.prc_ord_ts =
                            (
                            SELECT  MAX(prc1.prc_ord_ts)
                            FROM    sysadm.ps_gp_pye_prc_stat prc1
                            ,       sysadm.ps_gp_cal_run cal
                            WHERE   prc1.calc_type         = 'A'
                            AND     prc1.emplid            = prc.emplid
                            AND     prc1.empl_rcd          = prc.empl_rcd
                            AND     prc1.cal_run_id        = cal.cal_run_id
                            AND     cal.run_finalized_ind = 'Y'
                            )
        AND     prc.rslt_ver_num =
                            (
                            SELECT  MAX(prc2.rslt_ver_num)
                            FROM    sysadm.ps_gp_pye_prc_stat prc2
                            WHERE   prc2.emplid          = prc.emplid
                            AND     prc2.empl_rcd        = prc.empl_rcd
                            AND     prc2.gp_paygroup     = prc.gp_paygroup
                            AND     prc2.cal_id          = prc.cal_id
                            AND     prc2.orig_cal_run_id = prc.orig_cal_run_id
                            )
        AND     seg.rslt_seg_num =
                            (
                            SELECT  MAX(seg1.rslt_seg_num)
                            FROM    sysadm.ps_gp_pye_seg_stat seg1
                            WHERE   seg1.emplid          = seg.emplid
                            AND     seg1.empl_rcd        = seg.empl_rcd
                            AND     seg1.cal_run_id      = seg.cal_run_id
                            AND     seg1.gp_paygroup     = seg.gp_paygroup
                            AND     seg1.cal_id          = seg.cal_id
                            AND     seg1.orig_cal_run_id = seg.orig_cal_run_id
                            )
        AND     pin.pin_type        = 'AE'
        AND     pin.pin_nm         IN ('S VAC AE', 'S VRO AE','S FHOL AE','S BDAY AE','S ANIV AE','S PTO AE')
        ) b
ON      a.emplid = b.emplid;
END;

Error Message

The OCI function OCIStmtExecute returned status -1. Error code: 6,550, Error message: ORA-06550: line 3, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement. (CC_OraStatement::executePlSql, file CC_OraStatement.cpp, line 3,769)

Please advise me the corrections to the above code.

Thanks
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Monthy yearly Comparison Report (RDA) JDE
Next Topic: Siebel management agent not starting
Goto Forum:
  


Current Time: Fri Apr 19 05:47:51 CDT 2024