Home » SQL & PL/SQL » SQL & PL/SQL » Minus in Select (Oracle 11g)
Minus in Select [message #429995] Fri, 06 November 2009 14:07 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I see different results when i use MINUS in select statement,

select core.cust_dim_nb,core.usr_dim_nb, core.prod_dim_nb,core.acct_dim_nb,  core.pvlg_dim_nb   from hds01.hds_fct core 
where usr_dim_nb <> 0 and acct_dim_nb <> 0   minus
  select iris.cust_dim_nb,iris.usr_dim_nb, iris.prod_dim_nb,iris.acct_dim_nb,iris.pvlg_dim_nb  from iridbhq5.hds_fct@drda iris ; 

Number of Rows  -- 9525  ---> This is correct



But when i add few more columns in select statement, results are different
select core.cust_dim_nb,core.usr_dim_nb, core.prod_dim_nb,core.acct_dim_nb,  core.pvlg_dim_nb  ,core.sts_cd ,core.cre_usr_id from hds01.hds_fct core 
where usr_dim_nb <> 0 and acct_dim_nb <> 0   minus
  select iris.cust_dim_nb,iris.usr_dim_nb, iris.prod_dim_nb,iris.acct_dim_nb,iris.pvlg_dim_nb ,iris.sts_cd, iris.cre_usr_id  from iridbhq5.hds_fct@drda iris ; 
Number of Rows -- 84051----> This result is also wrong.



Both the tables have same datatype,
But this DB Link is "iridbhq5.hds_fct@drda " is talking to DB2 Database.

Does column addition which are not part of Primary Key can cause this or what else.

Thanks
Re: Minus in Select [message #429996 is a reply to message #429995] Fri, 06 November 2009 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT core.cust_dim_nb,
       core.usr_dim_nb,
       core.prod_dim_nb,
       core.acct_dim_nb,
       core.pvlg_dim_nb
FROM   hds01.hds_fct core
WHERE  usr_dim_nb <> 0
       AND acct_dim_nb <> 0
MINUS
SELECT iris.cust_dim_nb,
       iris.usr_dim_nb,
       iris.prod_dim_nb,
       iris.acct_dim_nb,
       iris.pvlg_dim_nb
FROM   iridbhq5.hds_fct@drda iris; 


SELECT core.cust_dim_nb,
       core.usr_dim_nb,
       core.prod_dim_nb,
       core.acct_dim_nb,
       core.pvlg_dim_nb,
       core.sts_cd,
       core.cre_usr_id
FROM   hds01.hds_fct core
WHERE  usr_dim_nb <> 0
       AND acct_dim_nb <> 0
MINUS
SELECT iris.cust_dim_nb,
       iris.usr_dim_nb,
       iris.prod_dim_nb,
       iris.acct_dim_nb,
       iris.pvlg_dim_nb,
       iris.sts_cd,
       iris.cre_usr_id
FROM   iridbhq5.hds_fct@drda iris; 



>Does column addition which are not part of Primary Key can cause this
YES, without exact match rows remain in result set.
Re: Minus in Select [message #430006 is a reply to message #429995] Fri, 06 November 2009 15:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
select 1,2,3 from dual
minus
select 1,2,3 from dual
/

select 1,2,3,99 from dual
minus
select 1,2,3,100 from dual
/


Why does adding additional columns to your selects causing your results to change, confuse you?

Do you know what minus does?

What do you think should happen?

Kevin
Re: Minus in Select [message #430234 is a reply to message #430006] Mon, 09 November 2009 11:34 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for Reply,

I have to complete this insert .
The Select statement here at first gives me the correct count, which i want to insert, the problem is it's having less number of columns then we are inserting

Insert into IRIDBHQ5.HDS_FCT@DRDA (CUST_DIM_NB,USR_DIM_NB,ACCT_DIM_NB,
PROD_DIM_NB,PVLG_DIM_NB,
SRV_DIM_NB,STS_CD,STS_MOD_DT,
CRE_USR_ID,CRE_DT,UPDT_USR_ID,UPDT_DT) 
select core.cust_dim_nb,core.usr_dim_nb, core.prod_dim_nb,core.acct_dim_nb,core.pvlg_dim_nb
from hds01.hds_fct core  
where usr_dim_nb <> 0 and acct_dim_nb <> 0   
minus
select iris.cust_dim_nb,iris.usr_dim_nb,
iris.prod_dim_nb,iris.acct_dim_nb,
iris.pvlg_dim_nb
from iridbhq5.hds_fct@drda iris


If I increase the columns in the select statement my results are not accurate,which we don't want, How can i do this insert into 12 columns but in select i have only 5, Any inner query or any other way to do this .

This insert is with all columns in select, which gives more rows and will insert more, which we don't want.
Insert into IRIDBHQ5.HDS_FCT@DRDA (CUST_DIM_NB,USR_DIM_NB,ACCT_DIM_NB,PROD_DIM_NB,PVLG_DIM_NB,
SRV_DIM_NB,STS_CD,STS_MOD_DT,CRE_USR_ID,CRE_DT,_USR_ID,UPDT_DT) 
select core.cust_dim_nb,core.usr_dim_nb, core.prod_dim_nb,core.acct_dim_nb,core.pvlg_dim_nb
SRV_DIM_NB,STS_CD,
TO_CHAR(CAST(STS_MOD_DT AS TIMESTAMP), 'YYYY-MM-DD-HH.MI.SS.FF6'),
CRE_USR_ID,  TO_CHAR(CAST(cre_dt AS TIMESTAMP), 'YYYY-MM-DD-HH.MI.SS.FF6'),UPDT_USR_ID,
TO_CHAR(CAST(updt_dt AS TIMESTAMP), 'YYYY-MM-DD-HH.MI.SS.FF6') from hds01.hds_fct core 
where usr_dim_nb <> 0 and acct_dim_nb <> 0 
minus
select iris.cust_dim_nb,iris.usr_dim_nb, iris.prod_dim_nb,iris.acct_dim_nb,iris.pvlg_dim_nb,
iris.SRV_DIM_NB,iris.STS_CD,iris.STS_MOD_DT,
iris.CRE_USR_ID,iris.CRE_DT,iris.UPDT_USR_ID,iris.UPDT_DT
from iridbhq5.hds_fct@drda iris


We have to apply functions on Date, as we are comparing it with other DB2 Database,over the DB link.

Any suggestion how to write this insert.

Thanks a lot
Re: Minus in Select [message #430235 is a reply to message #429995] Mon, 09 November 2009 11:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Try wrapping your select inside another select. Consider this code:

select dummy from dual
/

select
          a.dummy
        , cast(null as number) anumber
        , cast(null as date) adate
        , cast(null as varchar2(30)) avarchar2_30
from (
       select dummy from dual
     ) a
/

What do you think of this?

Kevin
Difference in Result Count [message #431045 is a reply to message #430235] Fri, 13 November 2009 16:00 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Please see this sequence of Select

select count(*) from hds01.usr_prod_pref -- 9979 rows

select count(*) from IRIDBHQ5.USR_PROD_PREF@DRDA --5572 rows   

 Difference is =  4407 which is correct


select pref_cd,usr_dim_nb,prod_dim_nb from hds01.usr_prod_pref
minus
select pref_cd,usr_dim_nb,prod_dim_nb from IRIDBHQ5.USR_PROD_PREF@DRDA
-- gives 9919 rows. these 3 columns make up a PK 

suppose to give me 4407

Then Another Select i tried , this also gives me 9919 rows
SELECT 'Insert into IRIDBHQ5.USR_PROD_PREF@DRDA (USR_DIM_NB,PREF_CD,PROD_DIM_NB,DFLT_PREF_VAL_TX,
USR_PREF_VAL_TX, STS_CD,STS_MOD_DT,CRE_USR_ID,CRE_DT,UPDT_USR_ID,UPDT_DT) values ('
|| PREF.usr_dim_nb || ',''' 
|| PREF.PREF_CD || ''','
|| PREF.prod_dim_nb ||',''' 
||nvl(PREF.DFLT_PREF_VAL_TX,'') ||''',''' 
||nvl(PREF. USR_PREF_VAL_TX,'') ||''','''
|| PREF.sts_cd ||''','''
||TO_CHAR(CAST(NVL(PREF.sts_mod_dt,sysdate) AS TIMESTAMP), 'YYYY-MM-DD-HH.MI.SS.FF6') ||''','''
||PREF.CRE_USR_ID ||''','''
||TO_CHAR(CAST(NVL(PREF.cre_dt,sysdate) AS TIMESTAMP), 'YYYY-MM-DD-HH.MI.SS.FF6')||''''||','''
||PREF.UPDT_USR_ID||''','''
||TO_CHAR(CAST(NVL(PREF.updt_dt,sysdate) AS TIMESTAMP), 'YYYY-MM-DD-HH.MI.SS.FF6')||'''' || ');'
From hds01.USR_PROD_PREF PREF
WHERE usr_dim_nb <> 0 and NOT EXISTS (
SELECT 1 FROM iridbhq5.USR_PROD_PREF@drda iris
where upper(PREF.pref_Cd) =upper(iris.pref_Cd)
and PREF.usr_dim_nb = iris.usr_dim_nb
and PREF.PROD_DIM_NB = iris.PROD_DIM_NB);


Is there any logic i am missing here or what, Please advice,
I am supposed to get only the difference record and make up this dynamic insert script.

Thanks
Re: Minus in Select [message #431046 is a reply to message #429995] Fri, 13 November 2009 16:16 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, the SQL don't lie.

select pref_cd,usr_dim_nb,prod_dim_nb from hds01.usr_prod_pref
minus
select pref_cd,usr_dim_nb,prod_dim_nb from IRIDBHQ5.USR_PROD_PREF@DRDA
-- gives 9919 rows. these 3 columns make up a PK 

suppose to give me 4407


This is telling you that there are 9919 key combos (pref_cd,user_dim_nb,prod_dim_nb) in hds01.usr_prod_pref which do not show up in IRIDBHQ5.USR_PROD_PREF@DRDA.

There is not much else to say about it. You will need to have a look at your data to figure out why. Consider this:

TABLE A       TABLE B
-------       -------
1             4
2             5
3             6

select count(*) from table_a; => 3
select count(*) from table_b; => 3

select c1 from table_a
minus
select c1 form table_b

yeilds 3. Do not assume that just because your rowcounts subtract to some number, that this implies the key lists in both rowsources have as much in common as you thought.

select count(*) from hds01.usr_prod_pref -- 9979 rows

select count(*) from IRIDBHQ5.USR_PROD_PREF@DRDA --5572 rows   

 Difference is =  4407 which is correct

hds01.usr_prod_pr minus IRIDBHQ5.USR_PROD_PREF@DRDA

will only yeild 4407 if all the keys in IRIDBHQ5.USR_PROD_PREF@DRDA are also in hds01.usr_prod_pr, but this is clearly not the case.

Kevin
Previous Topic: Converting a sql script to pl/sql
Next Topic: GMT to EST or EDT
Goto Forum:
  


Current Time: Thu Dec 08 03:49:22 CST 2016

Total time taken to generate the page: 0.17764 seconds