Rails - Feb 2006 - simplify

Trying to use Eric''s help and Amy Hoy''s page of playing with
auto_complete and decided that the script/console is the place to
rapidly test.

I have this feeling that CONCAT isn''t working for postgresql...

c = find_by_sql(SELECT * FROM clients WHERE CONCAT
(first_name,middle_initial,last_name) = "johnadams")
                                     ^
(irb):61: syntax error
c = find_by_sql(SELECT * FROM clients WHERE CONCAT
(first_name,middle_initial,last_name) = "johnadams")

^
        from (irb):61

As you can tell...I''ve taken a number of stabs at it and simplified it
down as much as I can.

postgresql-7.4.8-1.RHEL-4.1

I can do single field searches and multi-field searches and the
concatenated searches clearly is what I need.

Craig

Hi Craig,
CONCAT is actually a mysql function. Sorry about that. For Postgres I
believe you use a || to concatenate.

ex.
SELECT * FROM users WHERE firstname||lastname = ''EricGoodwin''

Cheers,
Eric Goodwin


> Trying to use Eric''s help and Amy Hoy''s page of playing
with
> auto_complete and decided that the script/console is the place to
> rapidly test.
>
> I have this feeling that CONCAT isn''t working for postgresql...
>
> c = find_by_sql(SELECT * FROM clients WHERE CONCAT
> (first_name,middle_initial,last_name) = "johnadams")
>                                      ^
> (irb):61: syntax error
> c = find_by_sql(SELECT * FROM clients WHERE CONCAT
> (first_name,middle_initial,last_name) = "johnadams")
>
> ^
>         from (irb):61
>
> As you can tell...I''ve taken a number of stabs at it and
simplified it
> down as much as I can.
>
> postgresql-7.4.8-1.RHEL-4.1
>
> I can do single field searches and multi-field searches and the
> concatenated searches clearly is what I need.
>
> Craig
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Craig White wrote:
> Trying to use Eric''s help and Amy Hoy''s page of playing
with
> auto_complete and decided that the script/console is the place to
> rapidly test.
> 
> I have this feeling that CONCAT isn''t working for postgresql...
> 
> c = find_by_sql(SELECT * FROM clients WHERE CONCAT
> (first_name,middle_initial,last_name) = "johnadams")
>                                      ^
> (irb):61: syntax error
> c = find_by_sql(SELECT * FROM clients WHERE CONCAT
> (first_name,middle_initial,last_name) = "johnadams")
> 
> ^
>         from (irb):61
> 
> As you can tell...I''ve taken a number of stabs at it and
simplified it
> down as much as I can.
> 
> postgresql-7.4.8-1.RHEL-4.1
> 
> I can do single field searches and multi-field searches and the
> concatenated searches clearly is what I need.
> 
> Craig
The syntax is wrong, you missed off quotes.
c = find_by_sql("SELECT * FROM clients WHERE 
CONCAT(first_name,middle_initial,last_name) =
''johnadams''")

See if that works.

Joey__
http://www.feedreed.com

-- 
Posted via http://www.ruby-forum.com/.

On Sun, 2006-02-26 at 11:25 +0100, joey__ wrote:
> Craig White wrote:
> > Trying to use Eric''s help and Amy Hoy''s page of
playing with
> > auto_complete and decided that the script/console is the place to
> > rapidly test.
> > 
> > I have this feeling that CONCAT isn''t working for
postgresql...
> > 
> > c = find_by_sql(SELECT * FROM clients WHERE CONCAT
> > (first_name,middle_initial,last_name) = "johnadams")
> >                                      ^
> > (irb):61: syntax error
> > c = find_by_sql(SELECT * FROM clients WHERE CONCAT
> > (first_name,middle_initial,last_name) = "johnadams")
> > 
> > ^
> >         from (irb):61
> > 
> > As you can tell...I''ve taken a number of stabs at it and
simplified it
> > down as much as I can.
> > 
> > postgresql-7.4.8-1.RHEL-4.1
> > 
> > I can do single field searches and multi-field searches and the
> > concatenated searches clearly is what I need.
> > 
> > Craig
> 
> The syntax is wrong, you missed off quotes.
> c = find_by_sql("SELECT * FROM clients WHERE 
> CONCAT(first_name,middle_initial,last_name) =
''johnadams''")
> 
> See if that works.
----
perhaps for mysql but CONCAT isn''t syntactically correct for postgresql
as Eric pointed out - which also explains why I couldn''t find any
references for it in my PostgreSQL book too. The || operator worked fine for
PostgreSQL and my only issue remaining is returning the found items back into
the calling code...not a simple matter.

Thanks

Craig