Solved

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

Posted on 2012-04-08
17
372 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Title # Comments Views Activity
ER Diagram 3 50
oracle differnce between two timestamps 5 47
return value in based on value passed 6 46
format dd/mm/yyyy parameter 16 56
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

740 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