how to include hard-coded value to output from mysql query?

Question

I've created a MySQL sproc which returns 3 separate result sets. I'm implementing the npm mysql package downstream to exec the sproc and get a result structured in json with the 3 result sets. I need the ability to filter the json result sets that are returned based on some type of indicator in each result set. For example, if I wanted to get the result set from the json response which deals specifically with Suppliers then I could use some type of js filter similar to this:

var supplierResultSet = mySqlJsonResults.filter(x => x.ResultType === 'SupplierResults');

I think SQL Server provides the ability to include a hard-coded column value in a SQL result set like this:

select
'SupplierResults',
*
from
supplier

However, this approach appears to be invalid in MySQL b/c MySQL Workbench is telling me that the sproc syntax is invalid and won't let me save the changes. Do you know if something like what I'm trying to achieve is possible in MySQL and if not then can you recommend alternative approaches that would help me achieve my ultimate goal of including some type of fixed indicator in each result set to provide a handle for downstream filtering of the json response?

Welcome to Stack Overflow. Please take the tour to learn how Stack Overflow works and read How to Ask on how to improve the quality of your question. Then edit your question to include a minimal reproducible example, see meta.stackoverflow.com/questions/333952/… for SQL related questions. Also include the complete error message you get from MySQL. — Progman
– Progman, Commented Jan 24, 2020 at 23:06

GMB · Accepted Answer · 2020-01-24 23:09:16Z

2

If I followed you correctly, you just need to prefix * with the table name or alias:

select 'SupplierResults' hardcoded, s.* from supplier s

As far as I know, this is the SQL Standard. select * is valid only when no other expression is added in the selec clause; SQL Server is lax about this, but most other databases follow the standard.

It is also a good idea to assign a name to the column that contains the hardcoded value (I named it hardcoded in the above query).

answered Jan 24, 2020 at 23:09

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick · Accepted Answer · 2020-01-25 00:00:06Z

0

In MySQL you can simply put the * first:

SELECT *, 'SupplierResults'
FROM supplier

Demo on dbfiddle

answered Jan 25, 2020 at 0:00

Nick

147k23 gold badges67 silver badges106 bronze badges

Comments

T.S. · Accepted Answer · 2020-01-27 22:08:00Z

0

To be more specific, in your case, in your query you would need to do this

select
'SupplierResults',
supplier.*  -- <-- this
from
supplier

Try this

create table a (f1 int);
insert into a values (1);
select 'xxx', f1, a.* from a;

Basically, if there are other fields in select, prefix '*' with table name or alias

edited Jan 27, 2020 at 22:08

answered Jan 24, 2020 at 23:31

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Collectives™ on Stack Overflow

how to include hard-coded value to output from mysql query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related