Substring in a Listbox field

Applications Forms
#1

Hi all.
I have a field that displays a list from a select on a table like this:

SELECT cue_codigo, sc_concat (cue_codigo, ‘-’, cue_nombre)
FROM account WHERE cue_cia = ‘[v_customerid]’

The list of data field are shown as follows:
12345 - Initial Account base
12344 - Secondary Account base
22345 - Account valued third

But I just want to display the first 10 characters of the field named “cue_nombre” therefore change the select like this:
SELECT cue_codigo, sc_concat (cue_codigo, ‘-’, substr (cue_nombre, 1,10))
FROM account
WHERE cue_cia = ‘[v_customerid]’

And the list in the field is displayed only with the field “cue_codigo”:
12345
12344
22345

Why does not extract only the 10 characters of the name? how I can fix it?
Thanks for the help.

JG

#2

Re: Substring in a Listbox field

You are using sc_concat() in your select command that will not be recognized by your DB??
Q: I have not used, will sc_concat() work in SQL command when called from SC code?

I always tried to stay away from macros when possible and use native PHP/MYSQL functions.

change to:


SELECT cue_codigo, CONCAT_WS(cue_codigo, '-', SUBSTRING(cue_nombre, 1,10))
FROM account
WHERE cue_cia = '[v_customerid]'

Regards,
Scott

#3

Re: Substring in a Listbox field

Thanks Scott.
I made the listbox using SC tool directly, selecting table and fields from SC interface, then SC put into the sentence the SC_CONCAT command, after that I tried to add “substring” command with the purpose on show just 10 characters.

Finally, it?s fixed now using the Scott?s suggestion. So:
SELECT cue_codigo, concat (cue_codigo, ’ - ', substring (cue_nombre, 1,10))
FROM account
WHERE cue_cia = ‘[v_customerid]’

Tks again!
JG