QLike Recipes

Below are some examples of the pattern to use in QLike for various scenarios.

Find all references to a table in SELECT clause

SELECT ? FROM my_table

Find all references to a column in SELECT clause

SELECT my_column

Find all references to a column in WHERE clause

SELECT ? FROM ? WHERE my_column

Find snapshot by timestamp for a particular table

SELECT ? FROM my_table AT(TIMESTAMP => ?)

Find select statements with MIN and MAX calls

SELECT MIN(?), MAX(?) FROM ?

Find count distinct calls

SELECT count(distinct ?) FROM ?

Find usages or ROLLUP

SELECT ? FROM ? GROUP BY ROLLUP(?)

Find all joins between two tables

SELECT ? FROM my_table1 JOIN my_table2

Find joins between two tables using not equals join condition

SELECT ? FROM ? JOIN ? ON (? != ?)

Find all outer joins between two tables

SELECT ? FROM my_table1 OUTER JOIN my_table2

Find unions of two particular tables

SELECT ? FROM my_table1 UNION SELECT * FROM my_table2

Find usage of PIVOT that uses AVG

SELECT ? FROM ? PIVOT(avg(?) FOR ? IN (?))

Find identifier indirection through session variable

SELECT ? FROM IDENTIFIER($my_session_var)

Find the setting of a particular session variable

ALTER SESSION SET my_session_var = ?

Find calls to a particular procedure

CALL my.procedure1(?)

Find calls to a particular procedure with particular arguments bound into script variables

CALL my.procedure1(A => ?, B => ?) INTO ?

Find usage of variable by name in scripting block

LET my_var ?

Find declaration of exception with a certain error number

LET ? EXCEPTION (-20002, ?)

Find declaration of cursor with a particular name in script block

LET my_cursor CURSOR FOR ?

Find if statement using SELECT in condition

IF ((SELECT ?)) THEN ?; END IF

Find script blocks with exceptions block

BEGIN ?; EXCEPTION ?; END

Find script blocks that catch OTHER exceptions

BEGIN ?; EXCEPTION WHEN OTHER THEN ?; END

Find script blocks that return tables

BEGIN RETURN TABLE(?); END