Access to Oracle SQL Conversion (Advanced)

IIF isn't an Oracle function.

Either use CASE or the older DECODE:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions004.htm

Missed the INT... also not Oracle.  Also no spaces in a column alias.

First for deliverd cases would be something like:
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,

If you want whole numbers either TRUNC or ROUND.

i have gotten it down to this, still now working

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,  
  CASE((Sum(CASE(CUST_NUM=16,UNITS/PD_UNTCAS))) Is Null,0,(Sum(CASE(CUST_NUM=16,UNITS/PD_UNTCAS)))) AS SAMPLE_CASES, 
  ARIEL_ALL_COMMISIONS.Rebate, DELIVERED_CASES*REBATE AS SOLD CASES_REBATE, 
  CASE(SAMPLE_CASES*SAMPLE_REBATE Is Null,0,SAMPLE_CASES*SAMPLE_REBATE) AS REBATE FOR SAMPLES, 
  SOLD_CASES REBATE+REBATE FOR SAMPLES AS TOTAL_REBATE
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISIONS.Rebate, ARIEL_ALL_COMMISIONS.SAMPLE_REBATE

Select allOpen in new window

Same error?

yes

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 3 Column: 26

>>CASE((Sum(CASE(CUST_NUM=16,UNITS/PD_UNTCAS))) Is Null,0,(Sum(CASE(CUST_NUM=16,UNITS/PD_UNTCAS)))) AS SAMPLE_CASES,
 

These lines are all messed up.  Looks like you are trying to use DECODE syntax with a CASE keyword.

Not sure if the logic is correct but try:

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,  
  CASE when Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) Is Null then 0 else Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) end AS SAMPLE_CASES, 
  ARIEL_ALL_COMMISIONS.Rebate, DELIVERED_CASES*REBATE AS SOLD CASES_REBATE, 
  CASE when SAMPLE_CASES*SAMPLE_REBATE Is Null then 0 else SAMPLE_CASES*SAMPLE_REBATE end AS REBATE FOR SAMPLES, 
  SOLD_CASES REBATE+REBATE FOR SAMPLES AS TOTAL_REBATE
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISIONS.Rebate, ARIEL_ALL_COMMISIONS.SAMPLE_REBATE

Select allOpen in new window

Same Error :-\

What tool are you running that SQL in?

I don't have your tables so I cannot really run this.

I did get a syntax error in a couple of places where there were spaces in column names/aliases.

I'm now down to the table or view does not exist.  This is as far as I can take it.

I suggest you select a single value then add additional values in until it breaks.  Then the last value you added is the issue.

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,  
  CASE when Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) Is Null then 0 else Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) end AS SAMPLE_CASES, 
  ARIEL_ALL_COMMISIONS.Rebate, DELIVERED_CASES*REBATE AS SOLD_CASES_REBATE, 
  CASE when SAMPLE_CASES*SAMPLE_REBATE Is Null then 0 else SAMPLE_CASES*SAMPLE_REBATE end AS REBATE_FOR_SAMPLES, 
  SOLD_CASES_REBATE+REBATE_FOR_SAMPLES AS TOTAL_REBATE
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISIONS.Rebate, ARIEL_ALL_COMMISIONS.SAMPLE_REBATE

Select allOpen in new window

These are the columns for "ARIEL_ONE_WEEKS_SALES  
    "CUST_NUM"      NUMBER(14,0),
    "CUSTOMER_NAME" VARCHAR2(50 BYTE),
    "ADDRESS"       VARCHAR2(50 BYTE),
    "PHONE"         VARCHAR2(20 BYTE),
    "SALESMAN"      VARCHAR2(30 BYTE),
    "SLSMAN_NUM"    NUMBER(5,0),
    "PROD_NUM"      VARCHAR2(10 BYTE),
    "UNITS"         VARCHAR2(6 BYTE),
    "TH_DOLRS"      NUMBER(20,6),
    "TH_PRICE"      NUMBER(20,6),
    "CM_CHNID"      VARCHAR2(3 BYTE),
    "ID_FLDNUM"     NUMBER(3,0),
    "CITY"          VARCHAR2(28 BYTE),
    "STATE"         VARCHAR2(2 BYTE),
    "ZIP"           VARCHAR2(10 BYTE),
    "PD_BRANCH"     NUMBER(5,0),
    "PM_PRDESC"     VARCHAR2(30 BYTE),
    "PM_VOLUME"     VARCHAR2(15 BYTE),
    "PD_UNTCAS"     VARCHAR2(4 BYTE),
    "S_DATE"        VARCHAR2(11 BYTE),
    "PM_BRAND_CODE" NUMBER(6,0)
