Solved

Access to Oracle SQL Conversion (Advanced)

Posted on 2012-04-06
23
410 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 76

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 76

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
 
LVL 76

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 76

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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 31

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 31

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 31

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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now