1

I need to do join query to MS SQL Server 2014 DB based on a column name value. The same query runs when doing query directly to DB, but when doing query through Mule I'm getting error. The query looks something like this :

  SELECT *  FROM sch.emple JOIN  sch.dept on sch.emple.empid = sch.dept.empid;

The above query work fine while doing query directly to MS SQL Server DB, but gives the following error through mulesoft.

Record cannot be mapped as it contains multiple columns with the same label. Define column aliases to solve this problem (java.lang.IllegalArgumentException). Message payload is of type: String

Request you to please help me out.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Balwant Kumar Singh
  • 1,158
  • 4
  • 24
  • 48

1 Answers1

3

Specify columns list directly:

SELECT e.<col1>, e.<col2>, ...., d.<col1>,...
FROM sch.emple AS e
JOIN sch.dept AS d
  ON e.empid = d.empid;

Remarks:

  1. You could use aliases instead of schema.table_name
  2. SELECT * in production code in 95% cases is bad practice
  3. The column that has duplicate is empid(or more). You could add alias for it e.empid AS emple_empid and d.empid AS dept_empid or just specify e.empid once.

To avoid specifying all columns manually, you could drag and drop them from object explorer to query pane like Drag and Drop Column List into query window.

Second way is to use plugin like Redgate Prompt to expand SELECT *:

enter image description here

Image from: https://www.simple-talk.com/sql/sql-tools/sql-server-intellisense-vs.-red-gate-sql-prompt/

Addendum

But the same query works directly.

It works because you don't bind them. Please read carefully link I provided for SELECT * antipattern and especially:

Binding Problems

When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names. But the same query works directly.

by Dave Markle

Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I need to select all the fields not the specific. While doing * getting the same error. – Balwant Kumar Singh Mar 07 '16 at 10:43
  • @BalwantChauhan **`SELECT *` is antipattern** and `SQL Server` does not support `USING` so you could write hard to maintain dynamic-SQL or just get columns names. – Lukasz Szozda Mar 07 '16 at 10:57
  • @lad2015 But the same query works directly. Writing column names would ve hard as there are more than 100 columns. – Balwant Kumar Singh Mar 07 '16 at 11:01
  • 1
    @BalwantChauhan Even if you've 100 columns or 1000 for that matter, if you need to select them you've to declare them in your project. Don't be lazy... it will save you lot of headache in future on table Alters – Charu Khurana Mar 07 '16 at 13:19