Macros in SQL Queries

Rita Mikusch asked on November 6, 2015 19:24

HI,

I created a custom table, then went to the QUERY tab for that custom table to create a query. For the life of me, I can't get macros to work for this query ... I was hoping somebody here would have some ideas!

The basic query without a macro works: Select Dates from customtable_test where active='yes' AND itemID=2

But if I add a macro, it doesn't work ... here are a few things I've tried, none of them work:

1) Select Dates from customtable_test where active='yes' AND itemID={%2%}

2) Select Dates from customtable_test where active='yes' AND itemID={%QueryString.itemID%}

3) Select Dates from customtable_test where active='yes' AND itemID={?itemID?}

We're using Kentico 8.0.

thank you :)

macros queries

Correct Answer

Laura Frese answered on November 6, 2015 20:19

The macro needs to go into the where condition of the data source or repeater that you are using to access that data. Make sure your query looks something like:

SELECT ##TOPN## ##COLUMNS## FROM customtable_test WHERE (##WHERE##) ORDER BY ##ORDERBY##

Navigate to the page you want to show the data on and use a Query data source to get the data:

Query Name: select your query
Where condition: active='yes' AND ItemID={%ItemID%}
if the itemid is passed in as a url parameter you can do {%QueryString.ItemID|(identity)GlobalAdministrator%} or {% itemID %}
Selected columns: Dates

Make sure you enter the where condition into the Edit Value dialog. You can access it by clicking the black triangle next to the Where text box

Then use a basic repeater to display the data

Data source name: QueryDataSource (or whatever name you gave your data source web part)

Make sure you have also created a transformation to display that data

If there is an error or the data isnt showing check the Event Log

If you want to see the SQL query generated, check out the Debug. Go into Settings > System > Debug to enable debugging

Some helpful pages:

Loading data using custom queries

Entering macro expressions

Displaying data from custom tables

Writing transformations

Check Event log

Debugging SQL queries

1 votesVote for this answer Unmark Correct answer

Recent Answers

Zach Perry answered on November 6, 2015 19:59

I don't believe you can use macros in the actual query. If you replace the where condition of your query with ##WHERE## and then put your where condition with the macro in a web part, it should work.

0 votesVote for this answer Mark as a Correct answer

Rita Mikusch answered on November 6, 2015 20:02

Thank you, Zachary, that would explain why nothing is working!

0 votesVote for this answer Mark as a Correct answer