troubleshooting Question

Anyone see a problem with this query?

Avatar of futureDBA
futureDBA asked on
Oracle Database
8 Comments1 Solution325 ViewsLast Modified:
Anyone see a problem with this query?

Specifically with

  SUM(CASE WHEN THD_CUSTNO NOT IN (15,16) AND PM_PROD_BSCGRP<>2 AND PM_PRDESC LIKE 'ALP RN' THEN ((TH_UNITS/PD_UNTCAS)*(2))  
           WHEN THD_CUSTNO NOT IN (15,16) AND PM_PROD_BSCGRP<>2 AND PM_PRDESC NOT LIKE 'ALP RN' THEN (TH_UNITS/PD_UNTCAS) END) AS "Cases Sold Rebate",


It seems that its still multiplying by 1 where I have "((TH_UNITS/PD_UNTCAS)*(2))  "

SELECT 
	PD_PRODNO AS PROD_NUM, 
	PM_PRDESC AS DESCRIPTION, 
	PM_VOLUME AS VOLUME, 
  SUM(CASE WHEN THD_CUSTNO NOT IN (15,16) THEN FLOOR(th_units / pd_untcas) END)AS "Regular Cases",
  SUM(CASE WHEN THD_CUSTNO IN (15,16) THEN nvl(FLOOR(TH_UNITS/PD_UNTCAS),0) END) AS "Sample Cases",
  SUM(CASE WHEN THD_CUSTNO IN (15,16) THEN nvl((TH_UNITS * PD_UNIT_COST),0) END) AS "Sample Rebate",
  SUM(CASE WHEN THD_CUSTNO NOT IN (15,16) AND PM_PROD_BSCGRP<>2 AND PM_PRDESC LIKE 'ALP RN' THEN ((TH_UNITS/PD_UNTCAS)*(2))  
           WHEN THD_CUSTNO NOT IN (15,16) AND PM_PROD_BSCGRP<>2 AND PM_PRDESC NOT LIKE 'ALP RN' THEN (TH_UNITS/PD_UNTCAS) END) AS "Cases Sold Rebate",
  PD_BRANCH
FROM (PRODTL@"DB2.REMOTE" 
	INNER JOIN rapfiles.PROMAS@"DB2.REMOTE" ON PRODTL.PD_PRODNO = PROMAS.PM_PRODNO) 
	INNER JOIN THSDTL@"DB2.REMOTE" ON PROMAS.PM_PRODNO = THSDTL.TH_PRODNO
WHERE 
	(((THSDTL.THD_TRANMO)=4) 
		AND ((THSDTL.THD_TRANYR)=12) 
		AND ((PROMAS.PM_BRAND_CODE)=20))
GROUP BY 
			PRODTL.PD_PRODNO, 
			PROMAS.PM_PRDESC, 
			PROMAS.PM_VOLUME, 
			PRODTL.PD_BRANCH
HAVING (((PRODTL.PD_BRANCH)=33))
ORDER BY PRODTL.PD_PRODNO;//
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros