Link to home
Start Free TrialLog in
Avatar of futureDBA
futureDBA

asked on

Converting MS ACCESS SQL to Oracle SQL [Advanced]

I need help with this portion of the 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]

Open in new window



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

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

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

Same for the rest.
Avatar of futureDBA

ASKER

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,

Open 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, 

Open 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, 

Open 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

Open 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, 

Open 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, 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
figured out the first
to_char(sysdate,'D') as Today 

Open 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

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

Open 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, 

Open 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, 

Open 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