Home » Other » Client Tools » Loop Select Statement (PL/SQL)  () 1 Vote
Loop Select Statement [message #446746] Wed, 10 March 2010 04:26 Go to next message
cplib
Messages: 15
Registered: March 2010
Junior Member
Hi All

I have a question.

I have this following select statement

Select name
from names_list
where name like ('A%')


The output returns a list of names beginning with 'A'

What I want is for the output to repeat that list of names multiple times e.g. if the only two names returned are 'Andrews' and 'Apple', I want to have the output show

Andrews
Andrews
Andrews
Apple
Apple
Apple

Hope I have been clear in my requirement, any help greatly appreciated!
Re: Loop Select Statement [message #446749 is a reply to message #446746] Wed, 10 March 2010 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
See this thread: How to get rows N times in a result set?
Re: Loop Select Statement [message #446753 is a reply to message #446746] Wed, 10 March 2010 04:53 Go to previous messageGo to next message
cplib
Messages: 15
Registered: March 2010
Junior Member
Hi

thanks for the speedy reply!

Applying the logic from that thread to my statement:

WITH lines AS (	 SELECT	 LEVEL line
							FROM	 DUAL
					CONNECT BY	 LEVEL <= 5)
(SELECT	a.season || '/' || a.sty_num || '/' || a.sty_qual, a.sty_ret_type,
			a.sty_name, b.bf_mat_char_val, d.sty_tprice, c.wsty_size,
			c.lsty_size, c.sty_barcode
  FROM	pro.sty_defs a, pro.sty_colours b, pro.vsty_barcodes c,
			pro.sty_tprices d, lines
 WHERE		 a.season = b.season
			AND a.sty_num = b.sty_num
			AND a.sty_qual = b.sty_qual
			AND a.sty_code = c.sty_id
			AND b.bf_mat_char_val = c.bf_mat_char_val
			AND a.season = d.season
			AND a.sty_num = d.sty_num
			AND a.sty_qual = d.sty_qual
			AND d.price_list = '0952'
			AND a.sty_ret_type = '499NEWTRENDS'
			AND a.sty_num = 'REVI'
			AND c.sty_barcode IS NOT NULL)


I get the following error message "ORA 24374 - define not done before fetch or execute and fetch"
Re: Loop Select Statement [message #446756 is a reply to message #446746] Wed, 10 March 2010 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
24374, 00000, "define not done before fetch or execute and fetch"
// *Cause:  The application did not define output variables for data being
//          fetched before issuing a fetch call or invoking a fetch by
//          specifying a non-zero row count in an execute call.
// *Action: Issue OCI define calls for the columns to be fetched.


The problem isn't the select it's the way you're using it in the rest of your code.
Re: Loop Select Statement [message #446760 is a reply to message #446756] Wed, 10 March 2010 05:13 Go to previous messageGo to next message
cplib
Messages: 15
Registered: March 2010
Junior Member
cookiemonster wrote on Wed, 10 March 2010 12:02
24374, 00000, "define not done before fetch or execute and fetch"
// *Cause:  The application did not define output variables for data being
//          fetched before issuing a fetch call or invoking a fetch by
//          specifying a non-zero row count in an execute call.
// *Action: Issue OCI define calls for the columns to be fetched.


The problem isn't the select it's the way you're using it in the rest of your code.


That is the extent of my code though.

The select statement

SELECT    a.season || '/' || a.sty_num || '/' || a.sty_qual, a.sty_ret_type,
            a.sty_name, b.bf_mat_char_val, d.sty_tprice, c.wsty_size,
            c.lsty_size, c.sty_barcode
  FROM    pro.sty_defs a, pro.sty_colours b, pro.vsty_barcodes c,
            pro.sty_tprices d
 WHERE         a.season = b.season
            AND a.sty_num = b.sty_num
            AND a.sty_qual = b.sty_qual
            AND a.sty_code = c.sty_id
            AND b.bf_mat_char_val = c.bf_mat_char_val
            AND a.season = d.season
            AND a.sty_num = d.sty_num
            AND a.sty_qual = d.sty_qual
            AND d.price_list = '0952'
            AND a.sty_ret_type = '499NEWTRENDS'
            AND a.sty_num = 'REVI'
            AND c.sty_barcode IS NOT NULL


works correctly and returns 35 records. what I want to do is add to this so that I get n * 35 records.
Re: Loop Select Statement [message #446763 is a reply to message #446746] Wed, 10 March 2010 05:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the error is an OCI one. I don't know much about OCI, don't use it, but it's complaining about the variables used to store the results of the query as far as I can tell.
It's possible your OCI doesn't like the with clause. Try creating a view with the new query and then select from that in the OCI.
Re: Loop Select Statement [message #446767 is a reply to message #446763] Wed, 10 March 2010 05:48 Go to previous messageGo to next message
cplib
Messages: 15
Registered: March 2010
Junior Member
cookiemonster wrote on Wed, 10 March 2010 12:20
Well the error is an OCI one. I don't know much about OCI, don't use it, but it's complaining about the variables used to store the results of the query as far as I can tell.
It's possible your OCI doesn't like the with clause. Try creating a view with the new query and then select from that in the OCI.


ok I got it. Am using Toad and seemingly it requires a with caluse to be used this way
SELECT	*
  FROM	(WITH lines AS (	  SELECT   LEVEL line
										 FROM   DUAL
								 CONNECT BY   LEVEL <= 5)
			 (SELECT   a.season || '/' || a.sty_num || '/' || a.sty_qual,
						  a.sty_ret_type, a.sty_name, b.bf_mat_char_val,
						  d.sty_tprice, c.wsty_size, c.lsty_size, c.sty_barcode
				 FROM   pro.sty_defs a, pro.sty_colours b, pro.vsty_barcodes c,
						  pro.sty_tprices d, lines
				WHERE 		a.season = b.season
						  AND a.sty_num = b.sty_num
						  AND a.sty_qual = b.sty_qual
						  AND a.sty_code = c.sty_id
						  AND b.bf_mat_char_val = c.bf_mat_char_val
						  AND a.season = d.season
						  AND a.sty_num = d.sty_num
						  AND a.sty_qual = d.sty_qual
						  AND d.price_list = '0952'
						  AND a.sty_ret_type = '499NEWTRENDS'
						  AND a.sty_num = 'REVI'
						  AND c.sty_barcode IS NOT NULL))


Re: Loop Select Statement [message #446770 is a reply to message #446746] Wed, 10 March 2010 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you running an old version of TOAD? It ought to be able to handle that.
Re: Loop Select Statement [message #446773 is a reply to message #446770] Wed, 10 March 2010 06:08 Go to previous message
cplib
Messages: 15
Registered: March 2010
Junior Member
cookiemonster wrote on Wed, 10 March 2010 12:57
Are you running an old version of TOAD? It ought to be able to handle that.


Am running 9.7.2.5
Previous Topic: DIM-00020
Next Topic: ORA-01031 and ORA-01034
Goto Forum:
  


Current Time: Fri Mar 29 07:11:55 CDT 2024