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

Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
Gerwin Jansen

Hello, I'd change to:

((2*TH_UNITS/PD_UNTCAS))

did you try that?
futureDBA

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

ASKER
that did not work, i get the same results
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

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.
futureDBA

ASKER
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
slightwv (䄆 Netminder)

Then you need to add a wildcard....

PM_PRDESC like 'ALP RN%'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
futureDBA

ASKER
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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question