How to use SELECT INTO

Newbie_Don · June 2, 2015, 11:23am

Hi group,

I’m in the process of creating a script. I believe I need to DECLARE some variables as NUMBERs as that is what I need their output to be. Eventually I’m going to do some math with these numbers. However when attempting to run my script I’m getting an error message that says

ORA-06550: line 8, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

I’ve done a fair amount of research to find the correct syntax to use the SELECT INTO clause. However I’ve not found it yet. Here’s the original script as I’ve written it:

DECLARE Period_Out_Month_Number NUMBER;
Period_Out_Year_Number NUMBER;
Open_Date_Month_Number NUMBER;
Open_Date_Year_Number NUMBER;

BEGIN

Select Distinct b.prop_master_id,
a.periods_out_desc,
b.Open_Date,

Case
When (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Feb’ then 2
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Mar’ then 3
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Apr’ then 4
when (SUBSTR(a.periods_out_desc,0,3)) in ‘May’ then 5
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jun’ then 6
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jul’ then 7
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Aug’ then 8
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Sep’ then 9
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Oct’ then 10
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Nov’ then 11
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Dec’ then 12

end as Period_Out_Month_Number,

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number,
EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number,
EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number

from dmart.F_ST_PERIOD_WKLY a,
crmmart.d_prop b

where b.prop_master_id in (‘123’) and
(SUBSTR(a.periods_out_desc,-4,4) in (‘2008’, ‘2009’, ‘2010’, ‘2011’, ‘2012’, ‘2013’, ‘2014’, ‘2015’)) and
(SUBSTR(a.periods_out_desc,0,3) in (‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’, ‘May’, ‘Jun’, ‘Jul’, ‘Aug’, ‘Sep’, ‘Oct’, ‘Nov’, ‘Dec’))

ORDER BY b. prop_master_id,
a.periods_out_desc;

End;

My questions are:

how do I property use the SELECT INTO clause?
what is the correct syntax and where to I insert the “INTO”?

In advance, thanks for any and all help.

Don

mstaszew · June 2, 2015, 11:28am

The basic structure is…

select fields into vars from table;

Here is a simple working example…

declare

number;

begin

select
count(*)
into n
from user_tables;

end;

You can do…

select field1, field2 into var1, var2 from table;

You’ll need to declare vars for your selected fields and add them as shown in the above example.

Michael

From: Newbie_Don [mailto:bounce-Newbie_Don@toadworld.com]

Sent: Tuesday, June 02, 2015 2:24 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] How to use SELECT INTO

How to use SELECT INTO

Thread created by Newbie_Don

Hi group,

I’m in the process of creating a script. I believe I need to DECLARE some variables as NUMBERs as that is what I need their output to be. Eventually I’m going to do some math with these numbers.
However when attempting to run my script I’m getting an error message that says

ORA-06550: line 8, column 1:

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

I’ve done a fair amount of research to find the correct syntax to use the SELECT INTO clause. However I’ve not found it yet. Here’s the original script as I’ve written it:

DECLARE Period_Out_Month_Number NUMBER;

Period_Out_Year_Number NUMBER;

Open_Date_Month_Number NUMBER;

Open_Date_Year_Number NUMBER;

BEGIN

Select Distinct b.prop_master_id,

a.periods_out_desc,

b.Open_Date,

Case

