CODEWITHSUNDEEP
sqloracle-databaseselectloopsfor-loop
nvrmore100
nvrmore100

Reputation: 27

Oracle SQL select loop based on other select

I would like to perform a series of select statements based on the values returned from an initial select statement. Essentially loop through the original values and using the value as a criteria for a new select.

Some pseudo code of what I am trying to do (how I would write this in shell...):

for location in `select places from tablename where XYZ`
do
   select new_field from tablename where location = '$location';
done

Here are the selects I really want to run.

Get me a list of Racks:

select   regexp_substr("MYTABLE"."Serial_Number" ,'[^ ]+', 1, 3) as "Racks"
  from   "MYTABLE" "MYTABLE" 
 where   "MYTABLE"."Data_Center" ='SOMEPLACE' 
   and   "MYTABLE"."Device_Type" ='RACK'
   and   "MYTABLE"."Serial_Number" not like '%WAREHOUSE%'

Print who should own the rack based on count of devices:

select count(*) as count, LOB 
 from    "MYTABLE" "MYTABLE" 
 where   "MYTABLE"."Data_Center" ='SOMEPLACE' 
   and   GRID_LOCATION = '$RACK_from_above' and rownum <= 1 group by LOB order by count desc;

Thanks in advance!

Upvotes: 0

Views: 3528

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can readily combine these into a single query:

with thelist as (
    select   regexp_substr("MYTABLE"."Serial_Number" ,'[^ ]+', 1, 3) as "Racks"
      from   "MYTABLE" "MYTABLE" 
     where   "MYTABLE"."Data_Center" ='SOMEPLACE' 
       and   "MYTABLE"."Device_Type" ='RACK'
       and   "MYTABLE"."Serial_Number" not like '%WAREHOUSE%'
)
select count(*) as count, LOB 
from    "MYTABLE" "MYTABLE" 
where   "MYTABLE"."Data_Center" ='SOMEPLACE' and
      GRID_LOCATION in (select racks from thelist) and
      rownum <= 1
group by LOB
order by count desc;

Upvotes: 0

DVK
DVK

Reputation: 129383

Simply join the table to itself (using 2 different aliases)

select count(1) as count, MYTABLE1.LOB, MYTABLE1.GRID_LOCATION  
from    "MYTABLE" "MYTABLE1" , "MYTABLE" "MYTABLE2"   
where   MYTABLE1.Data_Center ='SOMEPLACE'    
 and   MYTABLE1.GRID_LOCATION = regexp_substr(MYTABLE2.Serial_Number ,'[^ ]+', 1, 3)
 and   MYTABLE1.rownum <= 1
 and   MYTABLE2.Data_Center ='SOMEPLACE'
 and   MYTABLE2.Device_Type ='RACK'
 and   MYTABLE2.Serial_Number not like '%WAREHOUSE%'
group by MYTABLE1.LOB, MYTABLE1.GRID_LOCATION
order by count desc;

Note that I added grid location to the group by and select clauses for (what seems to me) obvious reasons - if that's unclear you can ask as a separate question.

Upvotes: 2

Related Questions