-------------

these are the columns for ARIEL_ALL_COMMISIONS
   "PROD_NUM"      VARCHAR2(10 BYTE),
    "DESCRIPTION"   VARCHAR2(50 BYTE),
    "S_SIZE"        VARCHAR2(10 BYTE),
    "GRISTEDES"     NUMBER(20,6),
    "BEST_YET"      NUMBER(20,6),
    "STOP_AND_SHOP" NUMBER(20,6),
    "AOM"           NUMBER(20,6),
    "KMART"         NUMBER(20,6),
    "CLASS"         VARCHAR2(10 BYTE),
    "REBATE"        NUMBER(20,6),
    "SAMPLE_REBATE" NUMBER(20,6)

I am running the query in SQL Developer and i have tried it from APEX SQL command

Thanks for the info.  Heading home for the evening.  I'll take a look at this a little later this evening.

I got the same Table or view does not exist error that you got and fixed it (commissions was mispelled) after i fixed that error..

now I got the error


ORA-00904: "REBATE_FOR_SAMPLES": invalid identifier

using

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,  
  CASE when Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) Is Null then 0 else Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) end AS SAMPLE_CASES, 
  ARIEL_ALL_COMMISSIONS.Rebate, DELIVERED_CASES*REBATE AS SOLD_CASES_REBATE, 
  CASE when SAMPLE_CASES*SAMPLE_REBATE Is Null then 0 else SAMPLE_CASES*SAMPLE_REBATE end AS REBATE_FOR_SAMPLES, 
  SOLD_CASES_REBATE+REBATE_FOR_SAMPLES AS TOTAL_REBATE
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISSIONS.Rebate, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE

Select allOpen in new window

A number of issues -
>>DELIVERED_CASES*REBATE AS SOLD CASES_REBATE<<
DELIVERED_CASES*REBATE AS SOLD_CASES_REBATE ==> Must be one word

This -
>>CASE when Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) Is Null
        then 0
        else Sum(CASE when CUST_NUM=16 then UNITS/PD_UNTCAS end) end AS SAMPLE_CASES<<

Should be this -
case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases

This -
>>CASE when SAMPLE_CASES*SAMPLE_REBATE Is Null then 0
       else SAMPLE_CASES*SAMPLE_REBATE
  end AS REBATE FOR SAMPLES<<

Should be this -
nvl(sample_cases*sample_rebate,0) as rebate_for_samples ==> Again, must be one word
However, you can not use the alias "sample_cases" so you need to redefine it -
nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end)*sample_rebate as rebate_for_samples

Not sure what you mean here but I think you're missing an operator
>>SOLD_CASES REBATE+REBATE FOR SAMPLES AS TOTAL_REBATE<<

Should probably be
sold_cases*(rebate + nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) else UNITS/PD_UNTCAS end,0)) as total_rebate
Again not using any alias

Sorry, rebates_for_samples needs another nvl wrapped around it.

nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end)*sample_rebate),0) as rebates_for_samples

And total rebates_should be -
sold_cases*(rebate + nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end)*sample_rebate) as total_rebate

this just got very confusing, give me a second while i try to wrap my head around this, ill post the query i come up with in a few minutes based off of this information

im getting invalid number of arguments

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,  
  case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases, 
  ARIEL_ALL_COMMISSIONS.Rebate, DELIVERED_CASES*REBATE AS SOLD_CASES_REBATE, 
  nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end)*sample_rebate),0) as rebates_for_samples, 
  sold_cases*(rebate + nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end)*sample_rebate) as total_rebate,
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISSIONS.Rebate, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE

Select allOpen in new window

You are tackling to much at once.  Please select one column at a time.

Some of your NVL calls are off.

Also, once I created your tables and fixed the syntax issue with the NVL calls, there is a missing column.  The SELECT uses a SOLD_CASES column.  That column does not exist in the tables.

Here is what I have now:

create table ARIEL_ONE_WEEK_SALES  (
    CUST_NUM      NUMBER(14,0),
    CUSTOMER_NAME VARCHAR2(50 BYTE),
    ADDRESS       VARCHAR2(50 BYTE),
    PHONE         VARCHAR2(20 BYTE),
    SALESMAN      VARCHAR2(30 BYTE),
    SLSMAN_NUM    NUMBER(5,0),
    PROD_NUM      VARCHAR2(10 BYTE),
    UNITS         VARCHAR2(6 BYTE),
    TH_DOLRS      NUMBER(20,6),
    TH_PRICE      NUMBER(20,6),
    CM_CHNID      VARCHAR2(3 BYTE),
    ID_FLDNUM     NUMBER(3,0),
    CITY          VARCHAR2(28 BYTE),
    STATE         VARCHAR2(2 BYTE),
    ZIP           VARCHAR2(10 BYTE),
    PD_BRANCH     NUMBER(5,0),
    PM_PRDESC     VARCHAR2(30 BYTE),
    PM_VOLUME     VARCHAR2(15 BYTE),
    PD_UNTCAS     VARCHAR2(4 BYTE),
    S_DATE        VARCHAR2(11 BYTE),
    PM_BRAND_CODE NUMBER(6,0)
)
/

