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

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

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

ASKER

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.

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.

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

Then you need to add a wildcard....

PM_PRDESC like 'ALP RN%'

PM_PRDESC like 'ALP RN%'

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

((2*TH_UNITS/PD_UNTCAS))

did you try that?