Advanced Query error expression

Advanced Query error expression

  
what can be wrong in the command below?

error when concatenating columns (expression):
ORA-01722 - INVALID NUMBER

The COMMAND is:
SELECT  {GG_RPC_IPC}.[CD_SOLICITACAO], {GG_RPC_IPC}.[CD_SOLICITACAO] + ' - ' + {GG_RPC_IPC}.[DT_SOLICITACAO]   as DS_SOLICITACAO
from {GG_RPC_IPC}
where {GG_RPC_IPC}.[CD_SEG_SISTEMA] = @pTipoSistema and
      {GG_RPC_IPC}.[IN_ROTINA] = @pTipoRotina and
      {GG_RPC_IPC}.[CD_USUARIO] = @pUserName and
      {GG_RPC_IPC}.[DT_SOLICITACAO] >= @pDtSolicitacao
Hi Rogrido,

In oracle to concatenate strings you use the '||' operator.
Change your query to {GG_RPC_IPC}.[CD_SOLICITACAO] || ' - ' || {GG_RPC_IPC}.[DT_SOLICITACAO]

Cheers,
André
and how to run on SqlServer and Oracle?
Add the concatenation as a input parameter of your query (needs to be expand inline). You also have a built-in function action that tells you what is the database server that it is using.

So you get.
{GG_RPC_IPC}.[CD_SOLICITACAO] @concatenate ' - ' @concatenate {GG_RPC_IPC}.[DT_SOLICITACAO]

and the input parameter value @concatenate = if (GetDatabaseProvider() = "SqlServer","+","||")

Cheers
thank´s
the following error is occurring:
ORA-00923: FROM Keyword not found where expected

 parameter value @concatenate = if (GetDatabaseProvider() = "SqlServer","+","||")
the advanced query is:
SELECT  {GG_RPC_IPC}.[CD_SOLICITACAO], {GG_RPC_IPC}.[CD_SOLICITACAO] @concatenate ' - ' @concatenate {GG_RPC_IPC}.[DT_SOLICITACAO] as DS_SOLICITACAO
from {GG_RPC_IPC}
where {GG_RPC_IPC}.[CD_SEG_SISTEMA] = @pTipoSistema and 
      {GG_RPC_IPC}.[IN_ROTINA] = @pTipoRotina and 
      {GG_RPC_IPC}.[CD_USUARIO] = @pUserName and 
      {GG_RPC_IPC}.[DT_SOLICITACAO] >= @pDtSolicitacao
another detail. formatting dates independent DBMS.
In the following command how to format the date field} {GG_RPC_IPC. [DT_SOLICITACAO]:

SELECT  {GG_RPC_IPC}.[CD_SOLICITACAO], {GG_RPC_IPC}.[CD_SOLICITACAO] @concatenate '-' @concatenate {GG_RPC_IPC}.[DT_SOLICITACAO]
from {GG_RPC_IPC}
where {GG_RPC_IPC}.[CD_SEG_SISTEMA] = @pTipoSistema and 
      {GG_RPC_IPC}.[IN_ROTINA] = @pTipoRotina and 
      {GG_RPC_IPC}.[CD_USUARIO] = @pUserName and 
      {GG_RPC_IPC}.[DT_SOLICITACAO] >= @pDtSolicitacao
I discovered the problem. GetDatabaseProvider () is returning but Oracle SQLServer
It did that a component in Integration studio. The table already exists in another database that is Oracle. What is the command to get the manager of the integrated table?
Rodrigo,

If you know that the table is from Oracle you don't need to use @concatenate parameter just use '||'

Cheers
ok but I have another client that will use SQL server
cok but I have another customer that will use SQL server
so I would do something for both DBMS
in Integration Studio to design the DBMS property I set the value Both
please if you tell me another alternative
Hi Rodrigo,

Why don't you make the query return all the 3 atributes and then concatenate the values in your application logic?
That would be alot simpler and database independent.


(on a side note on one of your queries you also had the 'as' keyword. It is an optional keyword and not database independent, so don't use it)

Regards,
João Rosado
but the values ??in the column comes from the database. How to solve it:
{GG_RPC_IPC}.[CD_SOLICITACAO] @concatenate ' - ' @concatenate {GG_RPC_IPC}.[DT_SOLICITACAO]
could you show an example?