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)) "

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;//
```

Oracle Database

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

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

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.

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)*(cas

) 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%'

PM_PRDESC like 'ALP RN%'

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

this is being converted from the following access query

```
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
```

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
```

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

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
((2*TH_UNITS/PD_UNTCAS))

did you try that?