SUB QUERY

[RottPaws]

I am trying to create a report, but right now I have to pull the information in multiple queries and then put it all together manually. I would like to pull all the info I need in one query, but I basically need to do a query within a query.

Right now I've got one query that pulls a total count of equipment at a specified site and another that gets a count of equipment that is used.

qryTotal:
SELECT DISTINCT COUNT(EQUIP_ID) AS TOTAL,
EQUIP_TYPE,
LOC
FROM EQUIPMENT
WHERE (STATUS = 'IS') AND
(LOC = 'PLACE') AND
(EQUIP_TYPE IN ('TYPE1', 'TYPE2', 'TYPE3'))
GROUP BY EQUIP_TYPE, LOC

output:
TOTAL EQUIP_TYPE LOC
1234 TYPE1 PLACE
2345 TYPE2 PLACE
321 TYPE3 PLACE

qryUsed:
SELECT DISTINCT
COUNT(DISTINCT EQUIPMENT.EQUIP_ID)
AS USED, EQUIPMENT.EQUIP_TYPE,
EQUIPMENT.LOC, EQUIPMENT.LOC_TYPE
FROM CORP.CONNECTIVITY, CORP.CIRC_STAT_HIST,
CORP.EQUIPMENT
WHERE CIRCUITS.CIRC_ID = CIRCUIT_HIST.CIRC_ID
AND
CIRCUITS.EQUIP_ID = EQUIPMENT.EQUIP_ID
AND (EQUIPMENT.LOC = 'PLACE') AND
(EQUIPMENT.EQUIP_TYPE IN ('TYPE1', 'TYPE2', 'TYPE3')) AND
(CORP.EQUIPMENT.EQUIP_STATUS_CD = 'IS') AND
(CIRCUIT_HIST.STATUS = 'IS')
GROUP BY EQUIPMENT.EQUIP_TYPE,
EQUIPMENT.LOC

output:
USED EQUIP_TYPE LOC
1103 TYPE1 PLACE
1542 TYPE2 PLACE
243 TYPE3 PLACE

I'd like to combine the queries into one and get output that looks like:
EQUIP_TYPE LOC TOTAL USED
TYPE1 PLACE 1234 1103
TYPE2 PLACE 2345 1542
TYPE3 PLACE 321 243

Thanks in advance. _________
Rott Paws

 

[Khayyam]

try from clause query.
exp:
select sal, sall from emp,(select sal sall from emp)e2
where empno=222 and emp.empno=e2.empno

 

[RottPaws]

Khayyam,

I wasn't able to get this to work. I ended up pulling the data in 2 separate recordsets and then combining it in a grid with code.

Thanks anyway. _________
Rott Paws

 

[vovan1415]

THIS should work...


SELECT DISTINCT
EQUIPMENT.EQUIP_TYPE,
EQUIPMENT.LOC,
NVL(SUB.TOTAL) TOTAL,
COUNT(DISTINCT EQUIPMENT.EQUIP_ID) AS USED
FROM
CORP.CONNECTIVITY,
CORP.CIRC_STAT_HIST,
CORP.EQUIPMENT,
(SELECT DISTINCT
COUNT(EQUIP_ID) AS TOTAL,
EQUIP_TYPE TYPE,
LOC
FROM
EQUIPMENT
WHERE (STATUS = 'IS') AND
(LOC = 'PLACE') AND
(EQUIP_TYPE IN ('TYPE1', 'TYPE2', 'TYPE3'))
GROUP BY EQUIP_TYPE, LOC) sub
WHERE
CIRCUITS.CIRC_ID = CIRCUIT_HIST.CIRC_ID AND
CIRCUITS.EQUIP_ID = EQUIPMENT.EQUIP_ID AND
(EQUIPMENT.LOC = 'PLACE') AND
(EQUIPMENT.EQUIP_TYPE IN ('TYPE1', 'TYPE2', 'TYPE3')) AND
(CORP.EQUIPMENT.EQUIP_STATUS_CD = 'IS') AND
(CIRCUIT_HIST.STATUS = 'IS') AND
EQUIPMENT.EQUIP_TYPE = sub.TYPE(+)
GROUP BY
EQUIPMENT.EQUIP_TYPE,
EQUIPMENT.LOC,
NVL(SUB.TOTAL)

 

[Turkbear]

RottPaws:
Which version of Oracle are you using? Subqueries are not supported in early ( 8.0.x or older) versions.


( BTW, version info is always needed for accurate answers, and more details than 'I wasn't able to get this to work' will enable better help)

 

[RottPaws]

That ROCKS!

I had to make a few changes. Most were do to the fact that I didn't make consistent changes to my SQL when I tried to make it more generic before posting it. I did get an "invalid number of arguments" error on the NVL clause. I changed it to NVL(SUB.Total,0) and it worked great.

Thanks a lot!
_________
Rott Paws