Solved

Access to Oracle SQL Conversion (Advanced)

Posted on 2012-04-06
23
420 Views
Last Modified: 2012-04-09
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

0
Comment
Question by:gnivkor
  • 11
  • 9
  • 3
23 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37816860
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37816868
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
 

Author Comment

by:gnivkor
ID: 37817085
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817098
Same error?
0
 

Author Comment

by:gnivkor
ID: 37817102
yes

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817126
>>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
 

Author Comment

by:gnivkor
ID: 37817176
Same Error :-\
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817223
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
 

Author Comment

by:gnivkor
ID: 37817269
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
 

Author Comment

by:gnivkor
ID: 37817272
I am running the query in SQL Developer and i have tried it from APEX SQL command
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817290
Thanks for the info.  Heading home for the evening.  I'll take a look at this a little later this evening.
0
 

Author Comment

by:gnivkor
ID: 37817295
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
 
LVL 32

Expert Comment

by:awking00
ID: 37817364
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
 
LVL 32

Expert Comment

by:awking00
ID: 37817376
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
 
LVL 32

Expert Comment

by:awking00
ID: 37817390
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
 

Author Comment

by:gnivkor
ID: 37817439
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
 

Author Comment

by:gnivkor
ID: 37817468
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817512
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
 

Author Comment

by:gnivkor
ID: 37817541
SOLD_CASES is suppose to be UNITS / PD_UNTCAS
0
 

Author Comment

by:gnivkor
ID: 37817693
you are right about sold_cases, something got lost in translation, i am going over it one by one now
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817760
>>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
 

Author Comment

by:gnivkor
ID: 37818312
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 37823093
Since you have opened a duplicate question there is no need to continue here.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question