Skip to content

Instantly share code, notes, and snippets.

/01_create_testdb.sql Secret

Created February 15, 2018 15:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anonymous/30cb138b598fec46be762789397796b6 to your computer and use it in GitHub Desktop.
Save anonymous/30cb138b598fec46be762789397796b6 to your computer and use it in GitHub Desktop.
Download ZIP
troubleshooting issues with schema prefix appended to tables in SHOW CREATE VIEW statements and mysqldump - mariadb10.2.12
Raw
01_create_testdb.sql
CREATE SCHEMA testdb;
Raw
02_create_tables.sql
CREATE TABLE `rpt_report` (
`filename` varchar(250) NOT NULL,
`basepath` varchar(250) NOT NULL DEFAULT '',
`status` varchar(15) DEFAULT NULL,
`message` varchar(2000) DEFAULT NULL,
`uploadDate` datetime NOT NULL,
`publishDate` datetime DEFAULT NULL,
`program` varchar(10) DEFAULT NULL,
`group` varchar(10) DEFAULT NULL,
`scope` varchar(15) DEFAULT NULL,
`COC` varchar(15) DEFAULT NULL,
`grantType` varchar(15) DEFAULT NULL,
`grantee` varchar(15) DEFAULT NULL,
`hudFO` varchar(10) DEFAULT NULL,
`hudRegion` char(3) DEFAULT NULL,
`state` char(2) DEFAULT NULL,
`periodEnd` date DEFAULT NULL,
`toolType` varchar(50) DEFAULT NULL,
`reportType` varchar(50) DEFAULT NULL,
`sort` varchar(15) DEFAULT NULL,
`fiscalYear` varchar(15) DEFAULT NULL,
`span` varchar(20) DEFAULT NULL,
PRIMARY KEY (`filename`),
KEY `fk_type_report` (`program`,`group`,`scope`),
KEY `rpt_report_coc_idx` (`COC`),
KEY `rpt_report_grantee__idx` (`grantee`),
KEY `idx_rpt_report_periodEnd` (`periodEnd`),
KEY `idx_rpt_report_status` (`status`),
KEY `idx_rpt_report_grantType` (`grantType`),
KEY `idx_rpt_report_state` (`state`),
KEY `idx_rpt_report_reportType` (`reportType`),
KEY `idx_rpt_report_toolType` (`toolType`),
KEY `idx_rpt_report_program_grantee_status` (`status`,`program`,`grantee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_type` (
`program` varchar(10) NOT NULL,
`group` varchar(10) NOT NULL,
`scope` varchar(15) NOT NULL,
`label` varchar(100) DEFAULT NULL,
PRIMARY KEY (`program`,`group`,`scope`),
KEY `fk_program_type` (`program`),
KEY `fk_group_type` (`group`),
KEY `fk_scope_type` (`scope`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_coc` (
`code` varchar(15) NOT NULL,
`label` varchar(255) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_grantee` (
`code` varchar(15) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_state` (
`code` char(2) NOT NULL,
`label` varchar(30) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_grant_type` (
`code` varchar(15) NOT NULL,
`label` varchar(45) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_tool_type` (
`code` varchar(15) NOT NULL,
`label` varchar(45) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_report_type` (
`program` varchar(10) NOT NULL,
`group` varchar(10) NOT NULL,
`code` varchar(15) NOT NULL,
`label` varchar(45) NOT NULL,
PRIMARY KEY (`code`),
KEY `idx_rpt_report_type_program` (`program`),
KEY `idx_rpt_report_type_group` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `c_tcategories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`categoryID` char(35) DEFAULT '0',
`masterNameLong` varchar(255) NOT NULL DEFAULT '0',
`masterNameShort` varchar(20) DEFAULT '0',
`rsc_programID` int(10) unsigned DEFAULT NULL,
`rsc_reportID` int(10) unsigned DEFAULT NULL,
`rsc_ccrID` int(10) unsigned DEFAULT NULL,
`faq_contentID` char(35) DEFAULT NULL,
`faq_contentTitle` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=182 DEFAULT CHARSET=utf8 COMMENT='Mapping for various plugin names to a common ''Master'' version of a Program/Resporting System/CCR name';
CREATE TABLE `rpt_environmental_review_record` (
`errId` varchar(15) NOT NULL,
`basePath` varchar(250) DEFAULT NULL,
`filename` varchar(250) DEFAULT NULL,
`projectName` varchar(60) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`reviewLevel` varchar(5) DEFAULT NULL,
`status` varchar(15) DEFAULT NULL,
`err_status` varchar(2) DEFAULT NULL,
`removalDate` datetime DEFAULT NULL,
`uploadDate` datetime DEFAULT NULL,
`message` varchar(2000) DEFAULT NULL,
`unique_id` bigint(20) DEFAULT NULL,
`publishDate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`errId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rpt_sort` (
`code` varchar(15) NOT NULL,
`label` varchar(45) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Raw
03_create_view.sql
DROP VIEW IF EXISTS vw_search_report_everydocid;
CREATE
VIEW vw_search_report_everydocid AS
SELECT
r.uploadDate AS HUDx_dlm,
r.publishDate AS HUDx_dp,
GROUP_CONCAT(mcat.category_short
ORDER BY mcat.category_short
SEPARATOR '|') AS HUDX_cs,
GROUP_CONCAT(mcat.category_long
ORDER BY mcat.category_long
SEPARATOR '|') AS HUDX_cl,
GROUP_CONCAT(mcat.category_display
ORDER BY mcat.category_display
SEPARATOR '|') AS HUDX_dsp,
CAST(CONCAT_WS('',
'https://www.hudexchange.info',
'/resource/reportmanagement/published/',
REPLACE(REPLACE(filename, '%', '%25'),
'+',
'%2B'))
AS CHAR CHARSET UTF8) AS HUDX_url,
'Reports' AS HUDX_ct,
CASE
WHEN
r.program = 'CoC' AND r.`group` = 'GIS'
THEN
CONCAT(YEAR(periodEnd),
' ',
tt.label,
' - ',
s.label,
CASE
WHEN tt.code = 'ShapeFile' THEN ' CoCs'
ELSE ''
END)
WHEN
r.program = 'CoC'
THEN
CONCAT(YEAR(periodEnd),
' ',
t.label,
CASE
WHEN c.label <> '' THEN CONCAT(' - ', c.label)
WHEN s.label <> '' THEN CONCAT(' - ', s.label)
ELSE ''
END)
WHEN
r.program = 'HOPWA'
THEN
CONCAT(YEAR(periodEND),
'-',
YEAR(periodEND) + 1,
' ',
t.label,
CASE
WHEN g.name IS NOT NULL THEN CONCAT(' - ', g.name)
ELSE ''
END)
WHEN
r.program = 'NSP'
THEN
CONCAT(t.label,
CASE
WHEN gt.label IS NOT NULL THEN CONCAT(' - ', gt.label, ' Grantee')
ELSE ''
END,
CASE
WHEN g.name IS NOT NULL THEN CONCAT(' - ', g.name)
ELSE ''
END,
CASE
WHEN
r.`group` = 'QPR'
THEN
CONCAT(' - ',
CASE
WHEN MONTH(periodEnd) <= 3 THEN CONCAT('Q1 ', YEAR(periodEnd))
WHEN
MONTH(periodEnd) > 3
AND MONTH(periodEnd) <= 6
THEN
CONCAT('Q2 ', YEAR(periodEnd))
WHEN
MONTH(periodEnd) > 6
AND MONTH(periodEnd) <= 9
THEN
CONCAT('Q3 ', YEAR(periodEnd))
ELSE CONCAT('Q4 ', YEAR(periodEnd))
END)
WHEN
r.periodEnd IS NOT NULL
THEN
CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y'))
ELSE ''
END)
WHEN
r.program = 'HOME'
THEN
CONCAT(CASE
WHEN
r.`group` = 'IncomeLmts'
OR r.`group` = 'RentLimits'
THEN
CONCAT(YEAR(periodend), ' ')
ELSE ''
END,
t.label,
CASE
WHEN
r.`group` = 'Dash'
THEN
CONCAT(IFNULL(CONCAT(' - ', g.name), ''),
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
''))
WHEN
r.`group` = 'Vacant'
THEN
CONCAT(' - ',
s.label,
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
''))
WHEN
r.`group` = 'Open'
THEN
CONCAT(' - ',
s.label,
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
''))
WHEN
r.`group` = 'Snap' AND r.scope = 'PJ'
THEN
CONCAT(IFNULL(CONCAT(' - ', g.name), ''),
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
''))
WHEN
r.`group` = 'Snap' AND r.scope <> 'PJ'
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
'')
WHEN
r.`group` = 'Prod'
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
'')
WHEN
r.`group` = 'Act'
THEN
CONCAT(' - ',
s.label,
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
''))
WHEN
r.`group` = 'ActHB'
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
'')
WHEN
r.`group` = 'Cancel'
AND r.periodend IS NOT NULL
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
'')
WHEN
r.`group` = 'Cancel'
AND r.periodend IS NULL
THEN
IFNULL(CONCAT(' - ', fiscalYear), '')
WHEN
r.`group` = 'Exp'
THEN
CONCAT(' - FY ',
r.fiscalYear,
CASE
WHEN periodEnd IS NOT NULL THEN ' or Earlier Expiring Funds'
ELSE ' Expired Funds'
END,
' - All States',
IFNULL(CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y')),
''))
WHEN
r.`group` = 'Deadline'
THEN
CONCAT(' - ',
CAST(YEAR(periodEnd) AS CHAR),
' Commitment & CHDO Reservation, and ',
CAST(YEAR(periodEnd) - 3 AS CHAR),
' Disbursement Requirements',
' - ',
hs.label)
WHEN
r.`group` = 'ES'
THEN
CONCAT(' - ',
DATE_FORMAT(periodEnd, '%M %e, %Y'),
' - ',
hs.label)
WHEN
r.`group` = 'IncomeLmts'
OR r.`group` = 'RentLimits'
THEN
IFNULL(CONCAT(' - ', s.label), '')
ELSE ''
END)
WHEN
r.program = 'CDBG'
THEN
CONCAT(t.label,
IFNULL(CONCAT(' - ', g.name), ''),
' - PY',
YEAR(periodEnd))
WHEN
r.program = 'CPD'
THEN
CONCAT(r.program,
' ',
rt.label,
IFNULL(CONCAT(' - ', g.name), ''))
ELSE t.label
END AS HUDX_title
FROM
rpt_report r
JOIN
rpt_type t ON r.program = t.program
AND r.`group` = t.`group`
AND r.scope = t.scope
LEFT JOIN
rpt_coc c ON c.code = r.coc
LEFT JOIN
rpt_grantee g ON g.code = r.grantee
LEFT JOIN
rpt_state s ON s.code = r.state
LEFT JOIN
rpt_grant_type gt ON gt.code = r.grantType
LEFT JOIN
rpt_tool_type tt ON r.tooltype = tt.code
LEFT JOIN
rpt_sort hs ON r.sort = hs.code
LEFT JOIN
rpt_report_type rt ON r.program = rt.program
AND r.`group` = rt.`group`
AND r.reportType = rt.code
LEFT JOIN
(SELECT
CASE
WHEN masterNameShort != '' THEN masterNameShort
ELSE masterNameLong
END AS category_short,
masterNameLong AS category_long,
CASE
WHEN masterNameShort != '' THEN CONCAT(masterNameShort, ': ', masterNameLong)
ELSE masterNameLong
END AS category_display
FROM
c_tcategories) mcat ON CASE
WHEN
r.program = 'CDBG'
THEN
mcat.category_long IN ('CDBG Entitlement Program' , 'CDBG HUD Administered Non-Entitled Counties in Hawaii Program',
'CDBG Insular Areas Program',
'CDBG State Program')
WHEN
r.program = 'CPD'
THEN
mcat.category_long IN ('CDBG Entitlement Program' , 'CDBG HUD Administered Non-Entitled Counties in Hawaii Program',
'CDBG Insular Areas Program',
'CDBG State Program')
OR mcat.category_short IN ('HOME' , 'HOPWA', 'ESG')
ELSE r.program = mcat.category_short
END
WHERE
status = 'published'
GROUP BY r.filename
UNION SELECT
uploadDate AS HUDx_dlm,
publishDate AS HUDx_dp,
CASE
WHEN mcat.masterNameShort != '' THEN mcat.masterNameShort
ELSE mcat.masterNameLong
END AS HUDX_cs,
mcat.masterNameLong AS HUDX_cl,
CASE
WHEN
mcat.masterNameShort != ''
THEN
CONCAT(mcat.masterNameShort,
': ',
mcat.masterNameLong)
ELSE mcat.masterNameLong
END AS HUDX_dsp,
CAST(CONCAT_WS('',
'https://www.hudexchange.info',
'/resource/reportmanagement/published/',
REPLACE(REPLACE(filename, '%', '%25'),
'+',
'%2B'))
AS CHAR CHARSET UTF8) AS HUDX_url,
'Reports' AS HUDX_ct,
CONCAT('Environmental Review Record - ',
projectName,
' ',
city,
' ',
state,
' - ',
err_status) AS HUDX_title
FROM
rpt_environmental_review_record err
LEFT JOIN
c_tcategories mcat ON mcat.masterNameLong = 'Environmental Review'
WHERE
status = 'published'
AND DATEDIFF(removalDate, CURDATE()) >= 0
;
Raw
04_show_create_view.sql
SHOW CREATE VIEW vw_search_report_everydocid;
Raw
05_show_create_view_output.sql
CREATE
ALGORITHM = UNDEFINED
DEFINER = `foobar`@`%`
SQL SECURITY DEFINER
VIEW `vw_search_report_everydocid` AS
SELECT
`r`.`uploadDate` AS `HUDx_dlm`,
`r`.`publishDate` AS `HUDx_dp`,
GROUP_CONCAT(`mcat`.`category_short`
ORDER BY `mcat`.`category_short` ASC
SEPARATOR '|') AS `HUDX_cs`,
GROUP_CONCAT(`mcat`.`category_long`
ORDER BY `mcat`.`category_long` ASC
SEPARATOR '|') AS `HUDX_cl`,
GROUP_CONCAT(`mcat`.`category_display`
ORDER BY `mcat`.`category_display` ASC
SEPARATOR '|') AS `HUDX_dsp`,
CAST(CONCAT_WS('',
'https://www.hudexchange.info',
'/resource/reportmanagement/published/',
REPLACE(REPLACE(`r`.`filename`, '%', '%25'),
'+',
'%2B'))
AS CHAR CHARSET UTF8) AS `HUDX_url`,
'Reports' AS `HUDX_ct`,
CASE
WHEN
(`r`.`program` = 'CoC'
AND `r`.`group` = 'GIS')
THEN
CONCAT(YEAR(`r`.`periodEnd`),
' ',
`tt`.`label`,
' - ',
`s`.`label`,
CASE
WHEN `tt`.`code` = 'ShapeFile' THEN ' CoCs'
ELSE ''
END)
WHEN
`r`.`program` = 'CoC'
THEN
CONCAT(YEAR(`r`.`periodEnd`),
' ',
`t`.`label`,
CASE
WHEN `c`.`label` <> '' THEN CONCAT(' - ', `c`.`label`)
WHEN `s`.`label` <> '' THEN CONCAT(' - ', `s`.`label`)
ELSE ''
END)
WHEN
`r`.`program` = 'HOPWA'
THEN
CONCAT(YEAR(`r`.`periodEnd`),
'-',
YEAR(`r`.`periodEnd`) + 1,
' ',
`t`.`label`,
CASE
WHEN `g`.`name` IS NOT NULL THEN CONCAT(' - ', `g`.`name`)
ELSE ''
END)
WHEN
`r`.`program` = 'NSP'
THEN
CONCAT(`t`.`label`,
CASE
WHEN `gt`.`label` IS NOT NULL THEN CONCAT(' - ', `gt`.`label`, ' Grantee')
ELSE ''
END,
CASE
WHEN `g`.`name` IS NOT NULL THEN CONCAT(' - ', `g`.`name`)
ELSE ''
END,
CASE
WHEN
`r`.`group` = 'QPR'
THEN
CONCAT(' - ',
CASE
WHEN MONTH(`r`.`periodEnd`) <= 3 THEN CONCAT('Q1 ', YEAR(`r`.`periodEnd`))
WHEN
(MONTH(`r`.`periodEnd`) > 3
AND MONTH(`r`.`periodEnd`) <= 6)
THEN
CONCAT('Q2 ', YEAR(`r`.`periodEnd`))
WHEN
(MONTH(`r`.`periodEnd`) > 6
AND MONTH(`r`.`periodEnd`) <= 9)
THEN
CONCAT('Q3 ', YEAR(`r`.`periodEnd`))
ELSE CONCAT('Q4 ', YEAR(`r`.`periodEnd`))
END)
WHEN
`r`.`periodEnd` IS NOT NULL
THEN
CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y'))
ELSE ''
END)
WHEN
`r`.`program` = 'HOME'
THEN
CONCAT(CASE
WHEN
(`r`.`group` = 'IncomeLmts'
OR `r`.`group` = 'RentLimits')
THEN
CONCAT(YEAR(`r`.`periodEnd`), ' ')
ELSE ''
END,
`t`.`label`,
CASE
WHEN
`r`.`group` = 'Dash'
THEN
CONCAT(IFNULL(CONCAT(' - ', `g`.`name`), ''),
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
''))
WHEN
`r`.`group` = 'Vacant'
THEN
CONCAT(' - ',
`s`.`label`,
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
''))
WHEN
`r`.`group` = 'Open'
THEN
CONCAT(' - ',
`s`.`label`,
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
''))
WHEN
(`r`.`group` = 'Snap'
AND `r`.`scope` = 'PJ')
THEN
CONCAT(IFNULL(CONCAT(' - ', `g`.`name`), ''),
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
''))
WHEN
(`r`.`group` = 'Snap'
AND `r`.`scope` <> 'PJ')
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
'')
WHEN
`r`.`group` = 'Prod'
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
'')
WHEN
`r`.`group` = 'Act'
THEN
CONCAT(' - ',
`s`.`label`,
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
''))
WHEN
`r`.`group` = 'ActHB'
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
'')
WHEN
(`r`.`group` = 'Cancel'
AND `r`.`periodEnd` IS NOT NULL)
THEN
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
'')
WHEN
(`r`.`group` = 'Cancel'
AND `r`.`periodEnd` IS NULL)
THEN
IFNULL(CONCAT(' - ', `r`.`fiscalYear`), '')
WHEN
`r`.`group` = 'Exp'
THEN
CONCAT(' - FY ',
`r`.`fiscalYear`,
CASE
WHEN `r`.`periodEnd` IS NOT NULL THEN ' or Earlier Expiring Funds'
ELSE ' Expired Funds'
END,
' - All States',
IFNULL(CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y')),
''))
WHEN
`r`.`group` = 'Deadline'
THEN
CONCAT(' - ',
CAST(YEAR(`r`.`periodEnd`) AS CHAR CHARSET UTF8),
' Commitment & CHDO Reservation, and ',
CAST(YEAR(`r`.`periodEnd`) - 3 AS CHAR CHARSET UTF8),
' Disbursement Requirements',
' - ',
`hs`.`label`)
WHEN
`r`.`group` = 'ES'
THEN
CONCAT(' - ',
DATE_FORMAT(`r`.`periodEnd`, '%M %e, %Y'),
' - ',
`hs`.`label`)
WHEN
(`r`.`group` = 'IncomeLmts'
OR `r`.`group` = 'RentLimits')
THEN
IFNULL(CONCAT(' - ', `s`.`label`), '')
ELSE ''
END)
WHEN
`r`.`program` = 'CDBG'
THEN
CONCAT(`t`.`label`,
IFNULL(CONCAT(' - ', `g`.`name`), ''),
' - PY',
YEAR(`r`.`periodEnd`))
WHEN
`r`.`program` = 'CPD'
THEN
CONCAT(`r`.`program`,
' ',
`rt`.`label`,
IFNULL(CONCAT(' - ', `g`.`name`), ''))
ELSE `t`.`label`
END AS `HUDX_title`
FROM
(((((((((`testdb`.`rpt_report` `r`
JOIN `testdb`.`rpt_type` `t` ON (`r`.`program` = `t`.`program`
AND `r`.`group` = `t`.`group`
AND `r`.`scope` = `t`.`scope`))
LEFT JOIN `testdb`.`rpt_coc` `c` ON (`c`.`code` = `r`.`COC`))
LEFT JOIN `testdb`.`rpt_grantee` `g` ON (`g`.`code` = `r`.`grantee`))
LEFT JOIN `testdb`.`rpt_state` `s` ON (`s`.`code` = `r`.`state`))
LEFT JOIN `testdb`.`rpt_grant_type` `gt` ON (`gt`.`code` = `r`.`grantType`))
LEFT JOIN `testdb`.`rpt_tool_type` `tt` ON (`r`.`toolType` = `tt`.`code`))
LEFT JOIN `testdb`.`rpt_sort` `hs` ON (`r`.`sort` = `hs`.`code`))
LEFT JOIN `testdb`.`rpt_report_type` `rt` ON (`r`.`program` = `rt`.`program`
AND `r`.`group` = `rt`.`group`
AND `r`.`reportType` = `rt`.`code`))
LEFT JOIN (SELECT
CASE
WHEN `testdb`.`c_tcategories`.`masterNameShort` <> '' THEN `testdb`.`c_tcategories`.`masterNameShort`
ELSE `testdb`.`c_tcategories`.`masterNameLong`
END AS `category_short`,
`testdb`.`c_tcategories`.`masterNameLong` AS `category_long`,
CASE
WHEN `testdb`.`c_tcategories`.`masterNameShort` <> '' THEN CONCAT(`testdb`.`c_tcategories`.`masterNameShort`, ': ', `testdb`.`c_tcategories`.`masterNameLong`)
ELSE `testdb`.`c_tcategories`.`masterNameLong`
END AS `category_display`
FROM
`testdb`.`c_tcategories`) `mcat` ON (CASE
WHEN `r`.`program` = 'CDBG' THEN `mcat`.`category_long` IN ('CDBG Entitlement Program' , 'CDBG HUD Administered Non-Entitled Counties in Hawaii Program', 'CDBG Insular Areas Program', 'CDBG State Program')
WHEN
`r`.`program` = 'CPD'
THEN
(`mcat`.`category_long` IN ('CDBG Entitlement Program' , 'CDBG HUD Administered Non-Entitled Counties in Hawaii Program', 'CDBG Insular Areas Program', 'CDBG State Program')
OR `mcat`.`category_short` IN ('HOME' , 'HOPWA', 'ESG'))
ELSE `r`.`program` = `mcat`.`category_short`
END))
WHERE
`r`.`status` = 'published'
GROUP BY `r`.`filename`
UNION SELECT
`err`.`uploadDate` AS `HUDx_dlm`,
`err`.`publishDate` AS `HUDx_dp`,
CASE
WHEN `mcat`.`masterNameShort` <> '' THEN `mcat`.`masterNameShort`
ELSE `mcat`.`masterNameLong`
END AS `HUDX_cs`,
`mcat`.`masterNameLong` AS `HUDX_cl`,
CASE
WHEN
`mcat`.`masterNameShort` <> ''
THEN
CONCAT(`mcat`.`masterNameShort`,
': ',
`mcat`.`masterNameLong`)
ELSE `mcat`.`masterNameLong`
END AS `HUDX_dsp`,
CAST(CONCAT_WS('',
'https://www.hudexchange.info',
'/resource/reportmanagement/published/',
REPLACE(REPLACE(`err`.`filename`, '%', '%25'),
'+',
'%2B'))
AS CHAR CHARSET UTF8) AS `HUDX_url`,
'Reports' AS `HUDX_ct`,
CONCAT('Environmental Review Record - ',
`err`.`projectName`,
' ',
`err`.`city`,
' ',
`err`.`state`,
' - ',
`err`.`err_status`) AS `HUDX_title`
FROM
(`testdb`.`rpt_environmental_review_record` `err`
LEFT JOIN `testdb`.`c_tcategories` `mcat` ON (`mcat`.`masterNameLong` = 'Environmental Review'))
WHERE
`err`.`status` = 'published'
AND TO_DAYS(`err`.`removalDate`) - TO_DAYS(CURDATE()) >= 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment