topic Re: DB Connect Column Aliases in Deployment Architecture

DB Connect Column Aliases

arrowsmith3 — Wed, 17 Apr 2013 18:04:53 GMT

Is anyone having an issues with assigning column aliases in a mysql db?

SELECT DISTINCT
issuestatus.pname AS "Status"
FROM issuestatus

pname
1 Open
2 Assigned

Re: DB Connect Column Aliases

wagnerbianchi — Thu, 18 Apr 2013 22:34:46 GMT

Yes, I got the same problem! Perhaps the parser is not considering convert the alias from SQL to SPL, I think...

SELECT user as username FROM mysql.user WHERE user='root';

Database Results
User
1 root
2 root

Re: DB Connect Column Aliases

Sammich — Thu, 06 Jun 2013 21:11:31 GMT

You need to escape the quotes that are used in the column alias.
| dbquery "database" "SELECT DISTINCT issuestatus.pname AS \"Status\" FROM issuestatus"

Re: DB Connect Column Aliases

vrmerlin — Wed, 03 Jul 2013 22:35:26 GMT

I tried escaping the quotes as suggested, but it didn't fix the problem for me. Any other suggestions?

Re: DB Connect Column Aliases

yuwtennis — Sat, 17 Aug 2013 01:21:03 GMT

I am having a same problem as well with using the 'AS' in select statement.

You cannot join multiple tables that has same column names if you cant
use the AS.

I hope there is a solution for this.

Re: DB Connect Column Aliases

HiroshiSatoh — Mon, 19 Aug 2013 01:55:29 GMT

Do not be resolved in a way and a separate column by editing the column?

ex.
| dbquery mysql01 limit=1000 "select users.id+0 as user_id from users"

(result)
user_id
1
2
3

How about now?
SELECT DISTINCT issuestatus.pname+"" AS "Status" FROM issuestatus

Re: DB Connect Column Aliases

rdevine — Fri, 20 Dec 2013 20:18:06 GMT

Anyone ever find a solution for this?

Re: DB Connect Column Aliases

aelliott — Fri, 20 Dec 2013 22:49:29 GMT

Did you attempt to exclude the quotes around the Status?

Re: DB Connect Column Aliases

FloydATC — Thu, 17 Jul 2014 11:22:16 GMT

I think I found the root cause of this problem in the README notes for MySQL J connector 5.1.x which states that .getColumnName() now always returns the original column name and the caller must use .getColumnLabel() to get the alias.

Downgrading from 5.1.4 to 3.14 solved the problem for me, although I'm sure it will cause other problems down the road. I have reported the issue to Splunk via our dealer.

Re: DB Connect Column Aliases

AvianFLU — Thu, 24 Jul 2014 23:16:04 GMT

If this is in the Database Input or Database Query feature - I found a workaround for this. Simply cast all the fields and it should work.

SELECT DISTINCT CAST(issuestatus.pname AS Char(32)) AS Status FROM issuestatus

Re: DB Connect Column Aliases

monkey — Fri, 25 Jul 2014 14:30:06 GMT

This workaround, well, works! I've been looking for a way to get aliases working for ages now and this finally does it. Thanks!