Link to home
Create AccountLog in
Avatar of JDCam
JDCam

asked on

Oracle 10g - CASE against count

I need some help with a CASE statement.

I am looking at sales order lines and linking out to a reference table. I want the case to output 'MY STRING' If at least 1 line on the order has the value 'WILLIAMS'. Nothing output if no lines contain this value.

So far I wrote a query to return the reference, then added a count where the reference equals Williams.

Select count(ITEM) 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 = 935373 
    )
WHERE BRAND = 'WILLIAMS'

Open in new window


Not sure how to make this part of a CASE. (of if CASE is even the best approach)
Avatar of JDCam
JDCam

ASKER

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 
    )

Open in new window

Avatar of flow01
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
;

Open 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
;

Open in new window

Avatar of JDCam

ASKER

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

Open 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'
/

Open in new window


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

ASKER

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.
SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of JDCam

ASKER

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