When (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Feb’ then 2

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Mar’ then 3

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Apr’ then 4

when (SUBSTR(a.periods_out_desc,0,3)) in ‘May’ then 5

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jun’ then 6

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jul’ then 7

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Aug’ then 8

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Sep’ then 9

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Oct’ then 10

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Nov’ then 11

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Dec’ then 12

end as Period_Out_Month_Number,

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number,

EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number,

EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number

from dmart.F_ST_PERIOD_WKLY a,

crmmart.d_prop b

where b.prop_master_id in (‘123’) and

(SUBSTR(a.periods_out_desc,-4,4) in (‘2008’, ‘2009’, ‘2010’, ‘2011’, ‘2012’, ‘2013’, ‘2014’, ‘2015’)) and

(SUBSTR(a.periods_out_desc,0,3) in (‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’, ‘May’, ‘Jun’, ‘Jul’, ‘Aug’, ‘Sep’, ‘Oct’, ‘Nov’, ‘Dec’))

ORDER BY b. prop_master_id,

a.periods_out_desc;

End;

My questions are:

how do I property use the SELECT INTO clause?
what is the correct syntax and where to I insert the “INTO”?

In advance, thanks for any and all help.

Don

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Newbie_Don · June 2, 2015, 11:56am

Michael, thanks for your reply. Unfortunately my experience won’t allow me to translate what your teaching me into how to fix my issue.

I believe I have the variables declared:

DECLARE Period_Out_Month_Number NUMBER;

Period_Out_Year_Number NUMBER;

Open_Date_Month_Number NUMBER;

Open_Date_Year_Number NUMBER;

Now I am selecting multiple fields (one of which includes a CASE statement). These are as follows:

SELECT DISTINCT

b.prop_master_id,

a.periods_out_desc,

b.Open_Date,

CASE when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Feb’ then 2

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Mar’ then 3

– and on and on until December…

END as Period_Out_Month_Number, – This is my first variable.

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number, – my second variable

EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number, – my third variable

EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number – my forth variable.

FROM – blah blah blah…

Where and how do I use “INTO” with this?

phyllis.helton_1150 · June 2, 2015, 12:02pm

You put INTO columns after all the SELECT columns and before FROM

On Tue, Jun 2, 2015 at 2:56 PM, Newbie_Don bounce-Newbie_Don@toadworld.com wrote:

RE: How to use SELECT INTO

Reply by Newbie_Don
Michael, thanks for your reply. Unfortunately my experience won't allow me to translate what your teaching me into how to fix my issue.

I believe I have the variables declared:

DECLARE Period_Out_Month_Number NUMBER;

Period_Out_Year_Number NUMBER;

Open_Date_Month_Number NUMBER;

Open_Date_Year_Number NUMBER;

Now I am selecting multiple fields (one of which includes a CASE statement). These are as follows:

SELECT DISTINCT

b.prop_master_id,

a.periods_out_desc,

b.Open_Date,

CASE when (SUBSTR(a.periods_out_desc,0,3)) in 'Jan' then 1

when (SUBSTR(a.periods_out_desc,0,3)) in 'Feb' then 2

when (SUBSTR(a.periods_out_desc,0,3)) in 'Mar' then 3

-- and on and on until December.....

END as Period_Out_Month_Number, -- This is my first variable.

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number, -- my second variable

EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number, -- my third variable

EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number -- my forth variable.

FROM -- blah blah blah....

Where and how do I use "INTO" with this?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician
Digital Strategies, Cru | Data Sciences & Analytics
Office 407-515-4452

phyllis.helton@cru.org

mstaszew · June 2, 2015, 12:09pm

Tweaks in bold. My prior email didn’t come through so if you see it, ignore it. I forgot a couple of changes you need.

SELECT DISTINCT

b.prop_master_id, – Need a var for this

a.periods_out_desc, – Need a var for this

b.Open_Date, – Need a var for this

CASE when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Feb’ then 2

when (SUBSTR(a.periods_out_desc,0,3)) in ‘Mar’ then 3

– and on and on until December…

END as Period_Out_Month_Number, – This is my first fourth variable.

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number, – my second fifth variable

EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number, – my third sixth variable

EXTRACT(YEAR FROM b.Open_Date**) Open_Date_Year_Number – my forth seventh variable**

into var1, var2, var3, var4, var5, var6, var7

from blah-blah-blah

rjesse · June 2, 2015, 12:18pm

Hey Don,

Like Michael and Phyllis said, the generic form is:

SELECT
CASE WHEN stuff happens END column_alias1,
SUBSTR(other stuff) column_alias2,
EXTRACT(blah blah blah) column_alias3
INTO
declared_variable1,
declared_variable2,
declared_variable3
FROM
some tables…

The “column_aliasN” text isn’t needed, except for your own documentation. The first column in the SELECT will be put into the first variable in the INTO clause, the second to the second, etc.

Also, for performance, you may want to consider changing your CASE from:

CASE when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Feb’ then 2
when (SUBSTR(a.periods_out_desc,0,3)) in ‘Mar’ then 3

…to…

CASE SUBSTR(a.periods_out_desc,0,3)
when ‘Jan’ then 1
when ‘Feb’ then 2
when ‘Mar’ then 3

You should be able to see that there could be a lot less SUBSTRing going on in the second CASE statement.

Just a thought. Good luck!

Rich

mstaszew · June 2, 2015, 12:36pm

Additions/changes in bold. Your SQL must return only one row for this to work.

SELECT DISTINCT

                    b.prop_master_id,

– Need a var for this

                    a.periods_out_desc,

– Need a var for this

                    b.Open_Date,

– Need a var for this

CASE when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1

                    when (SUBSTR(a.periods_out_desc,0,3)) in 'Feb'           then 2

                    when (SUBSTR(a.periods_out_desc,0,3)) in 'Mar'           then 3

– and on and on until December…

END as Period_Out_Month_Number, – This is my
~~first~~ fourth variable.

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number, – my
~~second~~ fifth variable

EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number, – my
~~third~~ sixth variable

EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number – my
~~forth~~ seventh variable

into var1, var2, var3, var4, var5, var6, var7

from blah-blah-blah

From: Newbie_Don [mailto:bounce-Newbie_Don@toadworld.com]

Sent: Tuesday, June 02, 2015 2:56 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to use SELECT INTO

RE: How to use SELECT INTO

Reply by Newbie_Don

Michael, thanks for your reply. Unfortunately my experience won’t allow me to translate what your teaching me into how to fix my issue.

I believe I have the variables declared:

DECLARE Period_Out_Month_Number NUMBER;

                    Period_Out_Year_Number       NUMBER;

                    Open_Date_Month_Number    NUMBER;

                    Open_Date_Year_Number       NUMBER;

Now I am selecting multiple fields (one of which includes a CASE statement). These are as follows:

SELECT DISTINCT

                    b.prop_master_id,

                    a.periods_out_desc,

                    b.Open_Date,

CASE when (SUBSTR(a.periods_out_desc,0,3)) in ‘Jan’ then 1

                    when (SUBSTR(a.periods_out_desc,0,3)) in 'Feb'           then 2

                    when (SUBSTR(a.periods_out_desc,0,3)) in 'Mar'           then 3

– and on and on until December…

END as Period_Out_Month_Number, – This is my first variable.

(SUBSTR(a.periods_out_desc,-4,4)) Period_Out_Year_Number, – my second variable

EXTRACT(MONTH FROM b.Open_Date) Open_Date_Month_Number, – my third variable

EXTRACT(YEAR FROM b.Open_Date) Open_Date_Year_Number – my forth variable.

FROM – blah blah blah…

Where and how do I use “INTO” with this?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.