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 next 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
Re: pl sql block INTO clause is expected in this SELECT statement [message #657391 is a reply to message #657390] Mon, 07 November 2016 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 25742
Registered: January 2009
Location: SoCal
Senior Member
PLS-00428: an INTO clause is expected in this SELECT statement.

when SELECT is issued from PL/SQL it must include INTO clause
Re: pl sql block INTO clause is expected in this SELECT statement [message #657392 is a reply to message #657391] Mon, 07 November 2016 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So remove
BEGIN
SET TRANSACTION READ ONLY;
and
END;
And this will be fine.
Re: pl sql block INTO clause is expected in this SELECT statement [message #657393 is a reply to message #657391] Mon, 07 November 2016 14:40 Go to previous messageGo to next message
Developer9
Messages: 6
Registered: June 2015
Junior Member
@BlackSwan,

So for my PL/SQL code ..Do I need to declare the variables first to use the insert into clause for all the select statements ?

Can you please provide me some example with the correction in my code so that I will change for the rest of the code.

Thanks
Re: pl sql block INTO clause is expected in this SELECT statement [message #657394 is a reply to message #657393] Mon, 07 November 2016 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes you need it, and the way to do it depends on the client program, do you want us to move the topic to Peoplesoft forum?

Re: pl sql block INTO clause is expected in this SELECT statement [message #657395 is a reply to message #657393] Mon, 07 November 2016 14:52 Go to previous messageGo to next message
BlackSwan
Messages: 25742
Registered: January 2009
Location: SoCal
Senior Member
Developer9 wrote on Mon, 07 November 2016 12:40
@BlackSwan,

So for my PL/SQL code ..Do I need to declare the variables first to use the insert into clause for all the select statements ?

Can you please provide me some example with the correction in my code so that I will change for the rest of the code.

Thanks
unwilling or incapable to use GOOGLE yourself?

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+pl+sql+select+into+example
Re: pl sql block INTO clause is expected in this SELECT statement [message #657396 is a reply to message #657392] Mon, 07 November 2016 14:54 Go to previous messageGo to next message
Developer9
Messages: 6
Registered: June 2015
Junior Member
@Michel,

Removed "SET TRANSACTION READ ONLY; AND END; "
 is of incorrect type. The required statement type is: PL/SQL. (CC_OraStatement::verifyStatementType, file CC_OraStatement.cpp, line 4,817)

Thanks
Re: pl sql block INTO clause is expected in this SELECT statement [message #657397 is a reply to message #657395] Mon, 07 November 2016 14:56 Go to previous messageGo to next message
Developer9
Messages: 6
Registered: June 2015
Junior Member
@Blackswan,

Sorry ..Will try first before asking for suggestions on the code
Re: pl sql block INTO clause is expected in this SELECT statement [message #657398 is a reply to message #657397] Mon, 07 November 2016 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 25742
Registered: January 2009
Location: SoCal
Senior Member
First make it work, then make it fancy.

DECLARE
RESULTS NUMBER;
BEGIN
SELECT COUNT(*) INTO RESULTS FROM USER_OBJECTS;
END;

can you make above work in your environment?
If "YES", then you should substitute in your SELECT statement & work from there.
Re: pl sql block INTO clause is expected in this SELECT statement [message #657399 is a reply to message #657394] Mon, 07 November 2016 15:44 Go to previous messageGo to next message
Developer9
Messages: 6
Registered: June 2015
Junior Member
@Michel

Please move the topic to Peoplesoft forum.Thanks
Re: pl sql block INTO clause is expected in this SELECT statement [message #657400 is a reply to message #657399] Mon, 07 November 2016 15:52 Go to previous messageGo to next message
joy_division
Messages: 4800
Registered: February 2005
Location: East Coast USA
Senior Member
I moved this to the Peoplesoft Forum for you, but as you can see, it gets very little action here. Maybe it should go back? Someone smarter than me should decide.
Re: pl sql block INTO clause is expected in this SELECT statement [message #657403 is a reply to message #657400] Tue, 08 November 2016 00:02 Go to previous message
Littlefoot
Messages: 21265
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regulars (at least, the ones I know) check all forums so they will see this discussion (and, hopefully, participate). The others aren't interested anyway.
Previous Topic: Monthy yearly Comparison Report (RDA) JDE
Goto Forum:
  


Current Time: Tue Dec 12 09:29:05 CST 2017

Total time taken to generate the page: 0.01397 seconds