Namespace names w/less boilerplate

This query is marked as a draft This query has been published by Cryptic.

A way to get namespace names in query results with less boilerplate, or at least less wasted vertical space in the query, than the CASE expression I've been using e.g. in query 77254.

A significant drawback is that if you use the simpler versions - that is, the ones without COALESCE - then you get either a NULL (if you left join ns) or no row at all (if you just join it) in the vanishingly-rare cases when there's a page in an undefined namespace.  On the other hand, "SELECT CONCAT(ns_s, page_title)" or even just "SELECT ns_s, page_title" feels really natural, and the latter even only hides the namespace, not title, for the hypothetical screwy page.  (Looks the same as the main namespace on Quarry, though, which represents NULLs as blank.  That'd be pretty misleading.)

Alternatively, eliminating the whitespace from the CASE version also gets it to seven lines.  You'd still need to repeat it for each pagename column in the resultset, though, which is an occasional issue.

And of course you can always just stuff either into a single line if you don't mind the query box scrolling horizontally.

SQL

WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4, 'Wikipedia:'),
  (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'), (11, 'Template talk:'),
  (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101, 'Portal talk:'), (118, 'Draft:'),
  (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829, 'Module talk:'), (2300, 'Gadget:'),
  (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:'))
SELECT CONCAT(COALESCE(ns1.ns_s, CONCAT('{{ns:', page_namespace, '}}:')), page_title) AS pagename,
       CONCAT(ns1.ns_s, page_title) AS 'pagename, simpler',
       CONCAT(COALESCE(ns_plus_4.ns_s, CONCAT('{{ns:', page_namespace + 4, '}}:')), page_title) AS 'pagename in ns+4',
       CONCAT(ns_plus_4.ns_s, page_title) AS 'pagename in ns+4, simpler'
FROM page
LEFT JOIN ns AS ns1 ON ns1.ns_n = page_namespace
LEFT JOIN ns AS ns_plus_4 ON ns_plus_4.ns_n = page_namespace + 4
WHERE page_random BETWEEN 0.3916 AND 0.391601;

AخA
 
WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4, 'Wikipedia:'),  (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'), (11, 'Template talk:'),  (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101, 'Portal talk:'), (118, 'Draft:'),  (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829, 'Module talk:'), (2300, 'Gadget:'),  (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:'))SELECT CONCAT(COALESCE(ns1.ns_s, CONCAT('{{ns:', page_namespace, '}}:')), page_title) AS pagename,       CONCAT(ns1.ns_s, page_title) AS 'pagename, simpler',       CONCAT(COALESCE(ns_plus_4.ns_s, CONCAT('{{ns:', page_namespace + 4, '}}:')), page_title) AS 'pagename in ns+4',       CONCAT(ns_plus_4.ns_s, page_title) AS 'pagename in ns+4, simpler'FROM pageLEFT JOIN ns AS ns1 ON ns1.ns_n = page_namespaceLEFT JOIN ns AS ns_plus_4 ON ns_plus_4.ns_n = page_namespace + 4WHERE page_random BETWEEN 0.3916 AND 0.391601;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...