SQL_REDIRECT Example

A SQL_REDIRECT query template is designed to run a SQL statement that redirects to another query template. Generally, it is a case statement that determines which query template to run based on parameters passed in from the CPM data.

When you drill from a data value for unit, Div1, this example redirects custom drill to run the Div1QueryTemplate. When drilling from a data value for unit, Div2, custom drill is redirected to run the Div2QueryTemplate. When drilling from a data value for any other unit, the OtherDivQueryTemplate is run. You can use various query template types for the redirected query templates, Div1QueryTemplate, Div2QueryTemplate.

We recommend using a case statement that includes an else clause in order to avoid returning a null value. The drill page cannot load if the CASE value is null.

SELECT QUERYNAME=case '{Unit}'
   WHEN 'Div1' THEN 'Div1QueryTemplate'
   WHEN 'Div2' THEN 'Div2QueryTemplate'
   ELSE 'OtherDivQueryTemplate' 
END

SELECT CAST
   (CASE '{Unit}' 
      WHEN 'Div1' THEN 'Div1QueryTemplate'
      WHEN 'Div2' THEN 'Div2QueryTemplate'
      ELSE 'OtherDivQueryTemplate' 
   END 
   AS VARCHAR2(40)) 
AS QUERYNAME;