OraFAQ Forum: SQL & PL/SQL » Minus in Select

Members

Help

Home

Home » SQL & PL/SQL » SQL & PL/SQL » Minus in Select (Oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Minus in Select [message #429995]

Fri, 06 November 2009 14:07

faiz_hyd
Messages: 283
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

Report message to a moderator

Re: Minus in Select [message #429996 is a reply to message #429995]

Fri, 06 November 2009 14:16

BlackSwan
Messages: 3358
Registered: January 2009

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.

Report message to a moderator

Re: Minus in Select [message #430006 is a reply to message #429995]

Fri, 06 November 2009 15:28

Kevin Meade
Messages: 1050
Registered: December 1999

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

Report message to a moderator

Re: Minus in Select [message #430234 is a reply to message #430006]

Mon, 09 November 2009 11:34

faiz_hyd
Messages: 283
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

Report message to a moderator

Re: Minus in Select [message #430235 is a reply to message #429995]

Mon, 09 November 2009 11:40

Kevin Meade
Messages: 1050
Registered: December 1999

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

Report message to a moderator

Difference in Result Count [message #431045 is a reply to message #430235]

Fri, 13 November 2009 16:00

faiz_hyd
Messages: 283
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

Report message to a moderator

Re: Minus in Select [message #431046 is a reply to message #429995]

Fri, 13 November 2009 16:16

Kevin Meade
Messages: 1050
Registered: December 1999

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 30 21:42:10 CST 2009

Total time taken to generate the page: 0.14458 seconds