1

Topic: To unite in the table of two columns two requests. Not UNION

All greetings. I ask to help! Tried set of variants, but the decision did not find. Faced such problem.
There are two requests from two different tables with conditions, deduce an amount. It is necessary that they formed two columns.
The first request:

SELECT COUNT (DISTINCT VL.CLIENT_ID) AS WITHOUT_P FROM V_LOAN VL
LEFT JOIN IFS_KERNEL.CLIENT_PHONE cp ON VL.CLIENT_ID = cp. CLIENT_ID
left join IFS_KERNEL.DIC_ITEM dic on (dic. ID = cp. SOURCE_ID)
left join LOAN_OUTER lo on (lo.id = VL.id)
WHERE CP.CLIENT_ID IS NULL AND cp. PHONE IS NULL
AND LO.ORG_ID = 61
and VL.LIST_NUMBER like ' No12- ') AS "Without_phones"

The second:

SELECT count (DISTINCT CP.CLIENT_ID) as "With_p" FROM IFS_KERNEL.CLIENT_PHONE CP
left join V_LOAN VL on (VL.CLIENT_ID = cp. CLIENT_ID)
left join IFS_KERNEL.DIC_ITEM dic on (dic. ID = cp. SOURCE_ID)
left join LOAN_OUTER lo on (lo.id = VL.id)
where cp. SOURCE_ID is not null
AND lo. ORG_ID = 61
and VL.LIST_NUMBER like ' No12- ') as "With_phones"

Most likely for you it will be difficult to understand that to what then I ask by analogy any example, the main thing that from different tables. Examples of join of requests from one heap.
Such variant works for me, but it :

select (SELECT COUNT (DISTINCT VL.CLIENT_ID) AS WITHOUT_P FROM V_LOAN VL
LEFT JOIN IFS_KERNEL.CLIENT_PHONE cp ON VL.CLIENT_ID = cp. CLIENT_ID
left join IFS_KERNEL.DIC_ITEM dic on (dic. ID = cp. SOURCE_ID)
left join LOAN_OUTER lo on (lo.id = VL.id)
WHERE CP.CLIENT_ID IS NULL AND cp. PHONE IS NULL
AND LO.ORG_ID = 61
and VL.LIST_NUMBER like ' No12- ') AS "Without_phones",
(SELECT count (DISTINCT CP.CLIENT_ID) as "With_p" FROM IFS_KERNEL.CLIENT_PHONE CP
left join V_LOAN VL on (VL.CLIENT_ID = cp. CLIENT_ID)
left join IFS_KERNEL.DIC_ITEM dic on (dic. ID = cp. SOURCE_ID)
left join LOAN_OUTER lo on (lo.id = VL.id)
where cp. SOURCE_ID is not null
AND lo. ORG_ID = 61
and VL.LIST_NUMBER like ' No12- ') as "With_phones"
from (SELECT COUNT (DISTINCT VL.CLIENT_ID) AS WITHOUT_P FROM V_LOAN VL
LEFT JOIN IFS_KERNEL.CLIENT_PHONE cp ON VL.CLIENT_ID = cp. CLIENT_ID
left join IFS_KERNEL.DIC_ITEM dic on (dic. ID = cp. SOURCE_ID)
left join LOAN_OUTER lo on (lo.id = VL.id)
WHERE CP.CLIENT_ID IS NULL AND cp. PHONE IS NULL)

2

Re: To unite in the table of two columns two requests. Not UNION

arelsom wrote:

such variant works For me, but it :

select (SELECT COUNT (DISTINCT VL.CLIENT_ID) AS WITHOUT_P FROM V_LOAN VL
>>> LEFT JOIN IFS_KERNEL.CLIENT_PHONE cp ON VL.CLIENT_ID = cp. CLIENT_ID <<<
left join IFS_KERNEL.DIC_ITEM dic on (dic. ID = cp. SOURCE_ID)
left join LOAN_OUTER lo on (lo.id = VL.id)
>>> WHERE CP.CLIENT_ID IS NULL AND cp. PHONE IS NULL <<<
AND LO.ORG_ID = 61
and VL.LIST_NUMBER>>> like ' No12- ' <<<) AS "Without_phones",
...

And your "working" variant produces the true data? Without knowing structure of your data, true council not to give, .
If V_LOAN at you the list of "clients" contains, as a variant, for a thought direction:

select sum (case when not exists (select 1 from IFS_KERNEL.CLIENT_PHONE CP where cp. CLIENT_ID = VL.CLIENT_ID) then 1 end) "Without_phones"
,sum (case when exists (select 1 from IFS_KERNEL.CLIENT_PHONE CP where cp. CLIENT_ID = VL.CLIENT_ID) then 1 end) "With_phones"
from V_LOAN VL, LOAN_OUTER lo
where VL.id = lo.id
and LO.ORG_ID = 61
and VL.LIST_NUMBER like ' No12- %'

but surrenders to me that this representation which also refers to the same tables. And generally -  were mistaken.
The moderator: the Subject is transferred from a forum "Oracle Forms".

3

Re: To unite in the table of two columns two requests. Not UNION

arelsom;

SQL> ed
Wrote file afiedt.buf
1 select
2 (select count (distinct job) from emp) c_emp
3, (select count (deptno) from dept) c_dept
4* from dual
SQL> /
C_EMP C_DEPT
---------- ----------
5 4

Than does not arrange?
....
stax