futureDBA
asked on
Converting MS ACCESS SQL to Oracle SQL [Advanced]
I need help with this portion of the query
this is the entire query
IIf([CM_PRIMRY_DLVSEQ_1]>0,"2","") & IIf([CM_PRIMRY_DLVSEQ_2]>0,"3","") & IIf([CM_PRIMRY_DLVSEQ_3]>0,"4","") & IIf([CM_PRIMRY_DLVSEQ_4]>0,"5","")
& IIf([CM_PRIMRY_DLVSEQ_5]>0,"6","") & IIf([CM_PRIMRY_DLVSEQ_6]>0,"7","") & IIf([CM_PRIMRY_DLVSEQ_1]>0,"2","")
& IIf([CM_PRIMRY_DLVSEQ_2]>0,"3","") & IIf([CM_PRIMRY_DLVSEQ_3]>0,"4","") & IIf([CM_PRIMRY_DLVSEQ_4]>0,"5","")
& IIf([CM_PRIMRY_DLVSEQ_5]>0,"6","") & IIf([CM_PRIMRY_DLVSEQ_6]>0,"7","") AS DELDAZE,
Weekday(Now()) AS TODAY,
Switch([TODAY]<Left([DELDAZE],1),Left([DELDAZE],1),[TODAY]>=Left([DELDAZE],1)
And [TODAY]<Mid([DELDAZE],2,1),Mid([DELDAZE],2,1),[TODAY]>Left([DELDAZE],1)
And [TODAY]>=Mid([DELDAZE],2,1)
And [TODAY]<>Mid([DELDAZE],3,1),Mid([DELDAZE],3,1),[TODAY]>Left([DELDAZE],1)
And [TODAY]>Mid([DELDAZE],2,1)
And [TODAY]>=Mid([DELDAZE],3,1),Left([DELDAZE],1),[TODAY]=Mid([DELDAZE],4,1),Left([DELDAZE],1)) AS NOTHING,
IIf(([NOTHING]-[TODAY])<=0,([NOTHING]-[TODAY]+7),[NOTHING]-[TODAY]) AS [DAYS TIL NEXT],
[DELIVERY AFTER NEXT]-[NEXT DELIVERY] AS [DAYS TIL AFTER NEXT], Date()+[DAYS TIL NEXT] AS [NEXT DELIVERY],
Mid([DELDAZE],CDbl(InStr(1,[DELDAZE],[NOTHING]))+1,1) AS [AFTER NEXT],
[NEXT DELIVERY]+IIf(([AFTER NEXT]-[NOTHING])<=0,([AFTER NEXT]-[NOTHING]+7),[AFTER NEXT]-[NOTHING]) AS [DELIVERY AFTER NEXT]
this is the entire query
SELECT
db2003_CUSMAS.CM_CUSTNO AS [Cust#],
db2003_CUSMAS.CM_STORNO AS [Store#],
[ORIGINAL ROUTES].FirstOfTH_ROUTNO AS [FIRST ROUTE],
db2003_CUSMAS.CM_CUSTMR_NAME AS Name,
db2003_CUSMAS.CM_PRIMRY_ROUTNO AS Route,
IIf([CM_PRIMRY_DLVSEQ_1]>0,"M",".") AS Mon,
IIf([CM_PRIMRY_DLVSEQ_2]>0,"T",".") AS Tue,
IIf([CM_PRIMRY_DLVSEQ_3]>0,"W",".") AS Wed,
IIf([CM_PRIMRY_DLVSEQ_4]>0,"Th",".") AS Thur,
IIf([CM_PRIMRY_DLVSEQ_5]>0,"F",".") AS Fri,
IIf([CM_PRIMRY_DLVSEQ_6]>0,"S",".") AS Sat,
[COOLER INFO].Notes,
[COOLER INFO].COOLER,
[COOLER INFO].DOORS,
[COOLER INFO].[Reset Date],
db2003_CUSMAS.CM_CUSTMR_NAME,
db2003_CUSMAS.CM_CUSTMR_ADDR_1,
db2003_CUSMAS.CM_CUSTMR_CITY,
db2003_CUSMAS.CM_CUSTMR_STAT,
db2003_CUSMAS.CM_CUSTMR_ZIPCOD,
db2003_CUSMAS.CM_PHNAREA,
db2003_CUSMAS.CM_PHNNBR,
db2003_CUSMAS.CM_SLSMAN,
IIf([CM_PRIMRY_DLVSEQ_1]>0,"2","") & IIf([CM_PRIMRY_DLVSEQ_2]>0,"3","") & IIf([CM_PRIMRY_DLVSEQ_3]>0,"4","") & IIf([CM_PRIMRY_DLVSEQ_4]>0,"5","") & IIf([CM_PRIMRY_DLVSEQ_5]>0,"6","") & IIf([CM_PRIMRY_DLVSEQ_6]>0,"7","") & IIf([CM_PRIMRY_DLVSEQ_1]>0,"2","") & IIf([CM_PRIMRY_DLVSEQ_2]>0,"3","") & IIf([CM_PRIMRY_DLVSEQ_3]>0,"4","") & IIf([CM_PRIMRY_DLVSEQ_4]>0,"5","") & IIf([CM_PRIMRY_DLVSEQ_5]>0,"6","") & IIf([CM_PRIMRY_DLVSEQ_6]>0,"7","") AS DELDAZE,
Weekday(Now()) AS TODAY,
Switch([TODAY]<Left([DELDAZE],1),Left([DELDAZE],1),[TODAY]>=Left([DELDAZE],1) And [TODAY]<Mid([DELDAZE],2,1),Mid([DELDAZE],2,1),[TODAY]>Left([DELDAZE],1) And [TODAY]>=Mid([DELDAZE],2,1) And [TODAY]<>Mid([DELDAZE],3,1),Mid([DELDAZE],3,1),[TODAY]>Left([DELDAZE],1) And [TODAY]>Mid([DELDAZE],2,1) And [TODAY]>=Mid([DELDAZE],3,1),Left([DELDAZE],1),[TODAY]=Mid([DELDAZE],4,1),Left([DELDAZE],1)) AS NOTHING, IIf(([NOTHING]-[TODAY])<=0,([NOTHING]-[TODAY]+7),[NOTHING]-[TODAY]) AS [DAYS TIL NEXT],
[DELIVERY AFTER NEXT]-[NEXT DELIVERY] AS [DAYS TIL AFTER NEXT], Date()+[DAYS TIL NEXT] AS [NEXT DELIVERY], Mid([DELDAZE],CDbl(InStr(1,[DELDAZE],[NOTHING]))+1,1) AS [AFTER NEXT], [NEXT DELIVERY]+IIf(([AFTER NEXT]-[NOTHING])<=0,([AFTER NEXT]-[NOTHING]+7),[AFTER NEXT]-[NOTHING]) AS [DELIVERY AFTER NEXT]
FROM
(db2003_CUSMAS INNER JOIN [ORIGINAL ROUTES] ON db2003_CUSMAS.CM_CUSTNO = [ORIGINAL ROUTES].CM_CUSTNO)
INNER JOIN [COOLER INFO] ON [ORIGINAL ROUTES].CM_CUSTNO = [COOLER INFO].[CUST#]
WHERE (((db2003_CUSMAS.CM_CHNID)="CV") AND ((db2003_CUSMAS.CM_ZONE)=3))
ASKER
so im assuming that
in oracle is
and if that is the case, does that mean that
is the same as
IIf([CM_PRIMRY_DLVSEQ_1]>0,"M",".") AS Mon,
IIf([CM_PRIMRY_DLVSEQ_2]>0,"T",".") AS Tue,
IIf([CM_PRIMRY_DLVSEQ_3]>0,"W",".") AS Wed,
IIf([CM_PRIMRY_DLVSEQ_4]>0,"Th",".") AS Thur,
IIf([CM_PRIMRY_DLVSEQ_5]>0,"F",".") AS Fri,
IIf([CM_PRIMRY_DLVSEQ_6]>0,"S",".") AS Sat,
in oracle is
CASE when CM_PRIMRY_DLVSEQ_1>0 then 'M' else '.' end AS Mon,
CASE when CM_PRIMRY_DLVSEQ_2>0 then 'T' else '.' end AS Tue,
CASE when CM_PRIMRY_DLVSEQ_3>0 then 'W' else '.' end AS Wed,
CASE when CM_PRIMRY_DLVSEQ_4>0 then 'TH' else '.' end AS Thur,
CASE when CM_PRIMRY_DLVSEQ_5>0 then 'F' else '.' end AS Fri,
CASE when CM_PRIMRY_DLVSEQ_6>0 then 'S' else '.' end AS Sat,
and if that is the case, does that mean that
IIf([CM_PRIMRY_DLVSEQ_1]>0,"2","") & IIf([CM_PRIMRY_DLVSEQ_2]>0,"3","") & IIf([CM_PRIMRY_DLVSEQ_3]>0,"4","") & IIf([CM_PRIMRY_DLVSEQ_4]>0,"5","")
& IIf([CM_PRIMRY_DLVSEQ_5]>0,"6","") & IIf([CM_PRIMRY_DLVSEQ_6]>0,"7","") & IIf([CM_PRIMRY_DLVSEQ_1]>0,"2","")
& IIf([CM_PRIMRY_DLVSEQ_2]>0,"3","") & IIf([CM_PRIMRY_DLVSEQ_3]>0,"4","") & IIf([CM_PRIMRY_DLVSEQ_4]>0,"5","")
& IIf([CM_PRIMRY_DLVSEQ_5]>0,"6","") & IIf([CM_PRIMRY_DLVSEQ_6]>0,"7","") AS DELDAZE,
is the same as
CASE when CM_PRIMRY_DLVSEQ_1>0 then '2' else ''
AND CM_PRIMRY_DLVSEQ_2>0 then '3' else ''
AND CM_PRIMRY_DLVSEQ_3>0 then '4' else '' END
>>and if that is the case, does that mean that
That is doing a string concatination.
Change: IIf([CM_PRIMRY_DLVSEQ_1]>0 ,"2","") & IIf([CM_PRIMRY_DLVSEQ_2]>0 ,"3","") ...
to:
case when CM_PRIMRY_DLVSEQ_1 > 0 then '2' end || case when CM_PRIMRY_DLVSEQ_2 > 0 then '3' end ...
There is no need for an ELSE in the case since it defaults to null.
That is doing a string concatination.
Change: IIf([CM_PRIMRY_DLVSEQ_1]>0
to:
case when CM_PRIMRY_DLVSEQ_1 > 0 then '2' end || case when CM_PRIMRY_DLVSEQ_2 > 0 then '3' end ...
There is no need for an ELSE in the case since it defaults to null.
ASKER
so far so good....
how about the syntax change for
and
how about the syntax change for
Weekday(Now()) AS TODAY,
and
Switch([TODAY]<Left([DELDAZE],1),Left([DELDAZE],1),[TODAY]>=Left([DELDAZE],1)
And [TODAY]<Mid([DELDAZE],2,1),Mid([DELDAZE],2,1),[TODAY]>Left([DELDAZE],1)
And [TODAY]>=Mid([DELDAZE],2,1)
And [TODAY]<>Mid([DELDAZE],3,1),Mid([DELDAZE],3,1),[TODAY]>Left([DELDAZE],1)
And [TODAY]>Mid([DELDAZE],2,1)
And [TODAY]>=Mid([DELDAZE],3,1),Left([DELDAZE],1),[TODAY]=Mid([DELDAZE],4,1),Left([DELDAZE],1)) AS NOTHING,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
figured out the first
still need the second
to_char(sysdate,'D') as Today
still need the second
ASKER
later than you though :) thanks,
ill explain that code in a second, let me run it to see what its doing exactly
ill explain that code in a second, let me run it to see what its doing exactly
ASKER
this portion of the query gives me my deliver days, below is the query and results
what i need to figure out is how to figure out what the next delivery date is, and the date after that is based on todays date (sysdate) I can get into details of how the access database is doing it, but it is not effecient at all and im sure there is a better way to do this. if you want to, i can explain how the access database is doing it if you dont see a quicker way of figuring it out
CASE when CM_PRIMRY_DLVSEQ_1>0 then 'M' else '.' end AS Mon,
CASE when CM_PRIMRY_DLVSEQ_2>0 then 'T' else '.' end AS Tue,
CASE when CM_PRIMRY_DLVSEQ_3>0 then 'W' else '.' end AS Wed,
CASE when CM_PRIMRY_DLVSEQ_4>0 then 'TH' else '.' end AS Thur,
CASE when CM_PRIMRY_DLVSEQ_5>0 then 'F' else '.' end AS Fri,
CASE when CM_PRIMRY_DLVSEQ_6>0 then 'M' else '.' end AS Sat,
MON TUE WED THURS FRI SAT
--------------------------------------------
. T . TH . .
M . W . F .
. T . TH . S
M . . TH . .
. T . . F .
. T . . F .
. . W . F .
M . . TH . .
. T . . F .
. . W . . S
what i need to figure out is how to figure out what the next delivery date is, and the date after that is based on todays date (sysdate) I can get into details of how the access database is doing it, but it is not effecient at all and im sure there is a better way to do this. if you want to, i can explain how the access database is doing it if you dont see a quicker way of figuring it out
>>this portion of the query gives me my deliver days, below is the query and results
Is that what the Access DB shows?
>>im sure there is a better way to do this
This is the issue with porting code from one system to another. There is typically a 'better' way. The problem is you started this question with how to port the existing SQL and not with the actual reauirements.
I suggest you open a new question and reference this one. In the new question post actual data and expected results. Don't post any of the Access information.
Is that what the Access DB shows?
>>im sure there is a better way to do this
This is the issue with porting code from one system to another. There is typically a 'better' way. The problem is you started this question with how to port the existing SQL and not with the actual reauirements.
I suggest you open a new question and reference this one. In the new question post actual data and expected results. Don't post any of the Access information.
ASKER
this data is coming from oracle, the rewritten query so far.. i will open up a new thread. thanks
Does what you have coming from Oracle match what is coming from Access thus far?
>>ill explain that code in a second, let me run it to see what its doing exactly<<
The access switch function is roughly equivalent to the Oracle decode function, and as slightwv stated, the left and mid functions can be duplicated in Oracle using the substr function. Can you provide some sample data for the DELDAZE attribute and its datatype along with the results you would expect to see from the switch function? I think that would help us replicate the results in Oracle.
The access switch function is roughly equivalent to the Oracle decode function, and as slightwv stated, the left and mid functions can be duplicated in Oracle using the substr function. Can you provide some sample data for the DELDAZE attribute and its datatype along with the results you would expect to see from the switch function? I think that would help us replicate the results in Oracle.
ASKER
please see
https://www.experts-exchange.com/questions/27887561/SQL-Query-select-next-date-based-on-query-results.html
for a reference of what i am trying to achieve
https://www.experts-exchange.com/questions/27887561/SQL-Query-select-next-date-based-on-query-results.html
for a reference of what i am trying to achieve
>>for a reference of what i am trying to achieve
Unfortunately I cannot answer that question over here.
The reason for the other question was to achieve the results from straight Oracle and not ported Access code.
I was wanting this question to be a backup and continue porting the code you have.
So, back to porting what you have:
Does what you have coming from Oracle match what is coming from Access thus far?
Unfortunately I cannot answer that question over here.
The reason for the other question was to achieve the results from straight Oracle and not ported Access code.
I was wanting this question to be a backup and continue porting the code you have.
So, back to porting what you have:
Does what you have coming from Oracle match what is coming from Access thus far?
ASKER
yes it is, so far everything is converting properly, i made some changes but this is the data from access
DELDAZE which is the concatenation portiong of the query returns results in this format "3535 " each number represents a day of the week
Sunday beinbg 1, monday 2, tuesday 3, etc.
The column nothing on the query, reads the data returned on deldaze to figure out which is the next delivery date
that's this portion of the query
the ultimate goal is to be able to get the date of the next expected delivery based on the results of
Mon Tue Wed Thur Fri Sat DELDAZE TODAY NOTHING DAYS TIL NEXT DAYS TIL AFTER NEXT NEXT DELIVERY AFTER NEXT DELIVERY AFTER NEXT
. T . Th . . 3535 4 5 1 5 10/4/2012 3 10/9/2012
M . W . F . 246246 4 6 2 3 10/5/2012 2 10/8/2012
M . W . F . 246246 4 6 2 3 10/5/2012 2 10/8/2012
. T . . F . 3636 4 6 2 4 10/5/2012 3 10/9/2012
. T . Th . S 357357 4 5 1 2 10/4/2012 7 10/6/2012
M . W . F . 246246 4 6 2 3 10/5/2012 2 10/8/2012
M . . Th . . 2525 4 5 1 4 10/4/2012 2 10/8/2012
M . . Th . . 2525 4 5 1 4 10/4/2012 2 10/8/2012
. T . . F . 3636 4 6 2 4 10/5/2012 3 10/9/2012
. . W . F . 4646 4 6 2 5 10/5/2012 4 10/10/2012
DELDAZE which is the concatenation portiong of the query returns results in this format "3535 " each number represents a day of the week
Sunday beinbg 1, monday 2, tuesday 3, etc.
The column nothing on the query, reads the data returned on deldaze to figure out which is the next delivery date
that's this portion of the query
Switch([TODAY]<Left([DELDAZE],1),Left([DELDAZE],1),[TODAY]>=Left([DELDAZE],1)
And [TODAY]<Mid([DELDAZE],2,1),Mid([DELDAZE],2,1),[TODAY]>Left([DELDAZE],1)
And [TODAY]>=Mid([DELDAZE],2,1)
And [TODAY]<>Mid([DELDAZE],3,1),Mid([DELDAZE],3,1),[TODAY]>Left([DELDAZE],1)
And [TODAY]>Mid([DELDAZE],2,1)
And [TODAY]>=Mid([DELDAZE],3,1),Left([DELDAZE],1),[TODAY]=Mid([DELDAZE],4,1),Left([DELDAZE],1)) AS NOTHING,
the ultimate goal is to be able to get the date of the next expected delivery based on the results of
CASE when CM_PRIMRY_DLVSEQ_1>0 then 'M' else '.' end AS Mon,
CASE when CM_PRIMRY_DLVSEQ_2>0 then 'T' else '.' end AS Tue,
CASE when CM_PRIMRY_DLVSEQ_3>0 then 'W' else '.' end AS Wed,
CASE when CM_PRIMRY_DLVSEQ_4>0 then 'TH' else '.' end AS Thur,
CASE when CM_PRIMRY_DLVSEQ_5>0 then 'F' else '.' end AS Fri,
CASE when CM_PRIMRY_DLVSEQ_6>0 then 'M' else '.' end AS Sat,
What are the actual columns you need?
Do you need The individual days columns? Do you need the Nothing, today, etc... for the app or are you just after the 'next' delivery date?
I was hoping in your other question you would have started with raw data and expected results and forget all about what you 'currently' have.
I mean I can probably port what you have in access but I'm thinking this can be simplified without all the string concatinaation and substr's.
Do you need The individual days columns? Do you need the Nothing, today, etc... for the app or are you just after the 'next' delivery date?
I was hoping in your other question you would have started with raw data and expected results and forget all about what you 'currently' have.
I mean I can probably port what you have in access but I'm thinking this can be simplified without all the string concatinaation and substr's.
ASKER
for the app I only need the next delivery day, and the delivery day after the next
all of the other data is irrelevant to me, was just being used to see the calculation, but will not be needed for the front end users
all of the other data is irrelevant to me, was just being used to see the calculation, but will not be needed for the front end users
IIf([CM_PRIMRY_DLVSEQ_1]>0
To:
CASE when CM_PRIMRY_DLVSEQ_1>0 then 'M' else '.' end
Same for the rest.