| PL/SQL or Perl? [message #39043] |
Mon, 10 June 2002 04:12  |
pvdalen
Messages: 14
Registered: April 2002
|
Junior Member |
|
|
Hi all,
I have the following query:
SELECT col1, col2, col3 FROM table1 WHERE col2 IS NOT NULL and col3 IN
(SELECT col3 from table1 WHERE col2 IS NULL and col4 LIKE 'XX%') GROUP BY col1, col2, col3
What that does, essentially, is bring back a list of rows whose col2 value is not null, but whose associated rows (linked by col3 in the subquery) has a null value for col2.
The question I have is this: Along with the three values generated by the main query, I want to return a column value from the subquery (call it 'col4'). To do so, I could use a Perl script to assign variables and return what I need, but that would entail a separate SQL string and call to the database, which I'd like to avoid as the table is HUGE. I don't know PL/SQL at all; is there a function that will allow me to set a variable to hold the value of col4 while, perhaps during the subquery, allowing me to process the SQL string with as normal?
Thanks very much.
|
|
|
|
| Re: PL/SQL or Perl? [message #39053 is a reply to message #39043] |
Mon, 10 June 2002 10:52  |
Susan
Messages: 102
Registered: October 2001
|
Senior Member |
|
|
Not exactly sure what you're looking for (if col4 is multi-valued for values of col3 in the subquery, maybe not), but is this query rewrite getting at your issue?
select a.col1, a.col2, b.col3, b.col4
from
(select distinct col1, col2, col3
from table1
where col2 is not null) a,
(select distinct col3, col4
from table1
where col2 is null
and col4 like 'XX%') b
where a.col3 = b.col3
Since you're table is large, using a join may also be a more efficient way of pulling the data you need (depending on your data and any indexes)... also, if col3 is all or part of an index, using "is not null" will not use the index (better to do a >0, or some other thing)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
