Home » SQL & PL/SQL » SQL & PL/SQL » Please Help Me with Select ... Into Statement (PLS-00428)
Please Help Me with Select ... Into Statement (PLS-00428) [message #270742] Thu, 27 September 2007 22:59 Go to next message
puriyves
Messages: 5
Registered: September 2007
Location: Indonesia
Junior Member
Hi all, can somebody help me, I try to create Oracle store procedure, the sql statement is working correctly but when I try to convert to procedure it's shown PLS-00428 error message,
Here is the statement :

CREATE OR REPLACE PROCEDURE ITEM_STOCK (
ITEM_CODE IN VARCHAR2,
DATE_NOW IN OUT VARCHAR2
)
AS

BEGIN
SELECT A.I_STOCK_MNTH, B.I_ITEM_CD, B.I_ITEM_DESC,
(SUM(A.I_MNTH_START_QTY+A.I_CUR_IN_QTY+A.I_CUR_ADJST_QTY+A.I_TRN_IN_QTY-I_CUR_OUT_QTY-I_CUR_LOSS_QTY-I_TRN_OUT_QTY-I_ALLOC_QTY))
AS TOT_QTY,
C.I_UNIT_DESC AS UOM, Trunc(((SUM(A.I_MNTH_START_QTY+A.I_CUR_IN_QTY+A.I_CUR_ADJST_QTY+A.I_TRN_IN_QTY-I_CUR_OUT_QTY-I_CUR_LOSS_QTY-I_TRN_OUT_QTY-I_ALLOC_QTY ))/B.I_NET_WEIGHT),0)
AS PACK
FROM T_SL_STOCK_MS A ,T_PM_MS B, T_UNIT_MS C, T_UNIT_MS D, T_DATE_MS E
WHERE A.I_ITEM_CD=B.I_ITEM_CD
AND A.I_STOCK_LOCATION=B.I_SHIP_LOCATION
AND B.I_STD_UNIT_CD=C.I_UNIT_CD
AND B.I_CNV_UNIT_CD=D.I_UNIT_CD
AND B.I_FAC_CD=E.I_FAC_CD
AND A.I_STOCK_MNTH=ITEM_CODE
AND A.I_ITEM_CD=DATE_NOW
GROUP BY A.I_STOCK_LOCATION, A.I_STOCK_MNTH, B.I_ITEM_CD,
B.I_ITEM_DESC, B.I_MODEL, C.I_UNIT_DESC, D.I_UNIT_DESC,
B.I_NET_WEIGHT;

END ITEM_STOCK;

Thank You in Advance
Puri
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270744 is a reply to message #270742] Thu, 27 September 2007 23:07 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
The error message text displayed next to the error and the Cause and Action info. documented in the Error Messages manual are pretty clear.

Quote:
"PLS-00428: an INTO clause is expected in this SELECT statement

Cause: The INTO clause of a SELECT INTO statement was omitted. For example, the code might look like SELECT deptno, dname, loc FROM dept WHERE ... instead of SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE ... In PL/SQL, only a subquery is written without an INTO clause.

Action: Add the required INTO clause."


--
Joe Fuda
SQL Snippets
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270745 is a reply to message #270742] Thu, 27 September 2007 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>it's shown PLS-00428 error message,
I bet you saw more than you reported here.

Please read & follow the posting guidelines at stated in the #1 STICKY post found at top of this forum.

use CUT & PASTE to post the whole session; what you typed in & EXACTLY how SQL*Plus responded.
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270753 is a reply to message #270742] Thu, 27 September 2007 23:31 Go to previous messageGo to next message
puriyves
Messages: 5
Registered: September 2007
Location: Indonesia
Junior Member
Hi anacedent,

here is the full statement :

-------
CREATE OR REPLACE PROCEDURE ITEM_STOCK (
ITEM_CODE IN VARCHAR2,
DATE_NOW IN OUT VARCHAR2
)
AS

BEGIN
SELECT A.I_STOCK_MNTH, B.I_ITEM_CD, B.I_ITEM_DESC,
(SUM(A.I_MNTH_START_QTY+A.I_CUR_IN_QTY+A.I_CUR_ADJST_QTY+A.I_TRN_IN_QTY-I_CUR_OUT_QTY-I_CUR_LOSS_QTY-I_TRN_OUT_QTY-I_ALLOC_QTY))
AS TOT_QTY,
C.I_UNIT_DESC AS UOM,
Trunc(((SUM(A.I_MNTH_START_QTY+A.I_CUR_IN_QTY+A.I_CUR_ADJST_QTY+A.I_TRN_IN_QTY-I_CUR_OUT_QTY-I_CUR_LOSS_QTY-I_TRN_OUT_QTY-I_ALLOC_QTY ))/B.I_NET_WEIGHT),0)
AS PACK
FROM T_SL_STOCK_MS A ,T_PM_MS B, T_UNIT_MS C, T_UNIT_MS D, T_DATE_MS E
WHERE A.I_ITEM_CD=B.I_ITEM_CD
AND A.I_STOCK_LOCATION=B.I_SHIP_LOCATION
AND B.I_STD_UNIT_CD=C.I_UNIT_CD
AND B.I_CNV_UNIT_CD=D.I_UNIT_CD
AND B.I_FAC_CD=E.I_FAC_CD
AND A.I_STOCK_MNTH=ITEM_CODE
AND A.I_ITEM_CD=DATE_NOW
GROUP BY A.I_STOCK_LOCATION, A.I_STOCK_MNTH, B.I_ITEM_CD,
B.I_ITEM_DESC, B.I_MODEL, C.I_UNIT_DESC, D.I_UNIT_DESC,
B.I_NET_WEIGHT;

END ITEM_STOCK;

the ouput is
Line POS Text
1 Create procedure, executed in 0.032s
8 2 PLS-00428: an INTO clause is expected in this select statement

line 8 is: SELECT A.I_STOCK_MNTH, B.I_ITEM_CD, B.I_ITEM_DESC,

but I have no idea where I put INTO statement, since I am novice in PL/SQL

Thank you
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270756 is a reply to message #270742] Thu, 27 September 2007 23:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
but I have no idea where I put INTO statement, since I am novice in PL/SQL

So learn it from seniors, attend any PL/SQL course or at least use the documentation, found eg.online on http://tahiti.oracle.com/.
Open PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Querying Data with PL/SQL
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270757 is a reply to message #270742] Thu, 27 September 2007 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>but I have no idea where I put INTO statement, since I am novice in PL/SQL
Do you know how to Read The Fine PL/SQL REFERENCE Manual as found here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

& apply the knowledge to construct a valid statement?
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270787 is a reply to message #270742] Fri, 28 September 2007 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Please Help Me with Select ... Into Statement (PLS-00428) [message #270816 is a reply to message #270742] Fri, 28 September 2007 01:32 Go to previous message
puriyves
Messages: 5
Registered: September 2007
Location: Indonesia
Junior Member
Thanks, All I got it now
Previous Topic: dollar amount data type
Next Topic: Accessing collection elements without Looping
Goto Forum:
  


Current Time: Wed Dec 07 20:27:20 CST 2016

Total time taken to generate the page: 0.22744 seconds