looking for solution on my sql query error.I'm trying to create second cascading Input Control in JaspersoftServer. The first Input Control works fine, however when I try to create a second cascade IC it returns with the error. I have 3 tables (user, client, user_client), many to many, so 1 linked table (user_client) between them.The 1st Input Control (client) - works well, end user will select the client, the client can have many users, so cascade is the key. Also, as the output, I would like to get not the user_id, but user's firstname and the lastname as one column field. And here is where i'm stuck. I'm pretty sure it is simple syntaxis error, but spent a good couple of hours to figure out what is wrong with it. Is anyone can have a look at it please and indicate where is the problem in my query ?! So far I've done:
select distinct
u.user_id,(
SELECT CONCAT(first_name, surname) AS user_name from tbl_user ),
c.client_id
FROM tbl_user u
left join tbl_user_client uc
on uc.user_id = u.user_id
left join tbl_client c
on c.client_id = uc.client_id
where c.client_id = uc.client_id
order by c.client_id
Thank you in advance. P.S. JasperServer + Oracle 11g
You're doing an uncorrelated subquery to get the first/last name from the user table. There is no relationship between that subquery:
SELECT CONCAT(first_name, surname) AS user_name from tbl_user
... and the user ID in the main query, so the subquery will attempt to return every first/last name for all users, for every row your joins find.
You don't need to do a subquery at all as you already have the tbl_user information available:
select u.user_id,
CONCAT(u.first_name, u.surname) AS user_name
c.client_id
FROM tbl_user u
left join tbl_user_client uc
on uc.user_id = u.user_id
left join tbl_client c
on c.client_id = uc.client_id
where c.client_id = uc.client_id
order by c.client_id
If you want to put a space between the first and last name you'll either need nested concat() calls, since that function only takes two arguments:
select u.user_id,
CONCAT(u.first_name, CONCAT(' ', u.surname)) AS user_name
...
... or perhaps more readably use the concatenation operator instead:
select u.user_id,
u.first_name ||' '|| u.surname AS user_name
...
If the first control has selected a client and this query is supposed to find the users related to that client, you're joining the tables the wrong way round, aren't you? And you aren't filtering on the selected client - but no idea how that's actually implemented in Jasper. Maybe you do want the entire list and will filter it on the Jasper side.