Converting MS ACCESS SQL to Oracle SQL [Advanced]

From:
IIf([CM_PRIMRY_DLVSEQ_1]>0,"M",".")

To:
CASE when CM_PRIMRY_DLVSEQ_1>0 then 'M' else '.' end

Same for the rest.

so im assuming that

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,

Select allOpen in new window

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, 

Select allOpen in new window

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, 

Select allOpen in new window

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

Select allOpen in new window

>>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.

so far so good....

how about the syntax change for

Weekday(Now()) AS TODAY, 

Select allOpen in new window

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, 

Select allOpen in new window

figured out the first

to_char(sysdate,'D') as Today 

Select allOpen in new window

still need the second

later than you though :) thanks,


ill explain that code in a second, let me run it to see what its doing exactly

this portion of the query gives me my deliver days, below is the query and results

	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

Select allOpen in new window

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.

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.

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

>>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?

yes it is, so far everything is converting properly, i made some changes but this is the data from access

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

Select allOpen in new window

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, 

Select allOpen in new window

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, 

Select allOpen in new window

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.

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