Qry Returns More Rows with Distinct than Without

[thermidor]

Hi All

I have the query below. If I comment out DISTINCT I get 6446 records. If I include DISTINCT I get 6859 records. ANy ideas how this could be?

TIA,
Sven

select count(*) from
(SELECT
distinct
fcst_signature,
NVL (interim_sku, lowest_sku) sku,
'D' stock_type,
'D' dmd_type,
'C' explosion_type,
'DMST' fcst_type,
CASE
WHEN interim_sku IS NULL
THEN fcst_units_rev * (lowest_revunits + lowest_nonrevunits)
ELSE fcst_units_rev * interim_revunits
END fcst_units_rev,
0 fcst_units_nonrev,
fcst_yr_prd,
NULL fcst_yr_prd_offset,
fcst_horizon_code,
fcst_id
FROM vw_fdm_bom_unexp_fcst_inv_net
WHERE stock_type = 'D'
AND dmd_type = 'I'
AND explosion_type = 'U'
AND fcst_type = 'DMST')

 

[SantaMufasa]

Thermidor,

Can you produce similar anomalous results (i.e., fewer rows without DISTINCT) with a cut-down set of rows from "vw_fdm_bom_unexp_fcst_inv_net" (say 10-20 rows)?

If you can produce such a situation, then please post here the "CREATE TABLE vw_fdm_bom_unexp_fcst_inv_net..." and "INSERT INTO vw_fdm_bom_unexp_fcst_inv_net..." statements so that we can experiment with this fascinating set of circumstances.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

I think the VW at the start of the table name might be a clue. Is it by any chance a VIEW rather than an actual table ? If so, it could easily consist of a many-to-one or many-to-many join of several tables.

 

[ChrisHunt]

Doing this might give you a clue as to what's going on:

SELECT
        distinct
        fcst_signature,
        NVL (interim_sku, lowest_sku) sku,
        'D' stock_type,
        'D' dmd_type,
        'C' explosion_type,
        'DMST' fcst_type,
         CASE
            WHEN interim_sku IS NULL
               THEN fcst_units_rev * (lowest_revunits + lowest_nonrevunits)
            ELSE fcst_units_rev * interim_revunits
         END fcst_units_rev,     
         0 fcst_units_nonrev,
         fcst_yr_prd,
         NULL fcst_yr_prd_offset,
         fcst_horizon_code,
         fcst_id
        FROM vw_fdm_bom_unexp_fcst_inv_net
       WHERE stock_type = 'D'
         AND dmd_type = 'I'
         AND explosion_type = 'U'
         AND fcst_type = 'DMST'
minus
SELECT
        fcst_signature,
        NVL (interim_sku, lowest_sku) sku,
        'D' stock_type,
        'D' dmd_type,
        'C' explosion_type,
        'DMST' fcst_type,
         CASE
            WHEN interim_sku IS NULL
               THEN fcst_units_rev * (lowest_revunits + lowest_nonrevunits)
            ELSE fcst_units_rev * interim_revunits
         END fcst_units_rev,     
         0 fcst_units_nonrev,
         fcst_yr_prd,
         NULL fcst_yr_prd_offset,
         fcst_horizon_code,
         fcst_id
        FROM vw_fdm_bom_unexp_fcst_inv_net
       WHERE stock_type = 'D'
         AND dmd_type = 'I'
         AND explosion_type = 'U'
         AND fcst_type = 'DMST'

That'll show you the rows that are showing up in one query but not in the other, but really there shouldn't be any...

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Dagon]

I don't think that approach will work.

a) You'd need the MINUS the other way round. The distinct rows will be the smaller set, so need to be taken away from the larger set.

b) Even then it won't work because MINUS removes duplicates. So if there are three rows with data A, B, C in the non-distinct set and one A,B,C in the distinct set, the query will return no rows because all the duplicates are eliminated.

There is no great mystery as to why vw_fdm_bom_unexp_fcst_inv_net should have duplicates. Unless it has a primary key defined and the primary key columns are being selected, it is entirely possible that it could contain duplicates. That's leaving aside the possibility that it's actually not a table at all but a view.

 

[SantaMufasa]

Thermidor,

It is "good form" to acknowledge the replies to your original posting. What is the status of this thread?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”