If not all ids are present return nothing

oracle

I have the following query

SELECT ATV.ATTRIBUTEVALUE_ID,
       ATV.ATTRIBUTE_ID,
       ASS.ASSET_ID
FROM   CLOTH.ATTRIBUTEVALUE ATV
JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
JOIN   CLOTH.ASSET ASS
ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 ) 
AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )

which returns

ATTRIBUTEVALUE_ID, ATTRIBUTE_ID, ASSET_ID,
7                      2          9103354
8                      2          7829028
9                      2          7829028
10                     2          7829028

It should return nothing, because 13057020 is not returned.

What this means is, when I have ATTRIBUTE_ID # and all ASSET_ID are not present (like now, 13057020 is missing) nothing should be returned.

Best Answer

One way:

WITH
  qry AS
  (  -- your query
    SELECT ATV.ATTRIBUTEVALUE_ID,
           ATV.ATTRIBUTE_ID,
           ASS.ASSET_ID
    FROM   CLOTH.ATTRIBUTEVALUE ATV
    JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
    ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
    JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
    ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
    JOIN   CLOTH.ASSET ASS
    ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
    WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 ) 
    AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )
  ),
  cnt AS
  ( -- count condition to be satisfied
    SELECT 1
    FROM qry
    HAVING COUNT(DISTINCT ASSET_ID) = 3
  )
SELECT qry.*
FROM qry, cnt ;

The cnt will have 0 rows and so will the cross join (FROM qry, cnt) if the condition is not met.

You could write the final query like this, perhaps it's more readable:

WITH
---

  cnt AS
  ( -- count condition to be satisfied
    SELECT COUNT(DISTINCT ASSET_ID) AS assets
    FROM qry
  )
SELECT qry.*
FROM qry
WHERE (SELECT assets FROM cnt) = 3 ;

Another way would be a window function:

WITH
  qry AS
  (  -- your query with one more column
    SELECT ATV.ATTRIBUTEVALUE_ID,
           ATV.ATTRIBUTE_ID,
           ASS.ASSET_ID,
           COUNT(DISTINCT ASS.ASSET_ID) OVER () AS assets
    FROM   CLOTH.ATTRIBUTEVALUE ATV
    JOIN   CLOTH.ASSET_ATTRIBUTEVALUE ASATV
    ON     ATV.ATTRIBUTEVALUE_ID = ASATV.ATTRIBUTEVALUE_ID
    JOIN   CLOTH.ASSET_ATTRIBUTE ASS_AT
    ON     ASATV.ASSET_ATTRIBUTE_ID = ASS_AT.ASSET_ATTRIBUTE_ID
    JOIN   CLOTH.ASSET ASS
    ON     ASS.ASSET_ID = ASS_AT.ASSET_ID
    WHERE  ATV.ATTRIBUTE_ID IN ( 1 , 2 , 3 ) 
    AND    ASS.ASSET_ID IN ( 13057020, 9103354, 7829028 )
  )
SELECT qry.ATTRIBUTEVALUE_ID,
       qry.ATTRIBUTE_ID,
       qry.ASSET_ID
FROM qry
WHERE qry.assets = 3 ;

Related Solutions

Compare dates problems

I see two problems with your date filter:

You're applying TO_DATE on a DATE datatype. It is not only superfluous, but it actually causes unexpected behaviour.

Here's how Oracle analyses the expression TO_DATE(data_inicio,'yyyy/mm/dd'):
- Oracle searches for a function named TO_DATE that has two arguments (DATE, VARCHAR2)
- Since no such function exists, Oracle looks for related functions that are accessible with implicit conversions.
- (Un)fortunately, a single TO_DATE function exists, so there is no ambiguity here, the function choosen is TO_DATE(VARCHAR2, VARCHAR2).
- Your date is converted to a VARCHAR2 with the current session date format, which means that the behaviour is unpredictable. The expression is equivalent to:
```
TO_DATE(TO_CHAR(data_inicio, 'current NLS_DATE_FORMAT'), 'yyyy/mm/dd')
```
  In this case you will lose the time component of the date if your NLS_DATE_FORMAT is YYYY/MM/DD.
You should never rely on implicit conversions because the behaviour is context dependent.
Your interval filter is wrong: two intervals [A,B] and [C,D] will intersect if and only if:
- A <= D
- and B >= C

Your query should look like:

SELECT COUNT(*) AS COUNT
  FROM indicador_dados
 WHERE id = '23'
   AND date_start  <= to_date('2013/05/16', 'yyyy/mm/dd')
   AND date_end >= to_date('2013/05/15', 'yyyy/mm/dd')

Selecting from a function that returns a sys refcursor or an alternative

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

This might be true, but is unlikely to be so.

When you apply a condition to a view, the condition is usually merged into the view query. This is called predicate pushing, and ought to allow the optimiser to apply the filter as early as possible in the execution plan.

There are some cases where this cannot happen, for example if your view summed up all of the sales orders by client and then you applied a condition to require that total sales order were greater then $1,000,000.

The explain plan wil tell you whether this has happened, but it's best to extract it using DBMS_Xplan.Display. the resulting text-based plan will show you the lines of the plan at which conditions are applied.

Best Answer

Related Solutions

Compare dates problems

Selecting from a function that returns a sys refcursor or an alternative

Related Question