Bastardized query into one query
-
Hi, I have a query that needs to act as one single query: Here's the first part:
select distinct a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21 ' and j.fstatus = 'PAID' group by a.officerHere's the second part:
x.amount as New from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer having x.amount <> 0.0Here's the third part:
b.amount as Refi from escrow c inner join e120 b on c.escrow = b.escrow
inner join a10 f on c.escrow = f.escrow where c.ftype = 'R' group by b.amount, f.officer having b.amount <> 0.0Is there a way to make this act as one single query? I have an application that reads only one cursor at a time. Thanks In Advance,
Nino
-
Hi, I have a query that needs to act as one single query: Here's the first part:
select distinct a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21 ' and j.fstatus = 'PAID' group by a.officerHere's the second part:
x.amount as New from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer having x.amount <> 0.0Here's the third part:
b.amount as Refi from escrow c inner join e120 b on c.escrow = b.escrow
inner join a10 f on c.escrow = f.escrow where c.ftype = 'R' group by b.amount, f.officer having b.amount <> 0.0Is there a way to make this act as one single query? I have an application that reads only one cursor at a time. Thanks In Advance,
Nino
I must ask why is the second part using different alias names from the third part? The queries seem to only differ by escrow.ftype.
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
I must ask why is the second part using different alias names from the third part? The queries seem to only differ by escrow.ftype.
A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
My reasoning behind that was to get the values of two different fields. The second part is getting the values for the 'S' which is defined as a "New" escrow sale. The third part is getting the values for the 'R' which is defined as a "Refi" or refinance. After these two fields are retrieved they are then added based on the escrow field based on the join.
Nino
-
Hi, I have a query that needs to act as one single query: Here's the first part:
select distinct a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21 ' and j.fstatus = 'PAID' group by a.officerHere's the second part:
x.amount as New from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer having x.amount <> 0.0Here's the third part:
b.amount as Refi from escrow c inner join e120 b on c.escrow = b.escrow
inner join a10 f on c.escrow = f.escrow where c.ftype = 'R' group by b.amount, f.officer having b.amount <> 0.0Is there a way to make this act as one single query? I have an application that reads only one cursor at a time. Thanks In Advance,
Nino
I might ask why the second & third part have 'GROUP BY' & 'HAVING' clauses when there is only 1 field selected and it is not an aggregate. I also question the counting of distinct dates in the first query. Can't an officer handle more then one open/closed/cancelled transaction per day?