[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-04-08
17
Medium Priority
?
383 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
  • 8
  • 5
  • 2
  • +1
16 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 78

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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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 78

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 78

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

872 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