Access to Oracle SQL Conversion (Advanced)

I am trying to convert an access query to oracle but am running into problems, normally I have been able to do these on my own but the queries are getting more difficult and I am only diving into oracle now. IF someone could please help it would be greatly appreciated... below i listed conversions of tables and columns,, the working Access SQL query and what i have so far for oracle.. when i run the query it tells me i am missing a parenthesis, but i have looked over and can not find it
----------------
Tables

Access                          Oracle
ONE WEEK TOTALS            ARIEL_ONE_WEEK_SALES
ALL COMMISIONS            ARIEL_ALL_COMMISSIONS

column names anything in access with a # in oracle becomes a _NUM
--------------------------------------
ACCESS SQL QUERY (Working)
SELECT [ONE WEEK TOTALS].[PROD#], [ONE WEEK TOTALS].PM_PRDESC AS DESCRIPTION, 
	[ONE WEEK TOTALS].PM_VOLUME AS VOLUME, 
	-Int(-(Sum(IIf([CUST#]<>16,([UNITS]/[PD_UNTCAS]))))) AS [DELIVERED CASES], 
	IIf((Sum(IIf([CUST#]=16,[UNITS]/[PD_UNTCAS]))) Is Null,0,(Sum(IIf([CUST#]=16,[UNITS]/[PD_UNTCAS])))) AS [SAMPLE CASES], 
	[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 [ONE WEEK TOTALS] INNER JOIN [ALL COMMISSIONS] ON [ONE WEEK TOTALS].[PROD#] = [ALL COMMISSIONS].[PROD#]

GROUP BY [ONE WEEK TOTALS].[PROD#], [ONE WEEK TOTALS].PM_PRDESC, [ONE WEEK TOTALS].PM_VOLUME, [ONE WEEK TOTALS].PM_BRAND_CODE, [ALL COMMISSIONS].Rebate, [ALL COMMISSIONS].SAMPLE_REBATE

HAVING ((([ONE WEEK TOTALS].PM_BRAND_CODE)=20));

Open in new window

-----------------------------------------

ORACLE SQL QUERY (Not Working)

SELECT ARIEL_ONE_WEEK_SALES.PROD_NUM, ARIEL_ONE_WEEK_SALES.PM_PRDESC AS DESCRIPTION, 
  ARIEL_ONE_WEEK_SALES.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_COMMISIONS.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_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
HAVING (((ARIEL_ONE_WEEK_SALES.PM_BRAND_CODE)=20));

Open in new window

gnivkorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
gnivkorAuthor Commented:
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

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
Same error?
0
gnivkorAuthor Commented:
yes

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 3 Column: 26
0
slightwv (䄆 Netminder) Commented:
>>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

Open in new window

0
gnivkorAuthor Commented:
Same Error :-\
0
slightwv (䄆 Netminder) Commented:
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

Open in new window

0
gnivkorAuthor Commented:
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)
0
gnivkorAuthor Commented:
I am running the query in SQL Developer and i have tried it from APEX SQL command
0
slightwv (䄆 Netminder) Commented:
Thanks for the info.  Heading home for the evening.  I'll take a look at this a little later this evening.
0
gnivkorAuthor Commented:
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

Open in new window

0
awking00Information Technology SpecialistCommented:
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
0
awking00Information Technology SpecialistCommented:
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
0
awking00Information Technology SpecialistCommented:
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
0
gnivkorAuthor Commented:
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
0
gnivkorAuthor Commented:
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

Open in new window

0
slightwv (䄆 Netminder) Commented:
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
/

Open in new window

0
gnivkorAuthor Commented:
SOLD_CASES is suppose to be UNITS / PD_UNTCAS
0
gnivkorAuthor Commented:
you are right about sold_cases, something got lost in translation, i am going over it one by one now
0
slightwv (䄆 Netminder) Commented:
>>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;
0
gnivkorAuthor Commented:
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));
                                  

Open 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));

Open in new window



The error I am getting:       ORA-00904: "REBATE_FOR_SAMPLES": invalid identifier
0
slightwv (䄆 Netminder) Commented:
Since you have opened a duplicate question there is no need to continue here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.