Concat problem with SQL

[Shivaan Keldon]

hi all
I have a problem when I try to concat 2 fields with a dot, in a query (Oracle
database).
here an example :

<query name="myquery" datasource="mydatasource">
select field1 || '.' || field2 from mytable
</query>

in Toad, this query works correctly. but with coldfusion, an error is thrown
(could not find FROM keyword)
first, I thought it was the quotes, but PreserveSingleQuotes changes nothing

has someone an idea ?

ps : I'm a french guy, and I'm not quite good to explain problems in english.
please ask me questions if you don't understand

thanks

[paross1]

I can't seem to duplicate your problem. I am able to concatenate two fields and
a period with my Oracle 9i database just fine, using CF 6.1 and both the Oracle
and ODBC socket connections.

However, you might want to alias the combined field with a new name.

SELECT field1 || '.' || field2 AS new_field FROM mytable

Phil

[Dan Bracuk]

It also works when I do it using Oracle 8i and CF7

Originally posted by: paross1
I can't seem to duplicate your problem. I am able to concatenate two fields
and a period with my Oracle 9i database just fine, using CF 6.1 and both the
Oracle and ODBC socket connections.

However, you might want to alias the combined field with a new name.

SELECT field1 || '.' || field2 AS new_field FROM mytable

Phil

[hebril]

Thaks for your reply

but I use an alias and I have always the theem erro.

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00923: FROM keyword not found
where expected

I use CF 6.1, and when I change the character . to * or another charater,
my query run correctly.

Please can you help me ! I I become crazy

ps : Sorry for my english

[Dan Bracuk]

take baby steps to find the problem. Run these queries in this order until you
find the error.

select field1
select field 2
select field1, field 2
select field1 || field2
select field1 || ' not a dot '
select field1 || '.'
select field1 || '. || field2