I’m trying to concatenate two columns for display in an sql calendar (Application Express) and insert text to describe what the two numbers are showing.
The columns need to be concatenated because Apex can display a date column and one other column. This bit is cool (it shows my numbers from each column but no space between or description):
select expensedate, sum(expamt) || sum(expappramt)
from expenseitem
group by expensedate;
nothing I have tried to insert some text will work. I’m sure it’s my syntax but searching Oracle forums etc and my books on sql are not helping me here. The examples I have found aren’t trying to concatenate columns together they are just inserting text before a result like this:
select expensedate, 'Exp ’ ||sum(expamt)
from expenseitem
group by expensedate;
but continuing this path leads to failure:
select expensedate, 'Exp ’ ||sum(expamt) || 'App ’ || sum(expappramt)
from expenseitem
group by expensedate;
I’m sure there’s a simple syntax rule I’m breaking but I’m not that experienced with pl/sql.
Hi Gryff,
Thanks for replying.
When I use a statement similar to yours :
select expensedate, 'EXP '|| sum(expamt) || 'APP ’ || sum(expappramt)
from expenseitem
group by expensedate;
The sql calendar display changes from something like e.g. 50.3050.30 (being the two amounts of 50.30 side by side) to a long string of #‘EXP’||sum(expamt)||‘APP’|| etc.etc.# i.e it’s just displaying the line of sql
I have a working query pretty much the same as Gryff’s (on Oracle either omit the char() function or change to to_char()).
select distribution, 'ABC ’ || char(sum(dlt1)) || 'XYZ ’ || char(sum(dlt2)) from distribution_ids
group by distribution;
No PL/SQL there at all it’s straight SQL. I don’t have an Oracle database to hand so I’m not sure if the sum() result are automatically to_char()-ed, it doesn’t hurt to explicitly to_char the numbers and it gives you the option to format them, to a fixed length, whatever.
On preview: GraemeGG can you post exactly what the query gives at a SQL prompt?
Ahh, I completely skipped that you were using Application Express, which is a tool I have no experience with.
The SQL you have is fine for plain old pl/sql. Could you try to place that entry into a variable then use it in Application Express?
This is actually correct output in Jdeveloper but when I put this as the sql in
Application express this is the error
***ORA-06502: PL/SQL: numeric or value error: dbms_sql.describe_columns overflow, col_name_len=43. Use describe_columns2
Return to application. ***
and this is what appears in the calendar box e.g. 23 Nov #SUM(EXPAMT)||SUM(EXPAPPRAMT)#
So it looks like my column name is too long? I don’t really understand that message.
Yeah, the error looked like the column name was the problem and as we hadn’t given it an explicit name it was trying to give it a name based on the calculation which was more than the length allocation to it (43).
You should be able to name that column something more descriptive now, just don’t make it too long.
In this case the tool you’re using seems to be doing some fancy stuff and hiding it from you. Let’s see what went wrong:
ORA-06502: PL/SQL: numeric or value error:
Good ol’ 6502. Generic data error, in this case a varchar truncation.
dbms_sql.describe_columns
dbms_sql is a database package for creating dynamic SQL, dunno why this is needed for a simple query but hey… Apparently the describe columns function isn’t smart enough to substr it’s output, hence the error.
Use describe_columns2
The database is suggesting you use a different function but since it’s the tool that’s chosen it it’s quite likely that you can’t influence this.
