[Advanced] MS Access SQL to ORACLE Conversion - Part 2
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
Here is My ORACLE SQL code
These are my tables
when i try to run this query, i get
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
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_REBATEHere 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_REBATEThese 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)
)
/when i try to run this query, i get
ORA-00904: "REBATE_FOR_SAMPLES": invalid identifier 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
ASKER
when i tried that i get an invalid identifier for sold cases rebate
- 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))ASKER
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
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
ASKER
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
ASKER
that's where i am at now
>>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.
https://www.experts-exchange.com/questions/27665464/Access-to-Oracle-SQL-Conversion-Advanced.html?&anchorAnswerId=37817760#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.
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.
https://www.experts-exchange.com/questions/27665464/Access-to-Oracle-SQL-Conversion-Advanced.html?&anchorAnswerId=37817760#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.
ASKER
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 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
, 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
ASKER
okay, so based off of your recommendation i am just repeating the formula, i ended up with
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
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;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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.083333333333333333333
....
give me a sec and i will give you a data values for both tables and the expected result that I get in access
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.083333333333333333333
....
give me a sec and i will give you a data values for both tables and the expected result that I get in access
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.083333333333333333333
Look up ROUND and/or CEIL. Since 462.08 does not round up to 463, I suggest CEIL:
ceil(462.08333333333333333
>>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.
>>some of my values are being returned as "462.083333333333333333333
Look up ROUND and/or CEIL. Since 462.08 does not round up to 463, I suggest CEIL:
ceil(462.08333333333333333
>>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.
ASKER
thank you
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 :-(
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.
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.
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:
Open in new window