troubleshooting Question

Converting MS ACCESS SQL to Oracle SQL [Advanced]

Avatar of futureDBA
futureDBA asked on
Microsoft AccessOracle DatabaseSQL
17 Comments1 Solution335 ViewsLast Modified:
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]


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 CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros