1

I would like to find a way of writing a SELECT statement which includes using the column name as a search criteria in a data_glossary table.

Example SQL statement

SELECT field1, field2, field3
FROM table1

I want to include field_definition from a table called tbl_data_glossary using WHERE data_reference = 'field1' whenever I run a script. So I should get two data sets, the first one has the data, the second has what the data set values mean.

I hope that makes sense, I want to implement this in a way where a BI tool could not only pull values but include the information required to provide help for business users.

I have tagged this as SQL and Javascript but maybe you have a better way of approaching this.

I wonder if I need to setup my select statement using an array of variables somehow and then also use the same array in a WHERE data_reference IN [array] type statement.

So, if one of the fields in my SELECT statement is reference_definition_name I want a sub query or separate query to look up that column name as a value which is held in the column called reference_code in tbl_reference_data

Here's the tbl_reference_data table which contains the data glossary with example data:

        (reference_def_id, 
         reference_def_name, 
         reference_def_created, 
         reference_def_last_updated, 
         dataset_category, 
         dataset_type, 
         reference_code, 
         reference_name, 
         reference_description, 
         reference_status, 
         reference_lang)

Example data:

('8f603ffe-6800-11ea-bc55-0242ac130003',  'Data Origin Identifiers',  '17/03/2020 16:34',  '17/03/2020 16:34',  'Reporting',  'data-glossary', 'reference_definition_name',        'Definition Name',        'This is the name of the reference data set related to the purpose of the reference definitions.','ACTIVE','EN')

Thanks, Matt

Matt Lightbourn
  • 597
  • 3
  • 20
  • How about : SELECT field1, field2, field3 FROM table1 UNION SELECT field_definition FROM tbl_data_glossary using WHERE data_reference = field1 ? – Ezani Mar 17 '20 at 05:43
  • I want to write up a common method of writing queries where the select statement will be variable, not always the same just like you can when you write it directly into a console but for it to automatically respond with additional data which is information on the fields you requested data from. The UNION you suggest, I believe, is for when one or more tables shares the same structure or characteristics. They are both different structures and purposes, one contains dimensions and facts, the other tells you what they mean. Thanks – Matt Lightbourn Mar 17 '20 at 19:53
  • Note: I have just checked out an old StackOverflow question and its close to what I'm trying to achieve I think - its not quite but close (https://stackoverflow.com/questions/18360118/sql-variables-as-column-names-in-where-clause) - what I want then is for one SELECT statement to write another query to return the data_glossary definitions. – Matt Lightbourn Mar 17 '20 at 20:31
  • ah, bad move apparently, SQL injection alert. All I want to do is when a query is run, it also looks up the column names as values in a WHERE statement from a different table. I would need both sets of results delivered to requesting client together or separately – Matt Lightbourn Mar 17 '20 at 21:06
  • Matt, if you want to avoid SQL injections, use parameters to pass the query variables rather than directly in SQL. – Ezani Mar 18 '20 at 02:19

0 Answers0