Does anyone have more information about "sc_concat"

Applications Forms
#1

When making an SQL in EDITION Lookup in a feeld scripcase uses sc_concat()

i cannot find anything about sc_concat in the manual…

I try to use it like this :

[i]SELECT
j_contacts_x_dossier.id, sc_concat( s_role.role,’ ', t_company.company, ’ ', t_department.department, ’ ', t_contacts.callname, ’ ',t_contacts.lastname )

FROM
j_contacts_x_dossier
INNER JOIN s_role ON j_contacts_x_dossier.role = s_role.id
LEFT JOIN t_contacts ON j_contacts_x_dossier.id_contact = t_contacts.id
LEFT JOIN t_department ON j_contacts_x_dossier.id_department = t_department.id
LEFT JOIN t_company ON j_contacts_x_dossier.id_company = t_company.id
WHERE j_contacts_x_dossier.id_dossier = 510[/i]

but the outcome is very strange… it does not give any… this looks like i’m crosing a limit … maybe max number of characters…

when i try to make it smaler…
SELECT
j_contacts_x_dossier.id, sc_concat( s_role.role,’ ', t_company.company, ’ ', t_department.department )

it only gives output if all 3 fields have content … if for example i have a company without a department is it not shown…

any ideas ?

#2

Re: Does anyone have more information about “sc_concat”

Found a working solution…

instead of using sc_concat() i now use mysql concat_ws …

this works…

#3

Re: Does anyone have more information about “sc_concat”

+1 - the sc_concat is flaky and, in any case, can be more complex compared to CONCAT_WS() when you need need to pad the return string with delimiters. For example, if you need to separate the returned fields with a comma,

sc_concat({field1},’, ', {field2}, ', ', {field3})

as compared to

CONCAT_WS(’, ‘, {field1},{field2},{field3})’

#4

Re: Does anyone have more information about “sc_concat”

Tnx for your reply GuiGuy…

the big difference between sc_concat() and concat from mysql for that matter compaired to concat_ws() is that concat_ws() does not skip the NULL values…

sc_concat() and concat() do skip NULL values …

#5

Re: Does anyone have more information about “sc_concat”

There you go. I didn’t know that. But then, I’ve never had sc_concat work well enough for me to realise that it was supposed to have that ability <grin>

Thanks