create table ARIEL_ALL_COMMISSIONS(
   PROD_NUM      VARCHAR2(10 BYTE),
    DESCRIPTION   VARCHAR2(50 BYTE),
    S_SIZE        VARCHAR2(10 BYTE),
    GRISTEDES     NUMBER(20,6),
    BEST_YET      NUMBER(20,6),
    STOP_AND_SHOP NUMBER(20,6),
    AOM           NUMBER(20,6),
    KMART         NUMBER(20,6),
    CLASS         VARCHAR2(10 BYTE),
    REBATE        NUMBER(20,6),
    SAMPLE_REBATE NUMBER(20,6)
)
/






SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
  Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,  
  case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases, 
  ARIEL_ALL_COMMISSIONS.Rebate, DELIVERED_CASES*REBATE AS SOLD_CASES_REBATE, 
  nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) as rebates_for_samples, 
  sold_cases*(rebate + nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate) as total_rebate
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISSIONS.Rebate, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE
/

Select allOpen in new window

SOLD_CASES is suppose to be UNITS / PD_UNTCAS

you are right about sold_cases, something got lost in translation, i am going over it one by one now

>>i am going over it one by one now

In case this is what you are trying to do, another FYI about Oracle (not sure about other DBs):  If you create a column alias, you cannot use it at the same level.

For example, the following will fail:
select 'Hello' myStr, myStr || ' World' from dual;

okay, after going over this over and over again... this is where I am at...


This is the ACCESS Query

SELECT ARIEL_ONE_WEEK_TOTALS.PROD_NUM, ARIEL_ONE_WEEK_TOTALS.PM_PRDESC AS DESCRIPTION, ARIEL_ONE_WEEK_TOTALS.PM_VOLUME AS VOLUME, 
	-Int(-(Sum(IIf(CUST_NUM<>16,(UNITS/PD_UNTCAS))))) AS DELIVERED CASES, 
	IIf((Sum(IIf(CUST_NUM=16,UNITS/PD_UNTCAS))) Is Null,0,(Sum(IIf(CUST_NUM=16,UNITS/PD_UNTCAS)))) AS SAMPLE CASES, 
	ARIEL_ALL_COMMISSIONS.Rebate, DELIVERED CASES*REBATE AS SOLD CASES REBATE, IIf(SAMPLE CASES*SAMPLE_REBATE Is Null,0,SAMPLE CASES*SAMPLE_REBATE) AS REBATE FOR SAMPLES, 
	SOLD CASES REBATE+REBATE FOR SAMPLES AS TOTAL REBATE
FROM ARIEL_ONE_WEEK_TOTALS INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_TOTALS.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_TOTALS.PROD_NUM, ARIEL_ONE_WEEK_TOTALS.PM_PRDESC, ARIEL_ONE_WEEK_TOTALS.PM_VOLUME, ARIEL_ONE_WEEK_TOTALS.PM_BRAND_CODE, ARIEL_ALL_COMMISSIONS.Rebate, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE
HAVING (((ARIEL_ONE_WEEK_TOTALS.PM_BRAND_CODE)=20));
                                  

Select allOpen in new window

This is what I have for oracle

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, ARIEL_ONE_WEEK_SALES.PM_VOLUME AS VOLUME, 
	Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end) AS DELIVERED_CASES,
	case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end as sample_cases,
	nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) as rebate_for_samples, 
	SOLD_CASES_REBATE+REBATE_FOR_SAMPLES AS TOTAL_REBATE
FROM ARIEL_ONE_WEEK_SALES INNER JOIN ARIEL_ALL_COMMISSIONS ON ARIEL_ONE_WEEK_SALES.PROD_NUM = ARIEL_ALL_COMMISSIONS.PROD_NUM
GROUP BY ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC, ARIEL_ONE_WEEK_SALES.PM_VOLUME, ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE, ARIEL_ALL_COMMISSIONS.Rebate, ARIEL_ALL_COMMISSIONS.SAMPLE_REBATE
HAVING (((ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE)=20));

Select allOpen in new window

The error I am getting:       ORA-00904: "REBATE_FOR_SAMPLES": invalid identifier