SQLite - using hyphen in a column alias
EDIT : used SQLiteBrowser as IDE \ Tool
Not a frequent user of SQLite - please forgive my ignorance. The next request is part of a larger request;
SELECT DISTINCT T1.Material AS "Diameter",
T2.sLength AS "0-110mm",
T3.sLength AS "110-160mm",
T4.sLength AS "225-300mm",
T5.sLength AS "300-450mm",
T6.sLength AS "450-600mm",
T7.sLength AS "600mm-"
FROM "mycte" T1
LEFT JOIN "mycte" T2
ON T1.Material = T2.Material
AND T2.Diameter = "0-110mm"
LEFT JOIN "mycte" T3
ON T1.Material = T3.Material
AND T3.Diameter = "110-160mm"
LEFT JOIN "mycte" T4
ON T1.Material = T4.Material
AND T4.Diameter = "225-300mm"
LEFT JOIN "mycte" T5
ON T1.Material = T5.Material
AND T5.Diameter = "300-450mm"
LEFT JOIN "mycte" T6
ON T1.Material = T6.Material
AND T6.Diameter = "450-600mm"
LEFT JOIN "mycte" T7
ON T1.Material = T7.Material
AND T7.Diameter = "600mm-"
WHERE T1.Material <> ''
Although I use an alias with a single defect / minus in the alias, the fields are returned empty. If I rename the alias "0-110mm" to "0-110mm", the column fields return data. It would seem that one hyphen \ minus confused things.
Any ideas? Thanks in advance.
source to share
Best of all I was able to do it. You just have to deal with the trailing space in the name.
SELECT DISTINCT
T1.Material AS "Diameter"
,T2.sLength AS [0-110mm ]
,T3.sLength AS [110-160mm ]
,T4.sLength AS [160-225mm ]
,T5.sLength AS [225-300mm ]
,T6.sLength AS [300-450mm ]
,T7.sLength AS [450-600mm ]
,T8.sLength AS [600mm- ]
FROM "MyCTE" T1
LEFT
JOIN "MyCTE" T2 ON T1.Material = T2.Material AND T2.Diameter = "0-110mm"
LEFT
JOIN "MyCTE" T3 ON T1.Material = T3.Material AND T3.Diameter = "110-160mm"
LEFT
JOIN "MyCTE" T4 ON T1.Material = T4.Material AND T4.Diameter = "160-225mm"
LEFT
JOIN "MyCTE" T5 ON T1.Material = T5.Material AND T5.Diameter = "225-300mm"
LEFT
JOIN "MyCTE" T6 ON T1.Material = T6.Material AND T6.Diameter = "300-450mm"
LEFT
JOIN "MyCTE" T7 ON T1.Material = T7.Material AND T7.Diameter = "450-600mm"
LEFT
JOIN "MyCTE" T8 ON T1.Material = T8.Material AND T8.Diameter = "600mm-"
source to share
I'm not sure which version of SQLite you are using, but the following script demonstrates that your initial approach works correctly:
http://sqlfiddle.com/#!7/20222/1
CREATE TABLE test
(
col1 VARCHAR(100)
);
INSERT INTO test
(col1)
SELECT 'foo'
SELECT col1 AS "col-1"
FROM test
source to share
Don't use double quotes or square brackets for your aliases. Use single quotes instead. This might fix the problem. I had a similar problem the other day, not the same but similar.
https://www.sqlite.org/lang_keywords.html
'keyword' The single quoted keyword is a string literal. "keyword" The double-quoted keyword is an identifier. [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This citation engine is used by MS Access and SQL Server and is included in SQLite for compatibility.
source to share