Skip to content

Instantly share code, notes, and snippets.

@markgajdosik

markgajdosik/query.sql

Created September 30, 2016 15:58
Show Gist options
  • Save markgajdosik/9945023ddb1d459e7d3442c6e631e883 to your computer and use it in GitHub Desktop.
Save markgajdosik/9945023ddb1d459e7d3442c6e631e883 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query.sql
WITH table_join AS (
SELECT row, value, field_id FROM cucm_csv_batentity
WHERE batdata_id = 10 AND role = 'phone' AND ((field_id = 2 AND value = 'Cisco 6921') OR (field_id = 1))),
add_table_join AS (
SELECT row, value, field_id from cucm_csv_batadditional
WHERE batdata_id = 10 AND role = 'phone' AND ((field_id = 2 AND value != '') OR (field_id = 1 AND value != ''))),
add_sort_join AS (
SELECT a0.row, "Import Notes", "Import Date" FROM
(SELECT DISTINCT add_table_join.row, add_table_join.value as "Import Notes" FROM add_table_join WHERE add_table_join.field_id = 2) AS a0 INNER JOIN
(SELECT DISTINCT add_table_join.row, add_table_join.value as "Import Date" FROM add_table_join WHERE add_table_join.field_id = 1) AS a1 ON a0.row = a1.row),
sort_join AS (
SELECT s0.row, "Device Name", "Device Type" FROM
(SELECT DISTINCT table_join.row, table_join.value as "Device Name" FROM table_join WHERE table_join.field_id = 1) AS s0 FULL OUTER JOIN
(SELECT DISTINCT table_join.row, table_join.value as "Device Type" FROM table_join WHERE table_join.field_id = 2) AS s1 ON s0.row = s1.row)
SELECT f0.row, "Device Name", "Device Type", "Import Date" FROM
(SELECT DISTINCT table_join.row FROM table_join WHERE table_join.field_id = 1) AS f0 INNER JOIN
(SELECT DISTINCT table_join.row FROM table_join WHERE table_join.field_id = 2) AS f1 ON f0.row = f1.row INNER JOIN
add_sort_join ON f0.row = add_sort_join.row LEFT JOIN
sort_join ON f0.row = sort_join.row
ORDER BY "Device Name" ASC NULLS FIRST, "Device Type" DESC NULLS LAST, "Import Date" ASC NULLS FIRST;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment