Query problem...

From: Neo <salil_khedkar_at_yahoo.com>
Date: 21 Mar 2002 03:19:11 -0800
Message-ID: <a6c24f5.0203210319.e7c0c68@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 - 05:19:11 CST