4LpkZkid

1CREATE PROCEDURE ", ?PROCEDURE_NAME, "() BEGIN
2        DROP TEMPORARY TABLE IF EXISTS tmp_uids;
3
4        CREATE TEMPORARY TABLE tmp_uids (
5            uid VARCHAR(200) NOT NULL,
6            PRIMARY KEY (`uid`)
7        ) ENGINE=MyISAM AS (
8            SELECT uid
9            FROM ((SELECT uid FROM v4_slots WHERE status = 'live')
10                  UNION
11                  (SELECT user_uid FROM v4_slots WHERE status = 'live')
12                  UNION
13                  (SELECT a.uid
14                    FROM v4_ads a
15                    INNER JOIN v4_campaigns c ON c.id = a.campaign_id
16                    INNER JOIN v4_objective_definitions d ON a.id = d.ad_id
17                    WHERE d.dependency_mode = 'display'
18                        AND d.status = 'live'
19                        AND a.status = 'live'
20                        AND a.resource_non_rtb IS NOT NULL)
21                  UNION
22                  (SELECT c.uid
23                    FROM v4_campaigns c
24                    WHERE c.status = 'live')
25                  UNION
26                  (SELECT apps.uid
27                   FROM v4_apps apps
28                   WHERE apps.status = 'live'))
29            AS possible_uids
30        );
31
32        SELECT v4_targeting_definitions.uid AS uid, `type`, `values`
33        FROM v4_targeting_definitions
34        INNER JOIN tmp_uids ON tmp_uids.uid = v4_targeting_definitions.uid
35        WHERE LENGTH(v4_targeting_definitions.uid) > 0
36          AND LENGTH(`values`) > 0
37          AND LENGTH(`type`) > 0
38          AND status = 'live'
39        ORDER BY v4_targeting_definitions.uid;
40
41        DROP TEMPORARY TABLE IF EXISTS tmp_uids;
42    END
43    ">>