Link to home
Start Free TrialLog in
Avatar of futureDBA
futureDBA

asked on

Anyone see a problem with this query?

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

Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Hello, I'd change to:

((2*TH_UNITS/PD_UNTCAS))

did you try that?
Avatar of futureDBA
futureDBA

ASKER

I am checking now, but why would that be different than what I have, it seems the same only backwards ?
that did not work, i get the same results
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.
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
Then you need to add a wildcard....

PM_PRDESC like 'ALP RN%'
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

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial