Oracle 10g - CASE against count

this is my attempt at the CASE. It works, but returns a row of output for every line of the order. I am looking for only 1 row output

null
null
MY STRING
Null
MY STRING

SELECT 
CASE
WHEN BRAND = 'WILLIAMS' THEN 'MY STRING'
END AS CMNT
    from (
    select D5.ORD_NUM, D5.ORD_LEV1 ITEM, D3.ALT_INVT_REP_CODE BRAND
    from E_ORD_D5 D5
    LEFT JOIN M_ITEM_D3 D3 on D3.COMP_CODE = D5.COMP_CODE and D3.CUST_CODE = D5.CUST_CODE and D3.ITEM_CODE = D5.ORD_LEV1
    where D5.comp_code = 'W1' and D5.ord_num = 935203 
    )

Select allOpen in new window

SELECT D5.ORD_NUM, D5.ORD_LEV1 ITEM , (SELECT MAX(CASE brand
                    when 'WILLIAMS' THEN 'MY  STRING'
                    ELSE
                        null
                    END
                    FROM M_TIME_D3 WHERE  D3.COMP_CODE = D5.COMP_CODE and D3.CUST_CODE = D5.CUST_CODE and D3.ITEM_CODE =                                                           D5.ORD_LEV1)  CMT
FROM E_ORD_D5 D5
where D5.comp_code = 'W1' and D5.ord_num = 935203
-- you will loose the D3.ALT_INVT_REP_CODE BRAND
-- but you only wanted I  row for an order
--  or do you want all items for the order but all with the same CMT ?

I'm not really following what you are after.

Can you provide a simple test case with sample data and expected results?

If you are interested in the total number of items, use a left join, but include the brand restriction into the join instead of a where clause.

SELECT
    COUNT( ITEM ) total_items
  , COUNT( BRAND ) wiliams_items
  , MAX( BRAND ) brand
  , MAX(CASE WHEN BRAND = 'WILLIAMS' THEN 'My String' END) some_string
FROM (
    SELECT
        D5.ORD_NUM
      , D5.ORD_LEV1          ITEM
      , D3.ALT_INVT_REP_CODE BRAND
    FROM E_ORD_D5 D5
    LEFT JOIN M_ITEM_D3 D3 ON D3.COMP_CODE = D5.COMP_CODE
        AND D3.CUST_CODE = D5.CUST_CODE
        AND D3.ITEM_CODE = D5.ORD_LEV1
        AND D3.BRAND = 'WILLIAMS'
    WHERE D5.comp_code = 'W1'
    AND D5.ord_num = 935373
) d
;

Select allOpen in new window

If you are not interested in the count of all items, use an inner join, and here you can use the brand restriction in a where clause

SELECT
    COUNT( BRAND ) wiliams_items
  , MAX( BRAND ) brand
  , MAX(CASE WHEN BRAND = 'WILLIAMS' THEN 'My String' END) some_string
FROM (
    SELECT
        D5.ORD_NUM
      , D5.ORD_LEV1          ITEM
      , D3.ALT_INVT_REP_CODE BRAND
    FROM E_ORD_D5 D5
    INNER JOIN M_ITEM_D3 D3 ON D3.COMP_CODE = D5.COMP_CODE
        AND D3.CUST_CODE = D5.CUST_CODE
        AND D3.ITEM_CODE = D5.ORD_LEV1
    WHERE D5.comp_code = 'W1'
    AND D5.ord_num = 935373
    AND D3.BRAND = 'WILLIAMS'
) d
;

Select allOpen in new window

Sorry.. My question was not clear.
An order may have 1 to 1000 line items. When I query the order, if any one line within that order contains 'Williams', I need to return a text string

In this example Order 10002 should return the text string, 10001 should not

D5.ORD_NUM	D5.ORD_LEV1	D3.ALT_INVT_REP_CODE
---------	-----------	--------------------
10001		AB001		MIKE
10001		AB023		MIKE
10001		AQ234		JOHN

10002		SW234		FRANK
10002		AQ232		WILLIAMS
10002		AB111		MIKE

Select allOpen in new window

then try  (i can't check syntax at the moment)
    select D5.ORD_NUM, D5.ORD_LEV1 ITEM, D3.ALT_INVT_REP_CODE BRAND,
    MAX(CASE brand
                    when 'WILLIAMS' THEN 'MY  STRING'
                    ELSE
                        null
                    END
       )  OVER (PARTITION BY D5.ORD_NUM) CMT            
    from E_ORD_D5 D5
    LEFT JOIN M_ITEM_D3 D3 on D3.COMP_CODE = D5.COMP_CODE and D3.CUST_CODE = D5.CUST_CODE and D3.ITEM_CODE = D5.ORD_LEV1
    where D5.comp_code = 'W1' and D5.ord_num = 935203

Since you didn't provide specific expected results, see if this is what you are after:

select min(ord_num), 'MY STRING'
from tab1
where alt_invt_rep_code = 'WILLIAMS'
/

Select allOpen in new window

If not, please provide your specific desired results so we do not have to guess

Desired result is simply the text 'My String'. No columns are needed
If the order contains any line with Williams then output 'My string'
If the order noes not contain any lines with 'Williams' then there is no output.

Slightwv.. your query works perfect. Thanks
Flow01.. your first query also works well