Anyone see a problem with this query?

futureDBA
futureDBA used Ask the Experts™
on
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;//

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Hello, I'd change to:

((2*TH_UNITS/PD_UNTCAS))

did you try that?

Author

Commented:
I am checking now, but why would that be different than what I have, it seems the same only backwards ?

Author

Commented:
that did not work, i get the same results
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Check your data and make sure the logic is correct for the data being returned.

That said, can't you simplify it (why use like with no wildcards):

SUM(
CASE WHEN THD_CUSTNO NOT IN (15,16) AND PM_PROD_BSCGRP<>2 THEN ((TH_UNITS/PD_UNTCAS)*(case when AND PM_PRDESC = 'ALP RN' then 2 else 1 end))  
) AS "Cases Sold Rebate",


If you can provide a simplified set of data and expected results, we can provide working SQL.

Author

Commented:
I think my problem is with the wild cards, I need anything that starts with ALP RN to be *2, so it could be something like ALP RN OT 523453 on the field that needs to get multiplied by 2, anything else that doesnt haveit, times 1. i am checking your last query now
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Then you need to add a wildcard....

PM_PRDESC like 'ALP RN%'

Author

Commented:
that did not work, as of now, my query looks like this

	PD_PRODNO AS PROD_NUM, 
	PM_PRDESC AS DESCRIPTION, 
	PM_VOLUME AS VOLUME, 
  SUM(CASE WHEN THD_CUSTNO NOT IN (15,16) THEN ROUND(th_units / pd_untcas) END)AS "Regular Cases",
  SUM(CASE WHEN THD_CUSTNO IN (15,16) THEN nvl(ROUND(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 THEN ((TH_UNITS/PD_UNTCAS)*(case when PM_PRDESC = 'ALP RN%' then 2 else 1 end)) END) AS "Cases Sold Rebate",
  PD_BRANCH

Open in new window




this is being converted from the following access query
	PD_PRODNO AS PROD_NUM, 
	PM_PRDESC AS DESCRIPTION, 
	PM_VOLUME AS VOLUME, 
	Sum(IIf(THD_CUSTNO Not In (15,16),CDbl(TH_UNITS/PD_UNTCAS),0)) AS REGULAR CASES, 
	Sum(IIf(THD_CUSTNO In (15,16),CDbl(TH_UNITS/PD_UNTCAS),0)) AS SAMPLE CASES, 
	Sum(IIf(THD_CUSTNO In (15,16),CDbl(TH_UNITS*PD_UNIT_COST),0)) AS SAMPLE REBATE, 
	Sum(IIf(THD_CUSTNO Not In (15,16) And PM_PROD_BSCGRP<>2 And PM_PRDESC Like ("*ALP RN*"),CDbl(TH_UNITS/PD_UNTCAS*2),
	IIf(THD_CUSTNO Not In (15,16) And PM_PROD_BSCGRP<>2 And PM_PRDESC Not Like ("*ALP RN*"),CDbl(TH_UNITS/PD_UNTCAS),0))) AS REBATE FOR REGULAR CASES, 
	Sum(IIf(THD_CUSTNO Not In (15,16) And PM_PROD_BSCGRP=2,CDbl(TH_UNITS*PD_UNIT_COST),0)) AS REBATE FOR FREE CASES, 
	PD_BRANCH

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>PM_PRDESC = 'ALP RN%'

Look at what I posted...  if you need wildcards, you need LIKE not '='.

PM_PRDESC like 'ALP RN%'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial