Solved

[Advanced] MS Access SQL to ORACLE Conversion - Part 2

Posted on 2012-04-08
17
373 Views
Last Modified: 2012-04-09
I have a previous thread which is not closed but it was getting very cluttered. I am trying to migrate the SQL from access to Oracle...


Here is my Access Code
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

Open in new window


Here is My ORACLE SQL code
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

Open in new window


These are my tables
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)
)
/

Open in new window


when i try to run this query, i get

	ORA-00904: "REBATE_FOR_SAMPLES": invalid identifier 

Open in new window



If there is anything else that you need to understand about my database structure or what my goal is, please let me know and i will reply with relevant data
0
Comment
Question by:gnivkor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
17 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37822390
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

Open in new window


- the error is on this line: SOLD_CASES_REBATE + REBATE_FOR_SAMPLES AS TOTAL_REBATE
- i am not sure which column does SOLD_CASES_REBATE come from but i assume maybe its from those tables. for the error line I've mentioned, you can also do the following:

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

Open in new window

0
 

Author Comment

by:gnivkor
ID: 37822421
when i tried that i get an invalid identifier for sold cases rebate
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37822429
- can you share what is the calculation for sold_cases_rebate? then replace sold_cases_rebate with that calculation in the same line:

<replace the SOLD_CASES_REBATE calculation here> + (nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0))

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!

 

Author Comment

by:gnivkor
ID: 37823130
sorry, i was not trying to cheat experts out of points. I was just trying to get a bit more organized since the other thread was long and confusing with no solution in sight..

OP_Zaharin.

what I am trying to achieve in the query is the follow


delivered cases = units/pd_untcas where cust_num does not = 16
sample cases = units/pd_untcas where cust_num = 16
sold cases = delivered cases * rebate
rebate for samples = sample_cases * sample_rebate
total rebate = sold cases rebate + rebate for samples
0
 

Author Comment

by:gnivkor
ID: 37823205
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
, delivered_cases*rebate as sold_cases_rebate
, sample_cases*ARIEL_ALL_COMMISSIONS.sample_rebate 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
 

Author Comment

by:gnivkor
ID: 37823206
that's where i am at now
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823254
>>was long and confusing with no solution in sight

Some Experts do not work weekends.  If a question is not receiving the attention you feel it should, the correct thing to do is click Request Attention and a Moderator will send out a call for help.

>>ORA-00904: "REBATE_FOR_SAMPLES": invalid identifier

I mentioned this in your other question.  You cannot alias a column and use the alias at the same level.

http://www.experts-exchange.com/Database/Oracle/Q_27665464.html?#a37817760

For example:
select 1 number_one, 2 + number_one from dual;

You would need to do this:
select 2 + number_one from (
select 1 number_one from dual
);


Or just repeat the formula everwhere.
0
 

Author Comment

by:gnivkor
ID: 37823302
i understood that but I do not see where I am using an alias at the same level unless my understanding of what aliasing a column is off

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
                                            

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823386
Open it in noepad or any editor ans search for the term.


, 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
0
 

Author Comment

by:gnivkor
ID: 37823414
okay, so based off of your recommendation i am just repeating the formula, i ended up with

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
,   Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end)*rebate as sold_cases_rebate
, nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) as rebate_for_samples
,   Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end)*rebate + nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) 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



now i am getting a

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 6 Column: 12
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 37823441
I believe in any database, you need to group by all non-aggregate columns.

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
,   Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end)*rebate as sold_cases_rebate
, nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) as rebate_for_samples
,   Sum(case when CUST_NUM<>16 then (UNITS/PD_UNTCAS) end)*rebate + nvl(nvl(case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end,0)*sample_rebate,0) 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
, case when cust_num = 16 then nvl(UNITS/PD_UNTCAS,0) end
;
0
 

Author Comment

by:gnivkor
ID: 37823660
almost there, except the math is all wrong but at least it is a valid sql query

some of my rows are being returned double with different values..

 i also need any null values on aliases to be returned as 0

some of my values are being returned as "462.083333333333333333333333333333333333" instead of 463.
....
give me a sec and i will give you a data values for both tables and the expected result that I get in access
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823670
I don't want to write all this for you.  It is better that you learn how to resolve this yourself.

>>some of my values are being returned as "462.083333333333333333333333333333333333" instead of 463

Look up ROUND and/or CEIL.  Since 462.08 does not round up to 463, I suggest CEIL:

ceil(462.083333333333333333333333333333333333)

>>i also need any null values on aliases to be returned as 0

You already have the NVL syntax.  Just us it in the correct places.

The format is NVL(value_to_check,0).  value_to_check can be any expression/value.
0
 

Author Closing Comment

by:gnivkor
ID: 37823751
thank you
0
 
LVL 32

Expert Comment

by:awking00
ID: 37823998
Gee, I never even got a peek at this duplicate. I guess my pointing out the need to use nvl as opposed to the way the case statements were initially applied didn't have any merit :-(
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37824032
awking00,

If you have a problem with how a question has been closed you can contact one of the Zone Advisors or click Request Attention for moderator intervention.  I would intervene but I'm involved in the question as a participating Expert.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.

726 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