How do I get just the case qty

This query comes up with 2 results.  What I want it to do is to show the stk_no when it is a case (CA), if it is anything else show NULL)

The result I am looking for is it to show 1 result where the stk_no = 30.



SELECT DISTINCT
        apvndmstr.vend_name AS 'MFG Name' ,
        partmstr.alt_part1 AS 'MFG #' ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        pricemtx.part_code ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
        partmstr.part_height ,
    'CASE QTY' = (SELECT CASE WHEN uommstr_1.uom ='CA' THEN uommstr_1.stk_no ELSE NULL END), 
                 
        partmstr.royalty_code AS 'Selling Units' ,
        vendpart.min_ord_qty ,
        partmstr.part_price AS LIST_PRICE ,
        MIN(CASE WHEN pricemtx.price_meth = 'C'
                 THEN ROUND(( ( pricemtx.part_price * .01 )
                              * partmstr.cost_no6 ), 2)
                 WHEN pricemtx.price_meth = 'R' THEN pricemtx.part_price
                 WHEN pricemtx.price_meth = 'M'
                 THEN ROUND(( partmstr.part_price + ( pricemtx.part_price
                                                      * partmstr.part_price )
                              / 100 ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '%'
                 THEN ROUND(( ( 100 - pricemtx.part_disc ) * .01
                              * partmstr.part_price ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '$'
                      AND pricemtx.formula_id = 'BASELIST'
                 THEN ROUND(( partmstr.part_price - pricemtx.part_disc ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '$'
                      AND pricemtx.formula_id <> 'BASELIST'
                 THEN ROUND(( ( pricemtx_1.part_price * .01 )
                              * ( partmstr.cost_no6 ) - pricemtx.part_disc ),
                            2)
            END) AS 'NET_PRICE' ,
        uommstr.uom
      
FROM    uommstr
        INNER JOIN pricemtx
        INNER JOIN partmstr ON pricemtx.part_code = partmstr.part_code ON uommstr.part_code = pricemtx.part_code
        LEFT OUTER JOIN uommstr AS uommstr_1 ON pricemtx.part_code = uommstr_1.part_code
        LEFT OUTER JOIN apvndmstr
        RIGHT OUTER JOIN vendpart ON apvndmstr.vend_code = vendpart.vend_code ON pricemtx.part_code = vendpart.part_code
        LEFT OUTER JOIN pricemtx AS pricemtx_1 ON pricemtx.formula_id = pricemtx_1.price_id
                                                  AND pricemtx.part_code = pricemtx_1.part_code
        LEFT OUTER JOIN custprice ON pricemtx.price_id = custprice.price_id
WHERE   ( custprice.cust_code = '8400343' )
        AND ( custprice.end_date > DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) )
        AND ( partmstr.avail = 'S' )
        AND ( uommstr.uom = 'EA' )
        AND ( partmstr.part_code = 'AAC05915' )
GROUP BY pricemtx.part_code ,
        custprice.price_id ,
        pricemtx.price_meth ,
        partmstr.alt_part1 ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
        partmstr.part_height ,
        vendpart.min_ord_qty ,
        partmstr.royalty_code ,
        vendpart.vend_name ,
        apvndmstr.vend_name ,
        apvndmstr.vend_name2 ,
        uommstr.uom ,
        partmstr.part_price ,
        partmstr.puom ,
        uommstr.uom_no ,
        uommstr.stk_no ,
        uommstr_1.stk_no ,
        uommstr_1.stk_no ,
        uommstr_1.uom
ORDER BY pricemtx.part_code

Open in new window



MFG Name	MFG #	upc_code	alt_part2	part_code	part_desc	uom	avail	gros_wt	part_length	part_width	part_height	CASE QTY	Selling Units	min_ord_qty	LIST_PRICE	NET_PRICE	uom
AMERICAN NATURAL RESOURCES	AAC05915	091037059154	NULL	AAC05915	LG. SPLIT AMERICAN ANTLER DOG CHEW	EA	S	0.5	10.5	5	1	NULL	1	30	12.73	10.18	EA
AMERICAN NATURAL RESOURCES	AAC05915	091037059154	NULL	AAC05915	LG. SPLIT AMERICAN ANTLER DOG CHEW	EA	S	0.5	10.5	5	1	30	1	30	12.73	10.18	EA

Open in new window

gpsdhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
I believe it's just your syntax. Try:
SELECT DISTINCT
        apvndmstr.vend_name AS 'MFG Name' ,
        partmstr.alt_part1 AS 'MFG #' ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        pricemtx.part_code ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
        partmstr.part_height ,
        CASE WHEN uommstr_1.uom ='CA' THEN uommstr_1.stk_no ELSE NULL END AS 'CASE QTY'
        partmstr.royalty_code AS 'Selling Units' ,
        vendpart.min_ord_qty ,
        partmstr.part_price AS LIST_PRICE ,
        MIN(CASE WHEN pricemtx.price_meth = 'C'
                 THEN ROUND(( ( pricemtx.part_price * .01 )
                              * partmstr.cost_no6 ), 2)
                 WHEN pricemtx.price_meth = 'R' THEN pricemtx.part_price
                 WHEN pricemtx.price_meth = 'M'
                 THEN ROUND(( partmstr.part_price + ( pricemtx.part_price
                                                      * partmstr.part_price )
                              / 100 ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '%'
                 THEN ROUND(( ( 100 - pricemtx.part_disc ) * .01
                              * partmstr.part_price ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '$'
                      AND pricemtx.formula_id = 'BASELIST'
                 THEN ROUND(( partmstr.part_price - pricemtx.part_disc ), 2)
                 WHEN pricemtx.price_meth = 'B'
                      AND pricemtx.disc_type = '$'
                      AND pricemtx.formula_id <> 'BASELIST'
                 THEN ROUND(( ( pricemtx_1.part_price * .01 )
                              * ( partmstr.cost_no6 ) - pricemtx.part_disc ),
                            2)
            END) AS 'NET_PRICE' ,
        uommstr.uom
      
FROM    uommstr
        INNER JOIN pricemtx
        INNER JOIN partmstr ON pricemtx.part_code = partmstr.part_code ON uommstr.part_code = pricemtx.part_code
        LEFT OUTER JOIN uommstr AS uommstr_1 ON pricemtx.part_code = uommstr_1.part_code
        LEFT OUTER JOIN apvndmstr
        RIGHT OUTER JOIN vendpart ON apvndmstr.vend_code = vendpart.vend_code ON pricemtx.part_code = vendpart.part_code
        LEFT OUTER JOIN pricemtx AS pricemtx_1 ON pricemtx.formula_id = pricemtx_1.price_id
                                                  AND pricemtx.part_code = pricemtx_1.part_code
        LEFT OUTER JOIN custprice ON pricemtx.price_id = custprice.price_id
WHERE   ( custprice.cust_code = '8400343' )
        AND ( custprice.end_date > DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) )
        AND ( partmstr.avail = 'S' )
        AND ( uommstr.uom = 'EA' )
        AND ( partmstr.part_code = 'AAC05915' )
GROUP BY pricemtx.part_code ,
        custprice.price_id ,
        pricemtx.price_meth ,
        partmstr.alt_part1 ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
        partmstr.part_height ,
        vendpart.min_ord_qty ,
        partmstr.royalty_code ,
        vendpart.vend_name ,
        apvndmstr.vend_name ,
        apvndmstr.vend_name2 ,
        uommstr.uom ,
        partmstr.part_price ,
        partmstr.puom ,
        uommstr.uom_no ,
        uommstr.stk_no ,
        uommstr_1.stk_no ,
        uommstr_1.stk_no ,
        uommstr_1.uom
ORDER BY pricemtx.part_code

Open in new window

0
gpsdhAuthor Commented:
Comes up with 2 still.  One for the EA and CA in the uommstr table.

Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
First of all, if you're using GROUP BY, then you won't need the DISTINCT clause.
Second, match the GROUP BY columns with the SELECT column list:
SELECT 
	apvndmstr.vend_name AS 'MFG Name' ,
    partmstr.alt_part1 AS 'MFG #' ,
    partmstr.upc_code ,
    partmstr.alt_part2 ,
    pricemtx.part_code ,
    partmstr.part_desc ,
    partmstr.uom ,
    partmstr.avail ,
    partmstr.gros_wt ,
    partmstr.part_length ,
    partmstr.part_width ,
    partmstr.part_height ,
    'CASE QTY' = (CASE WHEN uommstr_1.uom ='CA' THEN uommstr_1.stk_no ELSE NULL END), 
    partmstr.royalty_code AS 'Selling Units' ,
    vendpart.min_ord_qty ,
    partmstr.part_price AS LIST_PRICE ,
    MIN(CASE WHEN pricemtx.price_meth = 'C'
                THEN ROUND(( ( pricemtx.part_price * .01 )
                            * partmstr.cost_no6 ), 2)
                WHEN pricemtx.price_meth = 'R' THEN pricemtx.part_price
                WHEN pricemtx.price_meth = 'M'
                THEN ROUND(( partmstr.part_price + ( pricemtx.part_price
                                                    * partmstr.part_price )
                            / 100 ), 2)
                WHEN pricemtx.price_meth = 'B'
                    AND pricemtx.disc_type = '%'
                THEN ROUND(( ( 100 - pricemtx.part_disc ) * .01
                            * partmstr.part_price ), 2)
                WHEN pricemtx.price_meth = 'B'
                    AND pricemtx.disc_type = '$'
                    AND pricemtx.formula_id = 'BASELIST'
                THEN ROUND(( partmstr.part_price - pricemtx.part_disc ), 2)
                WHEN pricemtx.price_meth = 'B'
                    AND pricemtx.disc_type = '$'
                    AND pricemtx.formula_id <> 'BASELIST'
                THEN ROUND(( ( pricemtx_1.part_price * .01 )
                            * ( partmstr.cost_no6 ) - pricemtx.part_disc ),
                        2)
        END) AS 'NET_PRICE' ,
        uommstr.uom
FROM    uommstr
        INNER JOIN pricemtx
        INNER JOIN partmstr ON pricemtx.part_code = partmstr.part_code ON uommstr.part_code = pricemtx.part_code
        LEFT OUTER JOIN uommstr AS uommstr_1 ON pricemtx.part_code = uommstr_1.part_code
        LEFT OUTER JOIN apvndmstr
			RIGHT OUTER JOIN vendpart ON apvndmstr.vend_code = vendpart.vend_code 
			ON pricemtx.part_code = vendpart.part_code
		LEFT OUTER JOIN pricemtx AS pricemtx_1 ON pricemtx.formula_id = pricemtx_1.price_id AND pricemtx.part_code = pricemtx_1.part_code
        LEFT OUTER JOIN custprice ON pricemtx.price_id = custprice.price_id
WHERE   custprice.cust_code = '8400343'
        AND custprice.end_date > DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
        AND partmstr.avail = 'S'
        AND uommstr.uom = 'EA'
        AND partmstr.part_code = 'AAC05915'
GROUP BY 
	apvndmstr.vend_name,
    partmstr.alt_part1,
    partmstr.upc_code,
    partmstr.alt_part2,
    pricemtx.part_code,
    partmstr.part_desc,
    partmstr.uom,
    partmstr.avail,
    partmstr.gros_wt,
    partmstr.part_length,
    partmstr.part_width,
    partmstr.part_height,
    CASE WHEN uommstr_1.uom ='CA' THEN uommstr_1.stk_no ELSE NULL END, 
    partmstr.royalty_code,
    vendpart.min_ord_qty,
    partmstr.part_price,
    uommstr.uom
ORDER BY pricemtx.part_code

Open in new window

About your issue, I'll guess that if you transform one or more of the LEFT OUTER JOIN to INNER JOIN, it might fix your issue.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
gpsdh, a feedback will be appreciated.
Cheers.
0
gpsdhAuthor Commented:
That did not work either.  I had to create another column in the database to make this work correctly.


Thanks!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gpsdhAuthor Commented:
I had to add another column to get it to work correctly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.