| Loop Select Statement [message #446746] |
Wed, 10 March 2010 04:26  |
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 #446753 is a reply to message #446746] |
Wed, 10 March 2010 04:53  |
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 |
cookiemonster
Messages: 13961
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 |
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 |
cookiemonster
Messages: 13961
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 |
cplib
Messages: 15
Registered: March 2010
|
Junior Member |
|
|
cookiemonster wrote on Wed, 10 March 2010 12:20Well 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))
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
