Re: Query problem...

Neo wrote:
>
> 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

Hi Neo,
This is obviously a _very_ silly question, but, did you even look at the Oracle SQL reference guide?

If you took an article written in the London Times newspaper, and stuck it in the Washington Post newspaper with no modifications, then _many_ Americans would have trouble understanding the article. But both newspapers are written in English, aren't they, so what's the problem? Well, I guess that's why you have Webster's American English dictionary, and the Oxford English dictionary, right?

Not all SQL is the same. And remember, when all else fails, try reading the documentation (what a novel idea :-).

But perhaps I am assuming too much. Do you have access to the documentation? Can you read English? Can you understand the English that you read? Perhaps there are extenuating circumstances that prevent you from reading the documentation? If so, then maybe you should note that in future posts that quite obviously warrant an RTFM response.

Good Luck,
Avi. Received on Sun Mar 24 2002 - 02:25:01 CST