Hi Chris/Connor,
I need to find the count difference of below two queries like
-- Get the count of first SQL
-- Get the count of second SQL
-- Then COUNT(of 1st SQL) - COUNT(of 2nd SQL)
-- IF count if greater than 0 then I need to return one collection like user_id, N (if diff is Zero), Y (if diff >0)
E.g. USER_001 Y
USER_002 N -- if records match in both SQL's
..
so one
First SQL is exactly used in second SQL - the only difference if in 1st one i am getting COUNT (fk_tb_company_details).
And in 2nd SQL I am using same column fk_tb_company_details without count.
From first SQL we are getting count of companies assigned to that user.
And is second SQL we need to find out for how many companies records are there in test_lcr_details_1.
Lets say if for user USER_002, 4 companies assigned (SQL 1), and from those companies list only 3 re available in test_lcr_details_1 then I need to return Y.
If For USER_002 4 companies assigned and all companies are present in test_lcr_details_1 then I need to return N for that user.
Sample Date is shared in LiveSQl
https://livesql.oracle.com/apex/livesql/s/equjj563dc640x4ppy2x2y6g2 E.g. USER_001 Y
USER_002 N -- if records match in both SQL's
..
so one
Can you suggest any better approach to avoidexecution same SQL twice.
Select Uc.FK_TB_COMPANY_DETAILS
From test_user_company_1 Uc, test_company_det_1 cd
WHERE Uc.Fk_Tb_Login_Master in (select pk_Tb_Login_Master from test_login_mst_1 where user_id = 'USER_002')
And Uc.Fk_Tb_Company_Details = Cd.Pk_Tb_Company_Details;
-------------------------------------------------------------------------------
select link_id, fk_tb_login_master, fk_tb_company_details
from test_lcr_details_1
where link_id='DUMMY_LINK'
and fk_tb_login_master=(select pk_Tb_Login_Master from test_login_mst_1 where user_id = 'USER_002')
and fk_tb_company_details IN (Select Uc.FK_TB_COMPANY_DETAILS
From test_user_company_1 Uc, test_company_det_1 cd
WHERE Uc.Fk_Tb_Login_Master in (select pk_Tb_Login_Master from test_login_mst_1 where user_id = 'USER_002')
And Uc.Fk_Tb_Company_Details = Cd.Pk_Tb_Company_Details) ;
Thanks for the LiveSQL link! But I'm not really following what you're trying to do...
By "companies assigned and all companies are present in test_lcr_details_1" do you mean:
"All the values for FK_TB_COMPANY_DETAILS returned by the first query must be in the second query"
?
If so, then minus the results of the second query from the first. Then count the number of rows this returns. If it's zero they're the same, otherwise they're not:
CREATE TABLE TEST_LCR_DETAILS_1
( LINK_ID VARCHAR2(35 CHAR),
FK_TB_LOGIN_MASTER NUMBER(10,0),
FK_TB_COMPANY_DETAILS NUMBER(10,0)
) ;
CREATE TABLE TEST_USER_COMPANY_1
( FK_TB_COMPANY_DETAILS NUMBER(10,0),
FK_TB_LOGIN_MASTER NUMBER(10,0)
) ;
CREATE TABLE TEST_COMPANY_DET_1
( PK_TB_COMPANY_DETAILS NUMBER(10,0)
) ;
Insert into TEST_LOGIN_MST_1 (PK_TB_LOGIN_MASTER,USER_ID) values (929733,'USER_001');
Insert into TEST_LOGIN_MST_1 (PK_TB_LOGIN_MASTER,USER_ID) values (929744,'USER_002');
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13603);
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13605);
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13747);
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13751);
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929744,13605);
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929744,13747);
Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929744,13751);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13603,929733);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13605,929733);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13747,929733);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13751,929733);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13603,929744);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13605,929744);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13747,929744);
Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13751,929744);
Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13603);
Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13605);
Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13747);
Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13751);
commit;
with q1 as (
select uc.fk_tb_company_details
from test_user_company_1 uc, test_company_det_1 cd
where uc.fk_tb_login_master in
( select pk_tb_login_master from test_login_mst_1 where user_id = 'USER_002'
)
and uc.fk_tb_company_details = cd.pk_tb_company_details
), q2 as (
select link_id, fk_tb_login_master, fk_tb_company_details
from test_lcr_details_1
where link_id ='DUMMY_LINK'
and fk_tb_login_master=
( select pk_tb_login_master from test_login_mst_1 where user_id = 'USER_002'
)
and fk_tb_company_details in
(select uc.fk_tb_company_details
from test_user_company_1 uc, test_company_det_1 cd
where uc.fk_tb_login_master in
( select pk_tb_login_master from test_login_mst_1 where user_id = 'USER_002'
)
and uc.fk_tb_company_details = cd.pk_tb_company_details
)
), diff as (
select fk_tb_company_details from q1
minus
select fk_tb_company_details from q2
)
select case count(*)
when 0 then 'N'
else 'Y'
end
from diff;
CASECOUNT(*)WHEN0THEN'N'ELSE'Y'END
Y
If this isn't what you want, then please clarify in much more detail what you're trying to do.