Re: Query problem...

Hi unless i have confused myself, shouldn't the following work?

SELECT RTRIM(FIELD1) ||':'|| RTRIM(FIELD2) from TABLE1;

M

ps Be prepared to be flamed by the SQL Server is not the same as Oracle possee! ;-)

salil_khedkar_at_yahoo.com (Neo) wrote in message news:<a6c24f5.0203210319.e7c0c68_at_posting.google.com>...
> Hi Folks,
>
> I am working on a application designed for Microsoft SQL server which
> I am am now porting to Oracle.
>
> I want to get a ':' separated list of IDs from a table. So on MSSQL a
> query like works fine...
> SELECT RTRIM(CAST(FIELD1 AS char(30))) + ':' + RTRIM(CAST(FIELD2 AS
> char(30))) as LIST from TABLE1
> and return the following as result for example.
> 123:1234
> :123 (if first value is null)
> 123: (if first value is null)
> : (if first value is null)
>
> But on oracle if I do the same query
> SELECT RTRIM(CAST(FIELD1 AS char(30))) + ':' + RTRIM(CAST(FIELD2 AS
> char(30))) as "LIST" from TABLE1
> (1) It says I cant add mixed types like this
> (2) If I remove the + ':' + part, it gives me numeric addition of the
> two fields instead of string concatenation.
> (3) Finally, if any of the field is null, it does not return any value
> at all
>
> What is the correct query for oracle in this case?
>
> Regards,
> Neo
Received on Thu Mar 21 2002 - 08:57:13 CST