Created
September 30, 2016 16:24
-
-
Save markgajdosik/661baef3d2c5366f6478ffc506979ce3 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH table_join AS ( | |
SELECT rowdex, "value", field_id FROM cucm_csv_batrepeatingfield | |
WHERE batdata_id = 10 AND role = 'phone' AND | |
((field_id = 327 AND value ILIKE '%US%') OR | |
(field_id = 295 AND value ILIKE '%10000%') OR | |
(field_id = 220 AND value ILIKE '%Cu1%'))), | |
sort_join AS ( | |
SELECT s0.rowdex, "Directory Number", "Route Partition" FROM | |
(SELECT DISTINCT table_join.rowdex, table_join.value as "Directory Number" FROM table_join WHERE table_join.field_id = 295) AS s0 FULL OUTER JOIN | |
(SELECT DISTINCT table_join.rowdex, table_join.value as "Route Partition" FROM table_join WHERE table_join.field_id = 220) AS s1 ON s0.rowdex = s1.rowdex) | |
SELECT f0.rowdex, "Directory Number", "Route Partition" FROM | |
(SELECT DISTINCT table_join.rowdex FROM table_join WHERE table_join.field_id = 327) AS f0 INNER JOIN | |
(SELECT DISTINCT table_join.rowdex FROM table_join WHERE table_join.field_id = 295) AS f1 ON f0.rowdex = f1.rowdex | |
INNER JOIN | |
(SELECT DISTINCT table_join.rowdex FROM table_join WHERE table_join.field_id = 220) AS f2 ON f0.rowdex = f2.rowdex LEFT JOIN | |
sort_join ON f0.rowdex = sort_join.rowdex | |
ORDER BY "Directory Number" ASC NULLS FIRST, "Route Partition" DESC NULLS LAST; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment