Write macros in Select query in Query Data source

joyanta sen asked on December 10, 2018 21:36

Hi, Can I write macro {% Settings.EnteredLocationLatitude #%} inside Select query like below.In the first column I am calling a function, which will give me the distance. But it is throwing error. If not possible this way, could you please tell me how to pass some value in that function, which is not static value?

 SELECT  DISTINCT  
   [dbo].[Func_Cms_GetDistance](ISNULL(CL.LocationLatitude, 0), ISNULL(CL.LocationLongitude, 0), {% Settings.EnteredLocationLatitude #%},'-104.990251') as distance
   ,CL.LocationName,CL.LocationAddress,CL.LocationCity,CL.LocationState,CL.LocationZip
  FROM custom_Location CL

  Thanks,
  Joyanta

write macros in select qu

Recent Answers

Zach Perry answered on December 10, 2018 22:01

What version of Kentico are you on?

Where is this query being used?

They added a security feature to prevent this in 9.43 i think, but you used to be able to stick a ##WHERE## macro in the select and then in the where field on the webpart add your macro.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on December 11, 2018 03:29 (last edited on December 10, 2019 02:31)

if custom_Location is a custom table in kentico, yes you can:

{%GlobalObjects.CustomTables["customtable.table_code_name"].Items.Where("ItemID = 1")[0]"fieldname"]|(identity)GlobalAdministrator%}

I would assume your sql function Func_Cms_GetDistance can be reproduce with a macro.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on December 11, 2018 16:49

Actually here is a similar topic

0 votesVote for this answer Mark as a Correct answer