Vfrdtyky

I'm trying to finalize what can seem a very basic query but I got lost.
Basically, I'm looking for entries in a SQL table, created some mins ago and from that I collect addresses and put in variables.

Then I query google.maps with the addresses and collect the driving distance in km.

If I do the tasks step by step, I can find records, I get google answer (the right one!, yeah) and the issue is now to run everything in one job and schedule it.

The following code works fine:

DECLARE

@ToAddress NVARCHAR(100) =

(select replace((select 

(select oms60_0 from land with (nolock) where landcode = cicmpy.cmp_fctry) COLLATE Latin1_General_CI_AI +'+'+cmp_fpc+'+'+cmp_fcity+'+'+cmp_fadd1

 from cicmpy with (nolock)

 where cmp_type = 'd' and administration = (select comp from humres with (nolock) where res_id = ab.empid)),' ','+') 

 from absences ab where ab.type = '138' and hid = '2158') --must be ab.hid repalced by fixed value for testing

,@FromAddress NVARCHAR(100) =

(select replace((select

(select oms60_0 from land with (nolock) where landcode = cicmpy.cmp_fctry) COLLATE Latin1_General_CI_AI +'+'+cmp_fpc+'+'+cmp_fcity+'+'+cmp_fadd1

 from cicmpy with (nolock) where cmp_wwn = 

(select account from projectaccounts with (nolock) where project = 

(select top 1 projectnumber from prproject where projectnumber = ab.projectnumber))),' ','+')

 from absences ab where ab.type = '138' and hid = '2158') --must be ab.hid repalced by fixed value for testing

,@DistanceInKm FLOAT

,@Object INT

,@ResponseonseText NVARCHAR(4000)

,@StatuserviceUrl NVARCHAR(500)

SET @StatuserviceUrl = 'https://maps.googleapis.com/maps/api/distancematrix/xml?origins=' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=en-EN&units=metric,NY&key=MYGOOGLEMAPSKEY'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' EXEC sp_OAMethod @Object, 'send' EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT

DECLARE @Response XML

SET @ResponseonseText = REPLACE(@ResponseonseText, N'encoding="UTF-8"', N'')

SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)

DECLARE @Status NVARCHAR(20) DECLARE @Distance NVARCHAR(20)

SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)') 

IF(@Status = 'ZERO_RESULTS') SET @Distance = NULL ELSE SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)') 

SET @DistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)

PRINT @DistanceInKm

Now I try to make it work to update all entries that have been modified 5 minutes ago:

Begin transaction

update absences set absences.FreeTextField_15=(

DECLARE

@ToAddress NVARCHAR(100) =

(select replace((select 

(select oms60_0 from land with (nolock) where landcode = cicmpy.cmp_fctry) COLLATE Latin1_General_CI_AI +'+'+cmp_fpc+'+'+cmp_fcity+'+'+cmp_fadd1

 from cicmpy with (nolock)

 where cmp_type = 'd' and administration = (select comp from humres with (nolock) where res_id = ab.empid)),' ','+') 

 from absences ab where ab.type = '138' and hid = ab.hid)

,@FromAddress NVARCHAR(100) =

(select replace((select

(select oms60_0 from land with (nolock) where landcode = cicmpy.cmp_fctry) COLLATE Latin1_General_CI_AI +'+'+cmp_fpc+'+'+cmp_fcity+'+'+cmp_fadd1

 from cicmpy with (nolock) where cmp_wwn = 

(select account from projectaccounts with (nolock) where project = 

(select top 1 projectnumber from prproject where projectnumber = ab.projectnumber))),' ','+')

 from absences ab where ab.type = '138' and hid = ab.hid)

,@DistanceInKm FLOAT

,@Object INT

,@ResponseonseText NVARCHAR(4000)

,@StatuserviceUrl NVARCHAR(500)

SET @StatuserviceUrl = 'https://maps.googleapis.com/maps/api/distancematrix/xml?origins=' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=en-EN&units=metric,NY&key=GOOGLEMAPSKEY'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' EXEC sp_OAMethod @Object, 'send' EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT

DECLARE @Response XML

SET @ResponseonseText = REPLACE(@ResponseonseText, N'encoding="UTF-8"', N'')

SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)

DECLARE @Status NVARCHAR(20) DECLARE @Distance NVARCHAR(20)

SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)') 

IF(@Status = 'ZERO_RESULTS') SET @Distance = NULL ELSE SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)') 

SET @DistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)

PRINT @DistanceInKm

)

where absences.type = '138' and absences absences.sysmodified <(select dateadd(mi,-5,getdate()))

issue is= incorrect syntax near @DECLARE (the initial one and the ) after PRINT @DistanceInKm is underlined in red.

Another question hitting my head: as my update statement is the main I feel I miss a where clause to define the record id (called ab.hid in the subqueries to find the addresses).

My SQL knowledge is home made by trial and errors and step by step learning.

Thanks for your nice cooperation.