Access SQL

Formulas in a Query

The data derives from this query is automatically exported to a CSV file for import into another Oracle database. The crazy “+” signs in the field names was an expedient.

SELECT "Request Values" AS [Request Fields], 
	"Call" AS Provider, 
	"createUpdateCallHLA" AS Service, 
	"admin" AS UserName, 
	"admin" AS [Password], 
	"CST" AS TimeZone, 
	keyval("ProjectID") AS [Input+CallInput+serviceOrderId], See Access VBA
	Abandon.DIST & "1" AS [Input+CallInput+centerLogged], 
	1 AS [Input+CallInput+cust], 
	"SA" AS [Input+CallInput+callTypeCode], 
	IIf(Len([abandon].[contractor])>=2,Left$([Abandon].[CONTRACTOR],25),Null) 
		AS [Input+ActionUDFInput+CallContactName], 
	Abandon.FAX AS [Input+ActionUDFInput+CallContactPhone], 
	ViryaDate([woissued]) AS [Input+ActionInput+OpenDate], 
	ViryaDate(Now()+KeyVal("DaysFuture")) AS [Input+ActionInput+requiredDateTo], 
	ViryaDate(Now()+0.5) AS [Input+ActionInput+requiredDateFrom], See Access VBA
	Abandon.[SERVICE NUMBER] AS [Input+ActionUDFInput+SLSServiceNumber], 
	Abandon.[SERVICE LINE ABAND COST AGREEMENT] AS [Input+ActionUDFInput+SLACA], 
	IIf(Len([Abandon]![WAIVED SLACA CHARGE])>=2,Left$([Abandon].[WAIVED SLACA CHARGE],34),Null) 
		AS [Input+ActionUDFInput+SLACAWaivedChrg], 
	Null AS MainAbandonmentJob, 
	Abandon.COMMENTS AS [Input+ActionUDFInput+AdditionalInfo], 
	Left(Abandon.[INSTALL SERVICE LOCATION],2) AS [Input+ActionUDFInput+ServiceLocationFtg], 
	Right(Abandon.[INSTALL SERVICE LOCATION],4) AS [Input+ActionUDFInput+ServiceLocationDirLdmk], 
	Right([INSTALL CURB BOX LOC],4) AS [Input+ActionUDFInput+CurbBoxLocationDirLdmk], 
	Left([INSTALL CURB BOX LOC],2) AS [Input+ActionUDFInput+CurbBoxLocationDirFtg], 
	Right([INSTALL SERVICE RISER LOC],4) AS [Input+ActionUDFInput+RiserLocationDirLdmk], 
	Left([INSTALL SERVICE RISER LOC],2) AS [Input+ActionUDFInput+RiserLocationDirFtg], 
	Left([INSTALL SERVICE TEE LOC],2) AS [Input+ActionUDFInput+TeeLocationFtg], 
	Right([INSTALL SERVICE TEE LOC],4) AS [Input+ActionUDFInput+TeeLocationDirLdmk], 
	"99" AS [Input+ActionUDFInput+TapSizeCode], 
	=BreakString([Abandon]![RETIRE SERVICE SIZE CODE]) AS [Input+ActionUDFInput+RetSizeCode], 
	=BreakStringSecnd([Abandon]![RETIRE SERVICE SIZE CODE]) AS [Input+ActionUDFInput+RetSizeCode2], 
	=BreakString([Abandon]![RETIRE SERVICE MATERIAL]) AS [Input+ActionUDFInput+RetMaterial], 
	=BreakStringSecnd([Abandon]![RETIRE SERVICE MATERIAL]) AS [Input+ActionUDFInput+RetMaterial2], 
	=BreakString([Abandon].[RETIRE SERVICE FOOTAGE]) AS [Input+ActionUDFInput+RetFootage], 
	=BreakStringSecnd([Abandon]![RETIRE SERVICE FOOTAGE]) AS [Input+ActionUDFInput+RetFootage2], 
	=BreakString([Abandon]![RETIRE SERVICE WO#]) AS [Input+ActionUDFInput+RetWONo], 
	=BreakStringSecnd([Abandon]![RETIRE SERVICE WO#]) AS [Input+ActionUDFInput+RetWONo2], 
	Right([MAIN INFORMATION LOC],4) AS [Input+ActionUDFInput+MainLocationDirLdmk], 
	Left([MAIN INFORMATION LOC],2) AS [Input+ActionUDFInput+MainLocationFtg], 
	1 AS [Input+ActionRequiredSkills+ARRAY+1+resourceSequence], 
	1 AS [Input+ActionRequiredSkills+ARRAY+1+requiredQuantity], 
	"Call" AS [ActionRequiredSkills+ARRAY+2+levelDesc], 
	10 AS [Input+ActionRequiredSkills+ARRAY+2+level], 
	Abandon.PSKILL AS [Input+ActionRequiredSkills+ARRAY+2+skill], 
	1 AS [Input+ActionRequiredSkills+ARRAY+2+resourceSequence], 
	1 AS [Input+ActionRequiredSkills+ARRAY+2+requiredQuantity], 
	"XYZ" AS [Input+ActionUDFInput+Division]
FROM Abandon
WHERE (((Abandon.WOISSUED)>=keyval("AbandonDate")) AND ((Abandon.MigrateSH) Is Null) 
	AND ((Abandon.ABANDONED) Is Null))
ORDER BY Abandon.WOISSUED;